Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Versioning SQL Server Databases using SSDT

5.00/5 (6 votes)
8 Apr 2013CPOL4 min read 53K   403  
How to effectivley version your SQL Server databases using SQL Server Data Tools

Introduction

How to version SQL Server databases using SQL Server Data Tools (SSDT) and all the cool features we can use to manage the versions of our SQL Server databases.

Background

SQL Server Data Tools (SSDT) and the Data Tier Application Framework (DACFx) are add-ons for Visual Studio and SQL Server that allow us to better manage our SQL databases from development through to deployment.

Very, very briefly, SSDT gives us the visual studio tools to develop our databases and DACFx allows us to deploy these databases to SQL Server and manage them.

There are some very nice features available that allow us to version our databases but as I want to show it is more than just adding a version number to the database.

To learn more see the msdn page and this code project article. This article will assume a basic understanding of dacpacs, how to create them and how to deploy them.

Examples here will be shown using Visual Studio 2012, SQL Server 2012 and the latest (December 2012) version of SSDT. But it should all apply to Visual Studio 2010 and SQL Server 2008

Databases as Data Tier Applications

The key concept that SSDT gives us is that we can create our databases as 'Data Tier Applications'. This promotes our database from an ordinary database to a fully fledged application installed on a machine. I like to think of it as the difference between just copying an exe and dll's onto a machine and installing an application using an MSI.

Lets say we have developed a database using SSDT in Visual Studio and we now want to deploy it as a data tier application. We can do this direct from Visual Studio or from the command line.

If you open the attached source code you will see an example SSDT project that defines a very simple database called 'Library'.

We can publish this database from Visual Studio by right clicking the project in the solution and selecting 'Publish..' Fill in the target database connection info and hit publish and the database will be created. To create a data tier application just tick the box 'Register as Data-tier Application'.

Image 1

This can also be done through the command line by using the 'sqlpackage.exe' utility that comes with DACFx. On my dev machine I can find it in 'C:\Program Files\Microsoft SQL Server\110\DAC\bin'

sqlpackage.exe /Action:Publish /SourceFile:$DacPacPath /targetServerName:$TargetDataSource /TargetDatabaseName:$TargetDatabaseName /p:RegisterDataTierApplication=True

This is doing more than just publishing our database now. The database is 'installed' on the target SQL Server and this gives a few nice extra features.

Versioning Our Databases

We can set the version number of our database through the properties dialog in Visual Studio.

Image 2

This version number is then stored on the SQL Server and accessible through the msdb database via the following query

SQL
SELECT * FROM [msdb].[dbo].[sysdac_instances]  

This gives us the version number of our data tier application as well a host of other information.

Image 3

But there is a lot more to Data-Tier Applications than just version numbers. 

Talking About Drift

When a data tier application is created, an empty copy of the database schema is actually stored on the sql server. This schema gives us a view of what the official database should look like.

'Drift' measures to what extent the schema of the live database differs from the version that was actually registered. Directly after install there should be no drift. This can be a useful feature in production environments to keep track of any changes that are made to our database that do not go through the formal deployment process.

As an example imagine a DBA executes some scripts on against your production database to tweak performance or get past a deadlock issue. This change will come up in a drift report as the DBA made the changes without going through the dacpac publish route.

Measuring Drift

We need to use the sqlpackage command line utility to create a drift report

sqlpackage.exe /Action:DriftReport /TargetConnectionString:"Server=$DataSource;Database=$TargetDatabase;Integrated Security=SSPI;" 
/OutputPath:$DriftReport

As an example, if I run the following against my 'Library' example database straight after I publish it, then I will get an empty drift report.

"C:\Program Files\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /Action:DriftReport 
  /TargetConnectionString:"Server=Localhost\SQL2012;Database=Library;Integrated Security=SSPI;" 
  /OutputPath:"d:\Library_Drift_Report.xml"

But if now make a change to the database schema by manually deleting the foreign key constraint on the 'Books' table directly through the management studio, the drift report will look like this

XML
<DriftReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02">
  <Additions />
  <Removals>
    <Object Name="[ForeignKeyConstraint_Book_Author]" 
          Parent="[dbo].[Books]" Type="SqlForeignKeyConstraint" />
  </Removals>
  <Modifications />
</DriftReport>

Blocking Deployment to a Drifted Database

It is possible to block publishing a database update when the database has drifted from its registered version. This is a useful feature if we want to ensure a database update made through publishing a dacpac does not overwrite any changes made manually to the database and highlights any strange activity on our database.

The option is set through the publish dialog in visual studio or on the sqlpackage command line.

Wrapping Up

SSDT is a great tool in my opinion. There are bugs, there are things that don't quite work the way I want them to but it is under constant development (currently) and new versions are being released regularly.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)