Howto create useful dacpac versioning along with SSDT deployment?

Question

It took me almost one day to go through lots of articles and blogs to realize check-in driven continuous integration with SQL Server Database Projects (SSDT) using TFS and msbuild. Now once this is working properly, I would like to introduce versioning.

The database project's dacpac properties allow us to fill in a version in the format "x.x.x.x" that is getting published with the dacpac to Sql Server and can be queried there using:

select * from msdb.dbo.sysdac_instances_internal

This is nice but I wonder how to create something more useful and practical than a manually editable text for the version number? Of course the version number will never change as long it's up to developers to adjust it in the database project's properties...

Remembering how assembly version for example in c# project works, there is some magic increment possible by defining something like

"1.2.."

including the build number. How can this be done?

• CrazyPyro: This may be what you're after: dba.stackexchange.com/a/148487/33665

Answer 1

We finally solved this by adding the build numbers in short and long format to

a) The target database as user-defined extended properties

b) A versioning table