Microsofts SQL Server Data Tools (especially for databases) got stronger and stronger. It can compare whole database catalogs, generate deployment scripts, deploy initial table data any many more. In the end you can fully maintain (develop, build, deploy, document and reverse engineer) a database.
I’am using SSDT now for a while and sometimes I’am asking myself how to maintain the migration of a database developed in SSDT. Up to now we could avoid complex migrations. But it would be nice to have a process which handles such migrations more or less automatically. But before we dive into the different migrations scenarios I have to expose that a software version for a database is mandatory to maintain complex migration scripts. Typically you write migrations scripts to migrate a database from version x.y to x.y+1. This let you handle complex scenarios for example migrating a CDC enabled database or big fact tables.
The Software Version in SSDT projects
SSDT (to be precise DacFx; the backend of SSDT) has already a implemented a versioning mechanism. Lets take a look at the database properties:
As you can see the current database logical name and version is stored in the project file of the database. Because the project file is a simple MSBuild file we have many opportunities to extend the build behavior: for example auto incrementing a build number at the end of the version string or adding a debug or release suffix. One drawback is that this version number cannot be accessed by the script itself. It would be desirable to have a SQLCmd variable like $(DacVersion).
A marginal note – it seems to be that Microsofts preferred way maintaining migration scripts is to implement a build or deployment contributor (see here for reference http://msdn.microsoft.com/en-us/library/dn268597(v=vs.103).aspx). That implies you have to implement some C#-code which handles the migration for you. If you go this way you have to adress the normal bug fixing and deployment challenges to got it work in your development environment.
Lets get back to our „scripting“ way to handle database migrations. We now examine a SSDT project file. As you can see the DacVersion property hold our version information. This property is a normal MSBuild property.
To access this information in our scripts we need to add a SQLCmd variable. Lets call it CurrentDacVersion.
In the next step we have to edit the project file. Search for the CurrentDacVersion SQLCmd variable. You should find something like this:
This is the definition of a SQLCMD variable. This variable is connected to the MSBuild variable $(SqlCmdVar__1). Changing this to $(DacVersion) solves the problem. Save the project file and reload is in Visual Studio. Examining the project properties SQLCMD tab will show now this:
Now you can reference your current database version with the $(CurrentDacVersion) SQLCMD variable in your post- or predeployment scripts with some limitations:
- The local value overrides the default value. So the default value cannot be used anymore.
- Changing the local value will not affect the version number in the project file. Changing the version has to be done in the properties window. So this SQLCMD variable in read only for the developers.
- You need to synchronize your publishing profiles so that the variable got updated. However it is best practise to review the publishing profiles occasionally.
The next step is writing some SQL scripts which compares the target database version with the current database version and migrate some database objects (for example CDC enabled tables). I will cover this topic in my next post.