SchemaDrift

Overview

Development, test and production are supposed to have the same schema, and over time they stop having it. SchemaDrift answers: what is different between these two databases, and what would it take to make them match? It is a companion program that ships with Database Health Monitor and opens in a window of its own.

  • Step 1: choose the source (the side treated as correct) and the destination.
  • Step 2: choose which object types to compare and the comparison options, then click Compare.
  • Step 3: read the Identical, Different, Only in Source and Only in Destination tabs, and write a script, a difference list or an attestation.

SchemaDrift is a beta product and says so on its Beta Notes tab. Table differences are flagged for review rather than scripted (see below).

Where to find it

Route How
Menu bar Tools → SchemaDrift
Command line SchemaDrift.exe with the switches listed below, for scheduled jobs and pipelines

An installed copy keeps SchemaDrift beside the Database Health Monitor program. If it cannot be started, Database Health Monitor says which file it tried to run.

Step 1: source and destination

Each side has a Type:

Type Meaning
Connect To Database Click Connect, choose the instance and authentication, then pick the database from the Database list.
Source Control / File System Click Browse and choose a folder of scripts, one file per object.

A database can be compared to a database, a folder to a database, or a folder to a folder. Under each side the Schema fingerprint is shown once the side has been read: a hash of that schema that is the same whenever the schema is the same.

Step 2: what to compare

Database Objects to Compare has a check box for each of: Tables, Views, Stored Procedures, Functions, Indexes, Primary Keys, Foreign Keys, Unique Constraints, Defaults, Rules, Triggers, DDL Triggers, Synonyms, Schemas, Sequences, Partition Functions, Partition Schemes, XML Schema Collections, Assemblies, Users, User Permissions, Roles, Message Types, Contracts and Queues. The Uncheck All button clears them so you can tick only what you need. Service Broker queues are not compared in this version.

Comparison Options:

Option Effect
Ignore Case Names and text that differ only by case count as identical.
Ignore Whitespace Differences Differences in spacing are not reported.
Ignore Collation differences Collation is left out of the comparison.
Ignore Fill Factor differences Index fill factor is left out of the comparison.
Include Extended Properties Extended properties are compared as well.
Ignore MSMerge Replication Differences Merge replication objects are left out.

An option can hide a real difference, so a result with options on is only as complete as those options allow. The Status box shows progress, and Cancel stops a comparison that is running.

A whole setup (both sides, authentication and every option) is saved with File → Save Profile or Save As Profile and loaded again with Open Profile. The profile is also what the command line uses.

Reading the result

Tab Contents
Identical Objects that match on both sides.
Different Objects on both sides whose definitions differ.
Only in Source Objects that exist only on the source.
Only in Destination Objects that exist only on the destination.

Each grid lists the object and its type. Right click a row for the actions that apply to it:

  • Migrate one stored procedure or function from one side to the other as an ALTER, when that side is a database. Added objects are created in the target database. A failure is reported with the error and nothing is left half done.
  • Migrate or Save All to the folder, when that side is a folder: one .sql file per object, in a sub folder for its type. File → Script → Save All to Source Directory and Save All to Destination Directory do the same for everything.
  • Copy to clipboard, Copy All From Grid, and Copy all diff, only in source and only in dest.

The migration script

From the command line (/scriptTo) SchemaDrift writes one script that brings the target side into line with the model side, in dependency order:

  • Objects only on the model are created.
  • Views, procedures, functions and triggers that differ are deployed as an ALTER, so the text on the target ends up identical to the model.
  • Indexes and constraints that differ are dropped and recreated.
  • Objects only on the target are left alone, with the drop written commented out, unless /includeDrops is given. Tables are never dropped automatically.
  • Anything that cannot be scripted safely is written to a review section at the end behind a RAISERROR, so the script stops there instead of running past it. This covers a table that differs on both sides, assemblies, partition functions and schemes, encrypted objects, and users, roles and permissions. The header says how many objects were left for review, and which options and object types were in effect.
  • A sequence that differs is dropped and recreated, which returns it to its start value. The script warns where this happens.

Read the script before running it. Point /scriptTo at an existing folder instead of a file to get one numbered file per object plus a _deploy.sql that includes them in order for sqlcmd.

Attestation and fingerprints

File → Generate attestation, or /attestTo, writes two files from one path: a printable HTML report and a JSON document. It names both servers and databases, the versions and editions, every option in effect, who ran it, the status of each object and a schema fingerprint for each side, and carries an HMACSHA256 signature made with a key kept in your own SchemaDrift settings. No password is stored in it.

/verifyAttestation reconnects to both databases, takes the fingerprints again and compares them with the document: exit code 0 when they still match, 1 when they do not. That is the evidence that nothing else moved after a deployment.

Command line

The leading slash is optional (a dash works), case does not matter, and a value is everything after the first colon. Quote paths with spaces, and leave off a trailing backslash on a quoted folder.

Switch What it does
/loadSettingsFile:path Loads a saved profile: both servers, authentication and every option.
/database1:name, /database2:name Override the source and destination database names.
/objects:list Compare only these object types (a comma separated whitelist, for example Tables,Indexes,StoredProcedures).
/startCompare Run the comparison at startup.
/saveToFile:path Write the differences as a tab separated file: category, object name, object type.
/scriptTo:path Write the migration script (a file, or an existing folder for one file per object).
/scriptDirection:value sourceToDestination (default) or destinationToSource.
/includeDrops Script the drops of objects only on the target.
/scriptTransaction Wrap the script in SET XACT_ABORT ON and one transaction.
/attestTo:path Write path.html and path.json.
/verifyAttestation:path Recheck an attestation .json; never opens the window.
/emailTo:address Email the list of differences, only when there are some.
/failOnDrift Exit code 2 when any difference is found.
/quiet Console text instead of dialog boxes.
/timeoutSeconds:n Cancel an unattended run after n seconds (default 3600, largest 86400).
/closeAfterSave Close when all the work is done; also turns /quiet on.

Exit codes: 0 for a clean run, 1 for a failure (a file, a database, a profile, the email or the timeout), 2 for drift with /failOnDrift. The mail server for /emailTo is set under File → Email Settings; the settings are stored for the Windows account that saved them, so a scheduled job must run as that account. Help → Command Line Options opens the full reference with worked examples.

Permissions and versions

  • The login needs to be able to read the definitions of the objects compared in both databases. An object the login cannot see is not compared.
  • Users created without a login (contained users) are not reported.
  • No change is made to either database unless you choose a Migrate action or run the generated script yourself.
  • Connections use Microsoft.Data.SqlClient, and its native library must be present beside the program.
Report Why you would go there
Schema Search Find an object by name or text across the databases of an instance.
Structure Change Log See when a schema change was made, before comparing two copies.
What Changed What changed on an instance recently.