Site icon Eitan Blumin's blog

T-SQL Tuesday #152 – Which version of my database was deployed? It depends!

Eitan Blumin

This month’s T-SQL Tuesday is hosted by Deb the DBA (b|t), who invites us to give a long rant about a question that a coworker could be asking us, without knowing what they’ve just gotten themselves into by doing that.

Well… I’ve got just the thing 🤭

link back to Deb’s invitation post

Database DevOps and managing one’s database in Source Control are becoming more and more popular. Which is great! With it, many interesting questions are being raised about best practices and methodologies.

“What is SSDT“, you ask? Oh, you didn’t? Well, let me tell you anyway! SSDT is the go-to solution from Microsoft for versioning SQL Server databases and performing state-based deployments (and it’s free!). It has many useful capabilities for developing and publishing changes from your SQL Database project to your SQL Database in production (or wherever).

One of the things that are not so clear about SSDT specifically and database versioning in general, is how should one identify which “version” of your database project was last deployed to your server?

There are many ways to go about doing this. Which is the best one? As with most things having to do with relational databases in general and with SQL Server specifically, the answer is “…it depends“.

In this article, I hope to review several interesting methods of achieving “database versioning”. Some are well-known and obvious, and some are less so. Either way, it should be interesting. So, sit back, enjoy, and courageously deal with the consequences of asking.

On this page:

Data-Tier Application

The “built-in” solution that comes with SSDT (and SqlPackage.exe specifically, which is the command-line utility used for deploying SSDT projects), is something called “Data-Tier Application“.

It’s essentially a way to register your database as a sort of “application instance”, by storing some metadata in a specific system table in the msdb database.

This table is dbo.sysdac_instances, and as I said, it can be found in the msdb database.

It has interesting columns such as instance_name and database_name identifying the DacPac name and database name respectively, type_version which identifies the “semantic version” of the database, and type_stream which is a binary representation of the database schema objects without the data.

The most useful feature when registering your database as a data-tier application is the ability to BLOCK further deployments if schema drift was detected in the database (this is implemented using the type_stream column in sysdac_instances).

How can versioning be managed in a Data-tier application?

To change the semantic version of your database project, so that it would be reflected in the type_version column in sysdac_instances, you’ll need to right-click on your project and go to Properties; in the Project Settings page, under “Data-tier Application (.dacpac file)“, click on Properties… and in the dialog box that shows up, you’ll be able to change the version:

Naturally, you’ll need to do this every time you need to bump up your version number. Which could be rather cumbersome as it involves many clicks.

This change is reflected in your .sqlproj file in an XML element inside <PropertyGroup> called <DacVersion>. Unfortunately, it wouldn’t be much easier to change this value by editing the XML directly, as you’ll need to go outside of Visual Studio to do so.

Also, to query the version of a specific database, you’ll need to have proper access and permissions in the msdb database, and also to make sure that the instance_name of your database is unique at the server level. In other words, deploying the same DacPac to multiple databases on your server, while registering each of them as a separate Data-tier application… is complicated, to say the least.

Next, I’ll be showing you several versioning methods alternative to this one, but please remember that Data-tier application is the ONLY method that has built-in capabilities to detect version drifts and can use that to block further deployments. If that is not something that’s crucial for your use case anyway, then you might find some of the other methods below better suited for you.

Code-Driven Versioning

One common alternative method of versioning your database is by having some kind of a “module” in your database responsible for returning the current database version.

This could either be a scalar function, a view, or a stored procedure.

For example:

CREATE FUNCTION dbo.GetDatabaseVersion()
RETURNS varchar(50)
WITH SCHEMABINDING
AS
BEGIN
	RETURN '1.0.0.0'
END

Unlike the Data-tier application, this is very easy to implement and maintain. It doesn’t require any special permissions to a system database, and you can change it very quickly by just double-clicking on the relevant function/view/procedure in your SQL Database project and editing it.

This is probably the easiest and simplest solution among all others here.

Data-Driven Versioning

Another common method is to store the database version as a value in a dedicated table in your database.

Several database deployment tools and code-first migration tools use this method, most notably FlyWay and Entity Framework.

But you can totally implement something like this on your own in SSDT using a Post-Deployment script that inserts or updates the value in a table. For example:

MERGE INTO dbo.__DatabaseVersion AS Trgt
USING (VALUES
('1.0.0.0')
) AS Src(DBVersion)
ON 1=1
WHEN MATCHED THEN
	UPDATE SET DBVersion = Src.DBVersion
WHEN NOT MATCHED BY TARGET THEN
	INSERT (DBVersion)
	VALUES (DBVersion)
;

Changing this version would require changing the corresponding Post-Deployment script. And to retrieve this version for a given database would require you to query from this table.

It’s a bit more complicated than the Code-Driven method. But if you do it right, you can make it store more than just one data point for a version. For example, you could also use it to store a history of deployed versions with some useful metadata (such as deployment date, which user made the deployment, from which hostname, and more).

For more advanced versioning scenarios, you could even maintain a separate version for different “components” within your database, if you have something like that. For example:

MERGE INTO dbo.__ComponentVersion AS Trgt
USING (VALUES
('Sales','1.0.0.0'),
('WebApi','2.0.1.123'),
('DataWarehouse','1.0.0.357')
) AS Src(Component, ComponentVersion)
ON Trgt.Component = Src.Component
WHEN MATCHED THEN
	UPDATE SET ComponentVersion = Src.ComponentVersion
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Component, ComponentVersion)
	VALUES (Component, ComponentVersion)
;

As it is data-driven and designed at the database level, this could be anything you want it to be.

Extended Properties

Hey, did you have a chance to read my blog post about Extended Properties and what they could be used for?

Well, here’s one more use case for extended properties! You can set a database-level extended property to store the current version of the database.

Extended Properties are actually a supported object type in SSDT, doncha know.

You can save it in your project as something like this:

EXEC sp_addextendedproperty @name='DBVersion', @value ='1.0.0.0'

Upon deployment, the DacPac will automatically know whether to add this as a new property or update the existing property.

The catch here is that it’s not very straightforward retrieving this value, as it would require querying from a system table and doing a type conversion. For example:

SELECT CONVERT(varchar(50), [value]) AS DBVersion
FROM sys.extended_properties
WHERE major_id = 0 AND minor_id = 0
AND [name] = 'DBVersion'

You could create a scalar function that makes this more accessible, but then you’d probably be better off going with the Code-Driven method in the first place as it would provide the same functionality – with less effort.

SQLCMD Variable Versioning

SQL Database projects in SSDT also have the ability to utilize SQLCMD Variables.

These SQLCMD Variables can then be specified and changed during deployment time and/or in Publish Profiles. Therefore, it is possible to create a SQLCMD Variable to represent your database version.

This SQLCMD Variable could be used within a scalar function or view (similar to the Code-Driven method), like so:

CREATE FUNCTION dbo.GetDatabaseVersion()
RETURNS varchar(50)
WITH SCHEMABINDING
AS
BEGIN
	RETURN '$(DBVersion)'
END

It could also be used as part of a Post-Deployment script like in the Data-Driven method, like so:

MERGE INTO dbo.__DatabaseVersion AS Trgt
USING (VALUES
('$(DBVersion)')
) AS Src(Ver)
ON 1=1
WHEN MATCHED THEN
	UPDATE SET DBVersion = Src.Ver
WHEN NOT MATCHED BY TARGET THEN
	INSERT (DBVersion)
	VALUES (Ver)
;

To change your database version in this case, you’ll need to change the corresponding SQLCMD Variable in the project properties

However, this on its own will NOT affect your actual deployments. You’ll be better off creating a Publish Profile which you would be saving as part of the project in source control, and edit that profile whenever you need to change the version.

It can also be set when running the SqlPackage.exe utility during deployment:

SqlPackage.exe /Action:Publish /V:DBVersion="1.5.4.3"

This whole mess isn’t much easier to manage than the other methods, as working with Publish Profiles and SQLCMD Variables can get complicated sometimes.

But if, for whatever reason, the database version needs to be determined during deployment time rather than during design time, then this could be a good fit.

Assembly-Driven Versioning

Did you know that you can have CLR objects inside your SSDT project?
Yeah, of course you knew this. I’m sure you did.

Well, you can also specify an Assembly Version for your SQL Database project along with it.

It looks kinda similar to how you would specify the version for your project as a Data-tier application, but it’s in another tab. Specifically, in the “SQLCLR” tab of your project properties. And you can change it by clicking on the “Assembly Information…” button which will let you change the assembly version:

You can also change it pretty quickly by expanding the “Properties” node in your project tree and editing the file named AssemblyInfo.cs

Once you have that, create a new SQL CLR C# User Defined Function:

And then simply place the following single line of code in it:

return typeof(UserDefinedFunctions).Assembly.GetName().Version.ToString();

This simple line of code will output the assembly version that you specified previously.

The whole function code should look something like this

using System.Data.SqlTypes;
using System.Data.Sql;
using Microsoft.SqlServer;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GetDBVersion()
    {
        // Put your code here
        return typeof(UserDefinedFunctions).Assembly.GetName().Version.ToString();
    }
}

It’s very short, very simple. Just copy and paste.

This will also create a corresponding scalar SQL function that you’ll be able to use for retrieving the database’s current version, similar to the Code-Driven method.

The difference here is that this adds a sort of added “layer” of security since now no one can simply edit the code of a function or change the value in a table to “accidentally” change the database version without going through your SSDT project first.

On the other hand, this requires the use of CLR in your database (duh) which isn’t always possible in some scenarios and may complicate first-time deployments due to the need for assembly code-signing.

A significant benefit here that is unique to this method, is that due to the fact that this is based on .NET Assembly versioning, it can also be shared with .NET application projects.

Yeah, it’s kinda crazy, I know. But it can work pretty well for scenarios where a .NET application needs to share the same project version as its corresponding database.

This “sharing” of project versions can be done using the “Add As Link” capability in Visual Studio to make one project “link” to a file from another project without duplicating it. You can do so with the AssemblyInfo.cs file or one similar to it (for example, a .cs file that only contains the AssemblyVersion attribute. Just be careful to avoid duplicates as there can only be one such attribute per project).

This way, you will maintain a single-source-of-truth representing your solution’s version, and changing it will automatically affect all projects dependent on it – including your SQL Database project(s).

External Settings File

Speaking of sharing versions with an application, what if you don’t have a .NET application? What if it’s something else? For example, something that stores its project version in a JSON configuration file of some kind?

For example, let’s say that we have an app with a settings file that looks like this:

{
    "ConnectionStrings": {       
      "DefaultConnection": "Server=MySrv; Database=MyDB; Trusted_Connection=True"        
    },
	"Version": "4.5.6.7"
}

Well, SSDT doesn’t really know what to do with JSON files.

But if we do it right, we could utilize our Post-Deployment scripts to load this file into our database, and store that “Version” somewhere in our database like in a table or something.

To retrieve the contents of a JSON file, we could use the OPENROWSET function using the BULK and SINGLE_CLOB options, like so:

DECLARE @JsonDoc nvarchar(MAX)

SELECT @JsonDoc = BulkColumn
FROM OPENROWSET(BULK N'C:\src\Project\appSettings.json', SINGLE_CLOB) AS Doc

SELECT JSON_VALUE(@JsonDoc, '$.Version')

Once we have the “Version” inside the SQL Server, we could store it like you would with the Data-Driven method.

If you’re feeling fancy, you could replace the file’s path with a SQLCMD Variable which could be different in each environment (dev/test/QA/staging/production/etc.) and so it would be set during deployment time. For example:

DECLARE @JsonDoc nvarchar(MAX)

SELECT @JsonDoc = BulkColumn
FROM OPENROWSET(BULK N'$(SettingsFilePath)', SINGLE_CLOB) AS Doc

SELECT JSON_VALUE(@JsonDoc, '$.Version')
What if your settings file is actually an XML file instead of JSON?

Sure, that could work too. Let’s say that your settings file looks like this:

<Settings>
    <ConnectionStrings>       
      <key name="DefaultConnection" value="Server=MySrv; Database=MyDB; Trusted_Connection=True" />
    </ConnectionStrings>
	<Version>4.5.6.7</Version>
</Settings>

In that case, the version retrieval in T-SQL would look like this:

DECLARE @XmlDoc nvarchar(MAX)

SELECT @XmlDoc = BulkColumn
FROM OPENROWSET(BULK N'C:\src\Project\web.config', SINGLE_CLOB) AS Doc

SELECT CONVERT(xml, @XmlDoc).value('(Settings/Version/text())[1]','varchar(50)')

Same concept, just a different syntax.

Honorable Mention – Blocking Version Drift

As I said above, the Data-tier application method is the only one capable of stopping deployments when version drift is detected.

But it’s worth mentioning that there are ways of preventing database version drift entirely.

The obvious one would be the Principle of Least Privilege, as in – don’t give DDL permissions to any user except the one performing the DacPac deployments. But, alas, I know all too well how much people love giving sysadmin permissions to everyone. (sigh) Not you, though. I know you’re better than that.

Another method would be creating a Database Trigger for DDL events, which would automatically ROLLBACK and throw an exception when an unauthorized schema change is attempted.

There are also some 3rd party tools that do this, such as ApexSQL and Red-Gate, that claim to “lock” objects from being changed. Although, while they’re pretty nice tools, they’re most likely overkill for just this purpose. At the end of the day, they still create a DDL trigger behind the scenes to do this.

Conclusion

Oh boy, this article turned out pretty long, didn’t it? And we got to some really crazy stuff towards the end there. I bet some of you started sweating reading all of that nonsense. 😅

But seriously, I hope that I presented enough varied solutions here so that there would be something for everyone, and anyone reading could get some useful ideas from it.

Thank you for coming to my TED talk 😊

Exit mobile version