THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Detecting Database Drift using SSDT

One of the nice things about doing user group presentations is that when you’re putting the presentation together you invariably learn about features that were previously unbeknown to you; so it proved as I stumbled upon SSDT’s database drift detection features while researching material for my forthcoming pre-conference seminar SSDT from the ground up.

What is database drift?

You have probably experienced database drift, you just didn’t happen to refer to it as that. More likely you might have spluttered the following, perhaps sprinkled with a few expletives:

  • “Who put these tables in my database?”
  • “Who changed this view definition?”
  • “Why is this guy in db_owner?”
  • “Where has my stored procedure gone?”

In other words database drift can loosely be described as

stuff that appears, gets removed, or gets modified in your production databases that perhaps shouldn’t be

Detecting database drift using SSDT

If you’re using SSDT to manage your database schema then you probably consider the source code in your SSDT projects to be “the truth” and hence anything that appears in your databases that is not in your source code would be considered database drift.

In order to detect database drift using SSDT you must ensure that your database is registered as a Data-Tier Application. This can be done when you publish your database project (i.e. dacpac) by selecting “Register as a Data-tier Application”:

SNAGHTML450c41

Thereafter you can check for database drift on subsequent publishes by selecting “Block publish when database has drifted from registered version”:

SNAGHTML46b2db

If you check that box and database drift has occurred then the publish operation will fail and you see an appropriate message in the Data Tools Operations pane, “Publish stopped. The target database has drifted from the registered version.”:

SNAGHTML4bd20f

Clicking the View Report hyperlink displays the Drift Report which is represented in an XML file:

<?xml version="1.0" encoding="utf-8"?>
<DriftReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02">
  <Additions>
    <Object Name="[View_1]" Parent="[dbo]" Type="SqlView" />
    <ExtendedProperty HostName="[View_1]" HostParent="[dbo]" HostType="SqlView" Count="2" />
  </Additions>
  <Removals />
  <Modifications />
</DriftReport>

In this case a view called [dbo].[View_1] has been added to the target database. That view did not exist in the dacpac that was most recently deployed against the database thus the publish operation fails. Keeping one’s deployed databases as “clean” as possible is something that I am all in favour of so personally I think this is a pretty cool feature.

Generating a drift report from the command-line

The drift report can be generated by the command-line tool sqlpackage.exe. To do so you need to define:

  • the action to be DriftReport
  • a target server & database
  • an output file

>SqlPackage.exe /A:DriftReport /tsn:"(localdb)\Projects" /tdn:"Database1" /op:DriftReport.xml

SNAGHTML57a506

As far as I know there is no support for generating a drift report from SQL Server Management Studio (SSMS). I’m hoping that changes so that this feature gets more visibility.

 

If you have any comments stick them in the comments section below!

@Jamiet

Published Thursday, April 04, 2013 11:31 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kent Chenery said:

Hi Jami,

SSMS does have drift detection, but its not that obvious where it is.  Once you've registered a database as a data-tier application you can "upgrade" it through SSMS (Right click on the DB --> Tasks --> Upgrade data-tier application).  Select your DACPAC file, click next and the wizard does the drift detection (listed as Detect Change).

You can then save the report and choose to proceed if you're happy to.

In saying that, I've never used it in anger.  We tend to work like this: Just before a release, do the drift detection manually.  Look at the report and decide what to "wash back" into the project.  Thats then pushed through our CI to bring dev, test, stage, etc "into line".  When we're happy its all pushed into prod.  Its not perfect and we're refining all the time but its working for us at the moment.

April 4, 2013 6:02 AM
 

jamiet said:

Thanks for that Kent.

How do you "do the drift detection manually"? Do you use Schema Compare?

-Jamie

April 4, 2013 11:23 AM
 

Jack Corbett said:

Kent,

Do you need to be using SSMS 2012 to do this?  I don't see the Upgrade Data-Tier application option in the Tasks menu in SSMS 2008R2.

June 12, 2013 12:03 PM
 

Matt Potter said:

I know this is off at a slight tangent, but fairly similar - I'm trying to automate doing a schema compare as part of our build process without much success and was wondering if there's any way you know of to do a drift report between 2 databases or even better a schema compare.

All I can find are examples of this in VSDBCMD, but since we've upgraded to SSDT I can't find any way of doing this. It seems like this has been released without porting over all the previous functionality and i'm trying to find a workaround without having to code it in c#.

Any advice would be much appreciated.

Thanks,

Matt

November 4, 2013 1:59 PM
 

Peter Schott said:

@Matt, isn't that what Jamie mentioned with his SQLPackage syntax to generate the drift report? Add that in with a command to build the package, then compare the project to the package or the other way around. You should also be able to specify a source DB/connection in addition to the target DB. I've done this and normally use the DB as target compared to the package as source. It will generate an XML file. For our releases, we'd automate this with a command line task to run SQL Package.

November 4, 2013 2:09 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement