After successfully upgrading our database solutions to SSDT for Visual Studio 2012 we now managed to update our build definitions to deploy the new SSDT projects. These build definitions deploy both the DB and .Net code and run a batch of units.
Before the upgrade to SSDT the build definitions were using VSDBCMD.exe to deploy the database projects. In the “Items to Build” section of the “Process” tab of the build definition we specified all of the database project that we wanted to deploy.
After the upgrade to SSDT we wanted to use MSBuild to handle the build and publishing of the database projects. Rather than specifying the individual projects to build we just told it to build the solution file. We did try just building one project but we got an error saying something along the lines of the “OutputPath value is not set” and it told us to include the solution file in the build.
First of all we set up the build definition using the default XAML file and this has a bit in it that loops through the solution file and processes all the project files within it.
I was able to deploy the entire solution by just passing in the following to the “MSBuild Arguments” parameter in the “3. Advanced” part of the build definition:
/t:Build /t:Publish /p:SqlPublishProfilePath="MM443 (Simon).publish.xml"
The important bit here is the SqlPublishProfilePath parameter. ”MM443 (Simon)” was the name of the configuration that I use to deploy the solution to my local SQL Server instance. I simply created a “MM443 (Simon).publish.xml” file for every database in my solution all pointing at my local SQL Server. The build definition then just picks out this publish.xml file in each of the projects it processes and all the projects are deployed.
Once we’d got the database solution deploying to my local instance we updated the build definition to use our specific XAML file that also deploys the .Net code and run the unit tests. We just had to update the bit that deployed the databases to use MSBuild with the same parameters and it all worked fine.
One last thing we wanted was the ability to just generate the SQL scripts rather than publishing the databases. We do this in the build definition that runs against our as live environment. The generated scripts are then run manually by our DBA against the as live environment for UAT and then against live for the release. In order to just generate the publish scripts without updating the database just add the /p:UpdateDatabase=False parameter to the MSBuild arguments. So now the full value is:
/t:Build /t:Publish /p:SqlPublishProfilePath="MM443 (Simon).publish.xml" /p:UpdateDatabase=False
To automatically publish to the databases (which is the default) either remove the UpdateDatabase parameter or set the value to True.