Kendra Little

22 December 2020

New SQL Change Automation Filter Features for Enterprise Teams: Migrations and Drift Report

From Enterprises to tiny startups, most developers prefer to do work in small teams these days.

But when it comes to database development, teams in Enterprises often have a hard time keeping these small teams fully separate in the development process. This is because these small teams often need to do work on monolithic database environments which are shared by many teams.

If the Enterprise hasn’t yet been able to transition to an Infrastructure as Code approach for their database environments — whether by technology like Redgate’s SQL Clone, writeable SAN snapshotting technology, or another means — these teams must work in shared development and test databases. A large number of people making changes to a complex database environment makes a developer’s job much harder.

While we believe that the ability to quickly instantiate a new, dedicated database environment on the fly adds a huge amount of value and solves many of these problems for a team, we also want to empower our customers to get started where they are and incrementally change the parts of their development and provisioning processes as they choose.

To help Enterprise teams who are working with complex shared environments, we’ve recently added two capabilities to SQL Change Automation:

• The ability to filter changes when importing migrations in the SSMS plugin
• The ability to filter objects included in the Drift Report

Videos showing these new features are below. If you prefer to read about the features, no worries — look for the links to the related documentation.

Filter Changes for Import in SQL Change Automation in SSMS (3 minutes)

You can now easily filter the list of database changes to import in SQL Change Automation in SQL Server Management Studio.

This feature will be most useful for teams who need to generate migrations from a busy shared database environment with a large number of changes in play. However, even individual users with dedicated development environments who tend to do a large amount of experimentation may find this feature useful.

Interested in doing this in the Visual Studio plugin? We’ve had that feature for a while, I show it off in this video.

• 00:00 Opening a project with a lot of pending changes
• 00:30 Viewing the number of pending changes
• 00:45 The plugin will “remember” unchecked items
• 01:00 Using the filter
• 01:30 If you change the filter, your selections are remembered
• 02:10 Importing selected changes
• 02:35 Verifying and other actions

Check out the documentation for filtering migrations in SSMS, and in Visual Studio.

Drift Reports in SQL Change Automation (31 minutes – with chapters to find the parts you need)

SQL Change Automation’s Drift Reports give you important insights into how your target database environment may have “drifted” — before you deploy. In this video, we go from defining database drift into the internals of how the drift report works, then demo the three options you have to configure the drift report.

SQL Change Automation now has a new option for the drift report, ‘ObjectsToBeUpdatedOnly’. This option filters the drift report so that it zooms in and reports ONLY on objects which are included in the current deployment. This option helps enterprise scenarios where multiple teams need to work in a busy shared development or testing environment, perhaps for a legacy monolith database.

Please note that this is applicable to SQL Change Automation projects only (not SQL Source Control projects).

• 00:36 What is database drift?
• 04:00 What is a database drift report?
• 06:28 Who is this useful for?
• 09:12 How the drift report works
• 10:54 Best practices for a first deployment
• 11:54 Why the snapshot is captured in the build process
• 14:06 How the drift report is generated on subsequent deployments
• 15:20 Drift report options on New-DatabaseReleaseArtifact
• 15:36 ModifiedObjectsOnly
• 17:18 AllObjects
• 18:06 ObjectsToBeUpdatedOnly
• 19:20 Why it’s useful that this is an option on New-DatabaseReleaseArtifact
• 20:00 Dependency considerations with ObjectsToBeUpdatedOnly
• 20:47 Documentation on New-DatabaseReleaseArtifact
• 21:33 Demo: Running a build to start off
• 22:56 Demo: Overview of how I’m creating a release artifact
• 24:00 The “flavors” of drift I’ve created in my target database
• 26:23 Overview of Changes.html and Changes.json
• 27:03 Demo: Drift report with ModifiedObjectsOnly
• 28:25 Demo: Reporting drift with AllObjects
• 29:47 Demo: Reporting drift with ObjectsToBeUpdatedOnly

If you prefer to read about this, head to our documentation on Handling Schema Drift.

When it comes to resolving drift, you have options!

• SQL Change Automation’s release artifact contains a script named DriftRevertScript.sql, which may be run manually using SQL Server Management Studio
• You may automatically run this script as part of your release process if desired. The Use-DatabaseReleaseArtifact cmdlet ignores this by default, but you may set -DriftHandling to ‘RevertOnly’ or to ‘RevertAndDeploy’, depending on the desired behavior.