The aim of this blog post is twofold, it is to explain how:
- A “Self building pipeline” for the deployment of a SQL Server Data Tools project can be implemented using open source tools
- A build pipeline can be augmented using PowerShell
What You Will Need
Jenkins automation server
SQL Server 2016 (any edition will suffice)
Visual Studio 2015 community edition
A windows server, physical or virtual to install all of the above on, I will be using Windows Server 2012 R2 as the operating system
Automated integration via CI is extremely helpful, and Chris makes it look easy in this post.
What does this do?
Unblock-File *.ps1 – removes a flag that windows puts on files to stop them being run if they have been downloaded over the internet.
.\ContinuousDeploymentFTW.ps1 – runs the install script which actually:
- Downloads chocolatey
- Installs git
- Installs Jenkins 2
- Guides you how to configure Jenkins
- Creates a local git repo
- Creates a SSDT project which is configured with a test project and ssdt and all the references that normally cause people problems
- Creates a local Jenkins build which monitors your local git repo for changes
- When code is checked into the repo, the Jenkins job jumps into action and…
If you check into the default branch “master” then Jenkins:
- Builds the SSDT project
- Deploys the project to the unit test database
- Runs the tSQLt unit tests
- Generates a deployment script for the “production” database
and what you have there is continuous delivery in a box
Click through for a video where Ed shows how it all works.
The first thing to note is the garbage collection, there are like 8 changes to the line so 8 sets of garbage collection that happen which is expensive for a .net app. We get much more processor usage for a more prolonged time and we really start to see some disk activity, write activity just has one peak while the files are written but reading stays quite high throughout the whole process. It could be that it is sql reading from disk (my demo was from a single ssd with a local sql instance) but we can see after the sql cpu settles down the green disk read line stays high so it is unlikely to be pure sqlserver.exe.
What does this tell us?
Well if you have large objects, multi-thousand line stored procs then you better get yourself some serious ssd’s to deploy from. If you have smaller objects then you’ll need CPU and memory – don’t scrimp here!
Check it out.
When using VS for database projects I typically use my environment specific Publish xml file to deploy changes to my local database when experimenting with code changes. However, every once in a while I will have to use the New Schema Compare tool from the Tools | SQL Server menu when I have a “one-off” database that I need to synch to my database project.
Quite frequently because these are one-off databases there will be a ton of junk items that are in the database, but not in my database project or lots of objects in my project that aren’t in my database. Either way I want to ignore those changes. In the past, I’ve always manually unchecked each item, tedious when you have more than two items to uncheck.
Click through for more details.
It’s Release Day! 🙂
New versions of SQL Server Data Tools (SSDT) are available here. SSDT 16.5 and 17.0 (RC1) are available. Also available are Data-Tier Application Framework (DacFx) versions 16.5 and 17.0 (RC1).
New versions of SQL Server Management Studio (SSMS) are available here. SSMS 16.5 and 17.0 (RC1) versions are available for SSMS.
It’s going to be a busy couple of days for some people…
According to the blurb, deployment contributors can perform custom actions when deploying a SQL script. And one such use of deployment contributors would be to alter index builds to be an online operation. Microsoft also have a Github DACExtensions repo, and this is very useful because, and in the interests of full disclosure, I have never written a deployment contributor myself. This is partly because the repo has some very good examples, including the online index issue (this post nicely covers how to make use of deployment contributors.) I know those that have and have explained how they work very well. But I think there are a few challenges w/r/t deployment contributors:
No one has ever heard of them
You have to use C#
They’re not entirely straightforward.
This is a good discussion of deployment contributors, including why we don’t see them more frequently.
Idempotent: big word, and is used in maths. But don’t let that put you off. What it means in this context is that a script can be re-run and the same result happens. So in the context of altering a primary key, the pseudo-code would look something like this:
if database exists then
if table exists then
if primary key name eq “oldname” then
drop primary key then
add primary key with “newname”
I’m a huge fan of idempotent scripts. Releases become so much easier when you can guarantee that you can re-run the scripts, and idempotence is a necessary property of continuous integration.
Whilst I won’t be showing code in this, there is a companion sample database project. This is on GitHub and each key stage is shown by a branch of work. This means you can jump in at most stages and work from there. If you need some git advice, check out my quick git in 5 presentation.
The core DB is a super hero themed database.
Database unit testing involves a lot of compromises (because most of what we care about as database professionals tends to be integration tests rather than unit tests), but having imperfect tests generally beats having no tests, so check this out.
After installing, we need to customized their setting by creating connection(s) to our SQL Server. We do this by opening VS Code “User Preferences” and under “Default Settings.json” we search for the “vscode-mssql” settings to be copied over to our working folder “settings.json” file.
I played with this very early on and would like to see it continue to be developed, but it’s no replacement for Management Studio.
SSDT helps us to refactor code by automating the actions of:
- Expanding wildcards
- Fully qualifying object names
- Moving objects to a different schema
- Renaming objects
Aside from this list SSDT also, of course, helps us to refactor code manually with its general editing facilities.
If you aren’t familiar with what SSDT can do, check out this article.