You’ve versioned your assemblies using Team Build before…
I’m sure you are all aware of the nice little PowerShell script “ApplyVersionToAssemblies.ps1” that updates your C# AssemblyInfo.cs files, you can find it on codeplex here. It is part of a great project named Community TFS Build Extensions which provides a place for build engineers to share workflow activities, build process template files, PowerShell scripts and tools for Team Foundation Build.
But what about a SQL Server Project? If we take a look at the project properties we can see the following
If any of those values are changed from their defaults, the following elements are automatically added to your .sqlproj
Unfortunately the PowerShell script above does nothing for us. Luckily we can easily tweak that same PowerShell script to inject our version information into the .sqlproj project file. This particular method may not be suitable for you but you could also inject the version information into a post-deployment script if you wanted to insert the version number into a SQL table….
We’ll continue with the DacVersion approach which allows us to leverage the functionality found in a data-tier application.
What is a Data-tier Application? Here is the definition from MSDN:
A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects – like tables, views, and instance objects, including logins – associated with a user’s database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.
I am going to tweak the ApplyVersionToAssemblies.ps1 by adding the following lines to the bottom of the script. Note: I am referencing a function named Set-XmlElementsTextValue which I added to the beginning of the script. The function is only a few lines which does the following:
$node = Get-XmlNode -XmlDocument $XmlDocument -NodePath $ElementPath
# If the node exists, update its value.
$node.InnerText = $TextValue
The Build Number Format defined in the Build Process Template is the following: $(BuildDefinitionName) 1.0.$(Date:yy)$(DayOfYear)$(Rev:.rr)
When I execute my build definition which also triggers Release Management (in this example). You’ll notice the version number is 1.0.14164.01
In Release Management I have a component named DACPAC Database Deployer v12 which calls sqlpackage.exe with the .dacpac from the drop folder, I’ve added the parameter RegisterDataTierApplication=True to the components command-line options. I can easily check my database instance to see the version of the data-tier application that is deployed by executing the following query:
SELECT * FROM [msdb].[dbo].[sysdac_instances]
This is the result I get back when I run it against my database instance, you’ll see that the version matches exactly what was created by Team Build and my SQL-RM Build Definition.
If you have any questions or comments, please leave them below. This is one method you could use to identify the version of your SQL database in your release pipeline.