I am working on source controlling a database using SQL Server Data-Tier Applications. I've created my SQL Server Database Project in Visual Studio and am now in the process of automating the deploy of the DACPAC via powershell. While testing out the deploy options with the DacFx services Deploy method, I have been playing around with the deploy options, specifically BlockWhenDriftDetected.
Now from my understanding, database drift occurs when you add some type of SQL Server object (table, function, sproc, etc.) to the externally deployed database in SQL Server after you have deployed and registered said database as a Data-Tier Application on said server. As I have been testing this out though, something seems a bit off with this drift detection.
When I deploy/register the DACPAC the very first time, of course all is fine and there is no drift. Yet, if the only thing I change is the version number in the properties of my Database Project within VS, rebuild the project, and then attempt to re-deploy the DACPAC file using DacFx, it detects a database drift.
To deploy my DACPAC, I use the following powershell code:
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll"
$dacservices = New-Object Microsoft.SqlServer.Dac.DacServices "server=(localdb)\v11.0;trusted_connection=true;"
$dacpac = [Microsoft.SqlServer.Dac.DacPackage]::Load("C:/Projects/Sample/DacProject/bin/Debug/MyDatabase.dacpac")
$deployOptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployOptions.RegisterDataTierApplication = $true;
$deployOptions.BlockWhenDriftDetected = $true;
$dacservices.Deploy($dacpac, "MyDatabase", $true, $deployOptions)
Running the above code again after updating the version number and rebuilding, I'll get the drift detected error:
Exception calling "Deploy" with "4" argument(s): "Could not deploy package.
Error SQL0: Database has drifted from its registered data-tier application.
Also, to make matters more confusing, when I check the database drift...
Write-Host $dacservices.GenerateDriftReport("MyDatabase")
I get an empty report, meaning everything should be fine:
<DriftReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02">
<Additions />
<Removals />
<Modifications />
</DriftReport>
Is this a bug or am I missing something? I'm tempted to ignore this whole drift detection and enforce strict access to the production database in an attempt to limit changes after the fact. Any help would be greatly appreciated.
Thanks.