Our company has been using Database Projects to build the DACPAC for a while, but not until recently that we started to use VSTS Release Management to perform the deployment as well (at least to DEV as a start). Here’s my journey…
Deployment Targets: On-premise SQL Servers of various versions (mostly SQL 2008 and 2012).
I created a Build Definition for our Database Solution/Project, and that was easy enough. A Build Solution task and a Publish Artifact task to publish the DACPAC file. Life seems pretty straightforward!
Next up, the Release Definition. Right off the bat, I chose the “WinRM – SQL Server Database Deployment” task that VSTS provides (more details on this task HERE). It looks innocent enough, until I had to dig a little deeper on how it actually works.
According to its documentation, this task will download the DACPAC to the target server, look for a location of a SqlPackage.exe on the target machine, and execute SqlPackage.exe from the target machine itself.
At first glance, it’s not such a big deal, except that I had to get clearance from several other groups to open up more network routes, allow Remote Powershell to be executed on these database servers, and other elevated privileges on the OS itself in order to do so. Our company’s policies are pretty strict on database servers, so that was not an easy feast (compared to application servers). However, a much bigger problem started to surface when I got to configure this same process for another database on another SQL server. I ran into an error that took quite sometime to identify the root cause, and it was due to the fact that SqlPackage.exe on this particular server is older than the other servers, and it didn’t accept one of the properties specified in the Publish Profile.
This makes me question the logic of this task and I do not like it. If I have to deal with different versions of SqlPackage.exe on different target servers, my life is going to be quite painful. I consulted with one of my trusted colleagues (aka DevOps partner-in-crime), and we both agreed that rather than executing SqlPackage.exe on each target server, it’s a much better approach to just execute SqlPackage.exe on the Build Server itself. That way we’re only dealing with one copy of SqlPackage.exe, so troubleshooting will be easier. Furthermore, we only need to request the SQL ports to these database servers, which are already on the approved list of opened ports. Lo and behold, I cannot find an out-of-the-box VSTS Task that will do such a thing (or perhaps I’m not looking hard enough).
I resorted to using the plain old Command Line task to accomplish this (though I wish I could come up with something fancier :). I also turned my task into a Task Group so I could re-use it for other Release Definitions. Maybe some day I’ll turn it into an official customized Task, but a Task Group will do at the moment.
This is my current preferred choice of deploying DACPAC (instead of using the out-of-the-box VSTS task), so hopefully this will help someone else out there who’s pondering about the same thing. Or drop me a line if you think I’m looking at this completely wrong.
