Versioning your SQL Server Database using Team Build and Release Management


You’ve versioned your assemblies using Team Build before…

Pre-Build script path

Pre-Build script path

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

 

Data-tier application properties

Data-tier application properties

 

If any of those values are changed from their defaults, the following elements are automatically added to your .sqlproj

  • DacVersion
  • DacApplicationName
  • DacDescription

 

.sqlproj

.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.
if ($node)
{
$node.InnerText = $TextValue
}

 

Set-XmlElementsTextValue

Set-XmlElementsTextValue

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

 

SQL-RM 1.0.14164.01

SQL-RM 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.

data-tier applications

data-tier applications

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.

 

About wesmacdonald

Wes MacDonald is a Visual Studio ALM MVP, PSD, MCT and a Principal Consultant for LIKE 10 INC., a SharePoint Server, Visual Studio and Team Foundation Server Consulting Firm providing premium support and guidance services.

3 Responses to “Versioning your SQL Server Database using Team Build and Release Management”

  1. Wes…

    Thanks for the post. Looks very useful. One question for you.

    In your method Set-XmlElementsTextValue method you call Get-XmlNode. which I assume is another method you wrote. I have been trying to use SelectSingleNode in it’s place with little luck. What does your Get-XmlNode look like?

    Dave

    • Hi Dave,

      Here is the function I’m using in my PowerShell script:

      function Get-XmlNode([ xml ]$XmlDocument, [string]$NodePath, [string]$NamespaceURI = “”, [string]$NodeSeparatorCharacter = ‘.’)
      {
      # If a Namespace URI was not given, use the Xml document’s default namespace.
      if ([string]::IsNullOrEmpty($NamespaceURI))
      {
      $NamespaceURI = $XmlDocument.DocumentElement.NamespaceURI
      }
      # In order for SelectSingleNode() to actually work, we need to use the fully qualified node path along with an Xml Namespace Manager, so set them up.
      $xmlNsManager = New-Object System.Xml.XmlNamespaceManager($XmlDocument.NameTable)
      $xmlNsManager.AddNamespace(“ns”, $NamespaceURI)
      $fullyQualifiedNodePath = “/ns:$($NodePath.Replace($($NodeSeparatorCharacter), ‘/ns:’))”
      # Try and get the node, then return it. Returns $null if the node was not found.
      $node = $XmlDocument.SelectSingleNode($fullyQualifiedNodePath, $xmlNsManager)
      return $node
      }

Trackbacks/Pingbacks

  1. BPOTW 2014-06-20 | SQL Notes From The Underground - June 20, 2014

    […] Versioning your database using Team Build and Release Management Link […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: