Continuous Deployment of SQL Server Database Changes using Visual Studio & TFS Release Manager
Continuous Deployment is, or at least should be, the goal of any software development group. Performing CD of software and applications has become so easy, in most instances, many of us don’t think twice about implementing it. Performing CD of database changes on the other hand can easily prove to be a bit more difficult.
Database changes tend to have a high degree of nuance; scripts must be run in a specific order, existing data must be considered, and failures usually require manual intervention to remedy. Combine these realities with the notion of the database as a single point of failure and you create a situation where CD of database changes just feels too risky to warrant the effort.
To that, I say…. Doesn’t it at least make sense to try? Of course it does!
Please note that this article assumes the following pre-conditions;
- You are using Visual Studio for your development work
- You are using TFS 2013 or higher for Source Control
- You have a Database Project containing your Database definition
- That project can be Built inside of Visual Studio
- You know how to get around in Release Management.
What will we Continuously Deploy?
Before we begin though, there is one very important point that must be considered. You have to decide, on a per database level, what you are comfortable changing in an automated fashion?
At a high level and a bare minimum, objects in a SQL Server database fall into one of a few categories;
- Schema — the shape and definition of the data being stored
- Security — the permissions around access to that data
- Programmability — the way data is accessed and modified
Thinking back to the risks I mentioned earlier; Schema changes expose the most Risk. Failure to implement Schema changes correctly can result in loss or corruption of data. Security changes, if implemented incorrectly, can result in loss of access to data. The level of risk either of these 2 categories pose to your databases is up to you to decide.
Another thing to consider besides risk is Frequency of Change. Of the three categories noted above, what type of objects are you changing the most in your database?
More than likely, you are altering objects that fall into the Programmability category the most. These include changes to Functions, Triggers and Stored Procedures. Further, from a risk perspective, automatically changing these types of objects should generally carry less risk (assuming changes to these objects have already been tested & verified in a sandbox database).
Either way you are going to need to decide what to continuously deploy. You do that with a Publish Profile.
Create a Publish Profile
Creating a Publish Profile is actually really easy. With your Database Solution open in Visual Studio, Right-Click any of the Databases in your Solution and choose Publish. After some compilation jazz completes, you will be presented with the following dialog;
Click on the Advanced Button to get the meaty stuff…
Spend some time getting familiar with all the different options available to you in this dialog because there are a lot. Depending on your environment, you may want to spend some time going over all these options with your DBA. When in doubt, exclude everything you do not understand to avoid accidentally screwing yourself up. You can always uncheck an option later if something was missed during a deployment.
Whenever you are happy with your selections, click the OK button to close the dialog and commit your changes. Then you are going to want to Save this Publish Profile so that you can use in Release Manager.
This is going to result in an XML file with a bunch of stuff in it.
In keeping with the idea of Infrastructure as Code, it would be a good idea to commit this file to your source control system somewhere safe.
Building your Database Solution
The first thing we need to do is to get TFS to build our Database Project for us. This part can be pretty easy depending on how complex your Database Solution is.
We are going to create a simple Build Definition that contains 2 steps. The first step will use MSBuild to compile the Solution….
The Second Step will Publish the Build Artifacts. Our Solution has over a dozen Database Projects in it, but for this example I am only Publishing artifacts for one of the Databases we use.
Assuming everything works correctly, and your Solution actually compiles, you should be able to run this Build in Release Manager and move on with your day.
Releasing your Database Changes
Just as the Build definition we created was pretty simple and somewhat generic, our Release Definition will also be simple and generic.
With… a few small exceptions. In order to deploy a DACPAC, you are going to need SqlPackage.exe. This tool might not be installed by default on your servers. You can obtain the tool from Microsoft here.
You can also take advantage of the Agent based nature of Release Management. To do this, you would simply install a TFS Agent on the target SQL Servers you are using as part of your deployment. Then, for each Environment in your Release Definition, choose the appropriate Agent Queue from the dropdown;
Once you have the right tools on the right servers to execute SqlPackage, you need to configure the parameters of the deployment;
Here is an example of a working Arguments string;
/Action:Publish /SourceFile:”XXXXXX-Databases\CoreDatabasesDACPAC\DatabaseName.dacpac” /TargetDatabaseName:”$(TargetDatabase)” /TargetServerName:”$(TargetServer)” /Profile:E:\Tools\TFS-Agent\DEVELOPMENT2.publish.xml
Notice that I am using Variables for the TargetServer and TargetDatabase. As I move DB changes between environments, the only thing that changes in this instance is the Targets. I have also highlighted the Publish Profile setting which would be the file you generated earlier. This file resides in this filesystem of the server which is part of the Agent Queue I have configured for the Development environment. This file could also come from Source Control depending on how you set your stuff up.
When you are happy with all of your settings, lets save everything and give it a whirl!
Run the Release
Now that you are done, let’s run and test this Release;
twiddle your thumbs for a little bit because this could take a while…..
Success! No errors. At this point you can confirm that your changes were indeed deployed to the target database.
Conclusion
If you are a Microsoft based shop and rely solely on Jenkins for all your CI\CD needs, you could perform these same actions using that tool. But if you are already using a version of TFS which contains Release Management, it is definitely worth the time to learn how to use it.
Performing continuous deployment of database changes can not only free up a lot of time, but it also gives you better flexibility & control regarding your teams database changes. Plus, with Release Managements Approval workflows, you can even get your DBAs involved with the Release process and improve visibility and accountability of all DB changes.
With that, I hope you found this article helpful. If you have any questions or comments, please do not hesitate to leave them below.
