How do I get a drift report?

PeterDanielsCRB

I used

New-DatabaseReleaseArtifact 

to effectively compare my local dev DB to the prod DB with the intent of getting a drift report and drift correction script.  However, all I got was the changes.html file and an update.sql

There are quite a few "updates"/changes, so I wold also expect to see a drift report and sql.  what am I missing here?

Here's here is the code I'm using:

<div>$devLocalConnection&nbsp;=&nbsp;New-DatabaseConnection&nbsp;-ServerInstance&nbsp;$SourceSqlInstance&nbsp;-Database&nbsp;$SourceDatabase</div><br><div>$prodConnection&nbsp;=&nbsp;New-DatabaseConnection&nbsp;-ServerInstance&nbsp;$prodSqlinstance&nbsp;-Database&nbsp;$prodDatabase&nbsp;-Username&nbsp;$UserName&nbsp;-Password&nbsp;$Password</div><br><div>$releaseArtifact&nbsp;=&nbsp;New-DatabaseReleaseArtifact&nbsp;-Source&nbsp;$devLocalConnection&nbsp;-Target&nbsp;$prodConnection</div><br><div>$releaseArtifact&nbsp;|&nbsp;Export-DatabaseReleaseArtifact&nbsp;-Path&nbsp;<span>$OutputFolder</span></div>

PeterDanielsCRB

I figured it out.

First, I tried just adding:

-DriftFltering AllObjects

Still do luck, so I tried creating a build artifact to use as -Source instead of using my local dev DB.  This gave me a helpful error:

"Error performing schema comparison: The database snapshot was saved by a newer version of SQL Compare"

So, I updated my SCA install (actually my whole SQL Toolbelt), and now I am getting the full drift html report and the DriftRevertScript.sql.

Cool.

Thanks again, Kendra!

Cheers,

-Peter

PeterDanielsCRB

The code block did not format well.  Let's try an image:

Kendra_Little

Hi Peter,

The first thing I should validate is to make sure that a deployment has happened via SQL Change Automation to the production database in the past. (The drift is since the last deployment -- so there's never a drift report on first deployment.)  If this one is the case then it's possible to use SQL Compare to compare your project to the database to identify any key differences before first deployment.

If that's not it, then my second guess is that the option "DriftFiltering" on New-DatabaseReleaseArtifact may be playing a role here. The default value for it is ModifiedObjectsOnly. With this default...

Under this default behavior, the drift report will not include objects that exist in the target database but do NOT exist in the SQL Change Automation project.

One possibility is that if the changes were to new objects that were created in the target which aren't in the project, these wouldn't show up as drift. If this might be the case, you can set -DriftFiltering to "AllObjects" to consider objects added to the target database as drift. 

Could this be what is going on?

Kendra

PeterDanielsCRB

Thank you, Kendra.

All prod deployments happen via Azure DevOps release pipeline, which uses the Redgate SCA plugins.  I just checked, and did see a single row in the dbo.__SchemaSnapshot table from the latest deployment.

I wonder if I need to use the source project instead of my local dev DB when using New-DatabaseReleaseArtifact.  Or if I even need to make a build artifact.

I def considered just using SQL compare, too.  Was just hoping to see the drift report in action.

I will also try using -DriftFiltering AllObjects.

Cheers,

-Peter