I am working on a database management source using SQL Server Data-Tier applications. I created a SQL Server database project in Visual Studio, and now I am in the process of automating the deployment of DACPAC through powershell. By checking deployment options using the DacFx Deployment method, I play with deployment options, in particular BlockWhenDriftDetected .
Now, based on my understanding, database drift occurs when you add some type of SQL Server object (table, function, sproc, etc.) to an external deployed database on SQL Server after you deploy and register the specified database data in the form of a Data-Tier Application on the specified server. Since I tested this, though, there seems to be something a bit with this drift detector.
When I install / register DACPAC for the first time, of course, everything is in order and there is no drift. However, if the only thing I changed is the version number in the properties of my database project in VS, rebuild the project and then try to redeploy the DACPAC file using DacFx, it detects the database drift.
To deploy my DACPAC, I use the following command line 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)
Repeated execution of the above code after updating the version number and recovery, I get an 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 things more confusing when I check the database drift ...
Write-Host $dacservices.GenerateDriftReport("MyDatabase")
I get an empty report, that is, everything should be fine:
<DriftReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02"> <Additions /> <Removals /> <Modifications /> </DriftReport>
Is this a mistake or am I missing something? I am tempted to ignore all this drift detection and provide strict access to the production database, trying to limit changes after the fact. Any help would be greatly appreciated.
Thanks.