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”:
Thereafter you can check for database drift on subsequent publishes by selecting “Block publish when database has drifted from registered version”:
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.”:
Clicking the View Report hyperlink displays the Drift Report which is represented in an XML file:
<?xml version="1.0" encoding="utf-8"?>
<Object Name="[View_1]" Parent="[dbo]" Type="SqlView" />
<ExtendedProperty HostName="[View_1]" HostParent="[dbo]" HostType="SqlView" Count="2" />
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
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!