Nontrivial incremental change deployment with Visual Studio database projects

Question

Let's assume that I'm doing some sort of nontrivial change to my database, which requires "custom" work to upgrade from version A to B. For example, converting user ID columns from UUID data type to the Windows domain username.

How can I make this automatically deployable? That is, I want to allow developers to right-click the project, click on "Deploy" and have this logic executed if they are using a database old enough.

I do not see any place for such login in database projects - there does not appear to be any provision for such "upgrade scripts". Is this really not possible? To clarify, the logic cannot obviously be generated automatically, but I want it to be executed automatically, as needed.

The first logical obstacle would, of course, be that the deployment utility would not know whether any such logic needs to be updated - I'd assume I could provide the logic for this, as well (e.g. check a versions table and if the latest version is <5.0, execute this upgrade, later adding a new version row).

Is this possible? Can I have fully automated deployment with complex custom change scripts? Without me having to stick all of my custom change logic into the (soon to be) huge pre- or post-build scripts, of course...

Answer 1

You can indeed check the installed version, if you register your database as a data-tier application during deployment. You can do this by including the following in your publish profile:

    <RegisterDataTierApplication>True</RegisterDataTierApplication>

This option will register the schema and it's version number in the msdb database during deployment. Be sure to change the dacpac version number between releases! We use msbuild to create dacpacs, example code for setting the dacpac version:

DacVersion=$(ProjectReleaseNumber).$(ProjectBuildNumber).$(ProjectRevisionNumber)

Having done this, you can build version-aware predeployment scripts.

-- Get installed version, e.g. 2.3.12309.0
DECLARE @InstalledVersion NVARCHAR(64) = (
    SELECT type_version
    FROM msdb.dbo.sysdac_instances
    WHERE instance_name = DB_NAME()
);
-- Get the major part of the version number, e.g. 2
DECLARE @InstalledVersionMajor TINYINT = CONVERT(TINYINT, SUBSTRING(@InstalledVersion, 0, PATINDEX('%.%', @InstalledVersion)));

IF (@InstalledVersionMajor < 5)
BEGIN;
    PRINT 'Do some nontrivial incremental change that only needs to be applied on version before 5';
END;

Checking for the version number that you are currently deploying is a little more cumbersome but can also be done. Check out Jamie Thomson's excellent blog for this technique:Editing sqlcmdvariable nodes in SSDT Publish Profile files using msbuild