Database DevOps – SSDT and TeamCity

Using Visual Studio and SSDT you can immediately publish a fresh database or update an existing one by simply right clicking the database project and choosing ‘Publish’. This will bring up a dialog that lets you edit the connection string to your database and set various options related to the publish.

At this point you have a choice – whether to register your published database as a Data Tier Application or not. Please do so.  There are two distinct advantages

• You can enable database drift detection.
• You can query the server to see what version database is published there, when and by whom.

Registering a database as a Data-Tier Application stores a copy of the current state of the database schema in system metadata tables as well as associated information relating to the publish itself.  To see quick info you can run

select * from sysdac_instances

against the msdb database on the server you published to.

Database drift is a very useful feature that allows you to check whether somebody has directly altered the database in some way since the last publish. I know this is a problem for many small teams – a query is a little slow so an index is added, a default was applied to a column because an app was throwing exceptions further down the pipeline and it had to be fixed quickly. This allows you to detect that, find the changes and if necessary bring them in to your project or else delete them. I always choose the option to block publish when database has drifted from registered version.

Now that you have selected your server, registered the database as a DTA and prevented drift, save the profile in the form .publish.xml. This isn’t just so that you can double click the profile to immediately publish (although you can of course) this is so that when TeamCity (you ARE using CI aren’t you?) builds your SSDT solution you can tell it which profile to use to automatically publish your database.  How good is that?

In the TeamCity build step that actually builds your solution you can select a target of ‘Build Publish’ and then in your command line parameters add the following

/p:SqlPublishProfilePath=<profilename> /p:DeployOnBuild=true

Where <profilename> is the full name of your profile e.g. myserver.publish.xml.

Done!  An easily updated, version controlled database that watches for people messing about with it on the server and lets you know.  What more could you ask for?