SSDT: The Moment Database Deployments Became Repeatable

Shannon Lowder

Shannon Lowder

— 3 min read

For most of my career before 2012, deploying a database schema change meant running a SQL script. Maybe you wrote it yourself. Maybe someone emailed it to you. Maybe you'd been collecting changes in a text file since the last release and you were about to run all of them in sequence, hoping they were in the right order, hoping nothing depended on something you'd forgotten to include. If it worked: relief. If it failed: "I'll fix it manually and we'll document it later." (There was never a later.)

SQL Server Data Tools changed this. Not immediately and not perfectly, but it gave the database its first serious version control and repeatable deployment story. If you're still deploying schemas with hand-rolled SQL scripts in 2012, this is worth understanding.

What SSDT Actually Is

SSDT is a Visual Studio project type for SQL Server databases. A database project contains one .sql file per object: one for each table, each view, each stored procedure, each function. The project compiles to a dacpac — a zip file containing the schema model. That dacpac is your deployment artifact.

The deployment model is declarative and state-based, not imperative and migration-based. You don't write "add column X to table Y." You write the final desired state of table Y, including column X. When you deploy, SSDT compares the dacpac's desired state to the actual state of the target database and generates the migration script needed to bring the target into alignment. You describe what you want; SSDT figures out what needs to change.

The Comparison Model

This deserves emphasis because it's the core concept. Given:

-- SSDT project: what you want table Customers to look like
CREATE TABLE dbo.Customers (
    CustomerId    INT           NOT NULL IDENTITY PRIMARY KEY,
    CustomerName  NVARCHAR(200) NOT NULL,
    Email         NVARCHAR(256) NOT NULL,
    CreatedAt     DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);

And a target database where the table currently looks like:

-- Target database: current state
CREATE TABLE dbo.Customers (
    CustomerId    INT           NOT NULL IDENTITY PRIMARY KEY,
    CustomerName  NVARCHAR(200) NOT NULL,
    Email         NVARCHAR(100) NOT NULL   -- different size
    -- no CreatedAt column
);

SSDT generates:

-- Generated migration script
ALTER TABLE dbo.Customers
    ALTER COLUMN Email NVARCHAR(256) NOT NULL;

ALTER TABLE dbo.Customers
    ADD CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME();

You didn't write that script. SSDT generated it by comparing states. If you'd written it by hand, you might have gotten the size wrong, forgotten the NOT NULL, or missed the DEFAULT. SSDT missed none of those because it wasn't comparing from memory — it was comparing from the model.

Source Control for the Database

A SSDT project is a collection of text files. Text files go in source control. This sounds obvious but it was genuinely new for most DBA workflows in 2012. Once the schema was in source control:

  • Every schema change had a commit, a timestamp, and a commit message
  • Branching and merging for database changes worked the same way as application code
  • Pull requests could include database changes alongside the application code that depended on them
  • Code review applied to schema changes, not just application logic

The discipline that developers had built around source control for application code was now available for the database. The database was no longer the one artifact in the system that lived outside version control.

What SSDT Didn't Do (In 2012)

SSDT's state-based model was powerful but had limits. Data migrations — "rename this column but preserve the data in it," "backfill this new column from computed values" — couldn't be expressed as pure state changes. SSDT could detect that a column disappeared and a new one appeared, but it couldn't know they were the same column with a different name. For complex data migrations, you still wrote explicit scripts.

The pattern I settled on: SSDT for structural schema changes (tables, views, indexes, procedures), explicit migration scripts for data transformations, and a deployment pipeline that applied them in the right order. Not perfect, but vastly better than the alternative.

If you're running a SQL Server database in 2012 without SSDT, today is a good day to start the migration. The first time you deploy a complex schema change without writing a single migration script and it just works, you'll understand why it matters. As always, I'm here to help.

Read more