Schema Versioning for SQL Server

Production has a column that staging does not, and the only record of how it got there is an ALTER TABLE somebody ran during an incident. Versioning the structure itself closes that gap. The schema lives in one file that declares every table, column, key and index, and you commit that schema file next to the application code. The T-SQL that changes an instance is then generated from the difference between the file and the live database.

The three types of version control

Version control comes in three architectures, and what separates them is where the history lives. A local system keeps the revisions in one working copy on a single machine. A centralized system keeps the authoritative history on one shared server, and a client has to reach that server to record anything. A distributed system gives every clone the full history, so commits, branches and diffs happen locally and travel upstream when you push.

Schema versioning rides on the third row. The artifact you version is a file, it belongs in the repository that already holds the application code querying those tables, and you want to commit a change validated against a local instance before it goes anywhere shared.

State-based and migration-based version control

Two methods are in use, and they differ in what sits in the repository. State-based version control keeps one artifact describing the structure you want, and a comparison against a live database produces the statements that close the gap. Migration-based version control keeps an ordered set of T-SQL scripts, one per change, each applied once and recorded in a history table. The two are set side by side, artifact by artifact, in schema versioning for PostgreSQL.

In state-based version control, the schema file is the artifact in Git, and the deployment script is generated from the difference between that file and a database instead of typed by hand. For a SQL Server team the useful combination is both halves: the schema file in the repository, so a reviewer reads the change as structure, and the generated script attached to the release, so the same reviewer reads the exact T-SQL that will run. That is the case for a visual schema diff over a script folder alone.

Git, and where the schema file fits

Git is the distributed system your application repository is already in, which is the whole reason for putting the schema in a file. Storing the schema file in a Git repository gives the team a versioned history of every schema change, with the ability to branch, merge and roll back as with source code.

Once the file is in the repository, the things you do with application code apply to the schema. A feature branch isolates a table redesign until it is ready. A pull request puts the structural diff next to the code that depends on it, in front of the same reviewer. Git blame answers who added a column and when, and two people editing different tables merge as text, with no lock on any instance.

Rules for versioning a SQL Server schema

Version the structure and the reference rows that the structure means nothing without, such as lookup tables of country codes or statuses. Leave transactional rows out. They make the repository large, they go stale between commits, and they put customer data somewhere it does not belong.

Route every schema change through a pull request, and treat a direct ALTER TABLE against a shared instance as the exception you have to explain. The rule is enforceable rather than decorative once the schema file is the source: an out-of-band change shows up as a difference the next time anyone compares.

Make the change in the schema file rather than on an instance, and let the comparison write the T-SQL. This is where the state-based approach earns its keep for a SQL Server design: the statements come from a comparison, so nothing is forgotten because it was in a different script.

Run the generated script against a staging copy before production, and read it first. Turning a schema diff into a safe migration script covers what to change in a generated script before it runs, and the section below shows the two statements on SQL Server that most often need that second look.

A 1.0 to 1.1 upgrade, in the schema file and in T-SQL

In DbSchema, the schema file is the model file: DbSchema saves the entire database design, tables, columns, foreign keys and diagrams, into a single .dbs file in XML format, compares that file against a live SQL Server database, and generates the T-SQL that turns one version into the other. Keep each database in its own model file; several model files can share one repository.

DbSchema has Git built in, as its Git documentation describes: choose File → Git Collaboration to open the Git dialog, tick the model file under Uncommitted changes, type a Commit message, press Commit, then press Push.

While DbSchema is connected, every change that you confirm in the diagram runs in the database at once, so design the release offline: click the connection button and choose Offline (connect later). Editing the diagram then changes only the model, saving writes the .dbs file, and Commit and Push move that file through Git. The instance you are deploying to is untouched until you run a synchronization against it.

A release adds two columns and a unique index. You make all three changes in the DbSchema diagram while offline, so they reach the design model and nothing else, and the commit is a readable diff of the XML:

 <schema name="dbo" catalogname="CustomerManagement" >
     <table name="Customers" >
         <column name="customer_id" type="int" mandatory="y" />
         <column name="email" type="nvarchar" length="255" mandatory="y" />
         <column name="created_at" type="datetime2" mandatory="y" />
+        <column name="is_mfa_enabled" type="bit" mandatory="y" >
+            <defo><![CDATA[0]]></defo>
+        </column>
+        <column name="updated_at" type="datetime2" />
         <index name="pk_customers" unique="PRIMARY_KEY" >
             <column name="customer_id" />
         </index>
+        <index name="idx_customers_email" unique="UNIQUE_KEY" >
+            <column name="email" />
+        </index>
     </table>
 </schema>

The version in Git is now ahead of the database. In DbSchema, click the connection button and choose your SQL Server connection. When DbSchema asks how to bring the model and the database together, choose Compare, then decide difference by difference, and press Compare in the Synchronize with the Database dialog. The Synchronize Model and Database dialog then lists the differences object by object. Set the three rows to the database. Commit in the database runs the migration statements one at a time in the Execute Script In Database Dialog, and Generate SQL script writes them without running them, into an SQL Editor or a file:

ALTER TABLE dbo.Customers ADD is_mfa_enabled BIT NOT NULL DEFAULT 0;
ALTER TABLE dbo.Customers ADD updated_at DATETIME2 NULL;
CREATE UNIQUE INDEX idx_customers_email ON dbo.Customers (email);

Two of the three are worth editing on a table with a hundred million rows, so generate the script into an SQL Editor and edit it there before it runs. Running it is the moment the SQL Server database changes.

The first one adds a mandatory column with a default. In SQL Server 2012 (11.x) Enterprise edition and later versions, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant, and the value is then stored only in the metadata of the table rather than written into the existing rows[1]. A runtime constant is an expression that produces the same value at runtime for each row of the table, so a literal default such as 'pending' qualifies[1]. A default of NEWID() does not qualify, because NEWID() produces a unique value for each row[1]. Adding a NOT NULL column whose default is not a runtime constant always runs offline, with an exclusive Sch-M lock held for the duration of the operation[1].

The third statement builds an index, and the generated form takes the table with it. With ONLINE = ON, long-term table locks are not held for the duration of the index operation, and during the main phase only an intent shared lock is held on the source table, which lets queries and updates on it proceed[2]. With ONLINE = OFF, table locks are applied for the duration of the index operation[2]. Online index operations are not available in every edition of SQL Server[2], so add the option in the SQL Editor where the target instance supports it:

CREATE UNIQUE INDEX idx_customers_email ON dbo.Customers (email) WITH (ONLINE = ON);

Edit it there and press Run Script, then commit the model file so the repository and the instance tell the same story.

Which objects changed on the instance, and when

The model in Git says what the structure should be. To find out whether an instance still matches it, start with the catalog. sys.objects contains a row for each user-defined, schema-scoped object created within a database, and its modify_date column holds the date the object was last modified by using an ALTER statement; for a table or a view, modify_date also changes when an index on it is created or altered[3].

SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS object_name, type_desc
FROM sys.objects
WHERE modify_date > DATEADD(day, -1, GETDATE())
ORDER BY modify_date;

Run that after the migration above and one row comes back, because both statements landed on the same table:

A date tells you that something moved, not what moved. For that, connect DbSchema to the instance and choose Database → Refresh Model from Database, then Review Differences: the Synchronize Model and Database dialog lists the added, removed and modified tables, columns, indexes and foreign keys, and for each difference you choose to write it into the model, write it into the database, or Ignore it.

Writing the difference into the model and committing the file is how an ALTER TABLE that skipped review stops being invisible, and the same comparison against staging and production is what keeps the two environments agreeing on the same version.

Download DbSchema at https://dbschema.com/download.html, connect to the SQL Server database you deploy to, and reverse-engineer it into a model you commit as the baseline before the next change. Connecting, reverse-engineering and the interactive diagrams are the free Community Edition. Saving the design to a .dbs file and schema synchronization, the two steps that make the schema versionable, are Pro, and the same download runs Pro as a trial.

Frequently asked questions

What exactly does the .dbs model file contain?

The design model holds the schema structure, the diagrams, virtual foreign keys and comments, in human-readable XML that any text editor opens. Table data is not in it, so committing the file puts no customer rows in the repository.

I designed the change while disconnected. What happens when I reconnect?

DbSchema keeps offline edits in the .dbs file and sends no statements to the database while you are disconnected. After reconnecting, choose Database → Refresh Model from Database to detect the differences, then review each one in the Synchronize Model and Database dialog and either apply it to the database, take it into the model, or generate a migration script from it.

Can the comparison run without opening DbSchema?

Schema synchronization can be scripted and run in headless mode with Groovy automation scripts or with DbSchemaCLI, which is how the comparison goes into a build pipeline rather than a person's afternoon.

Which DbSchema edition covers this workflow, and what does it cost?

Pro covers it, as a one-time perpetual license with the first year of updates included, and keeping the updates afterwards is a yearly renewal. The pricing page lists the current figures for both.

Sources

1. learn.microsoft.com
2. learn.microsoft.com
3. learn.microsoft.com