SSMS 22.7 Schema Compare — Before You Click Apply

For twenty years, comparing two database schemas meant leaving SSMS. You opened Visual Studio and SSDT, or you paid for a third-party tool, or you did what most of us actually did -- scripted both sides out and eyeballed the diff. Schema compare has been one of the most requested features in SSMS for as long as I can remember.

SSMS 22.7 finally includes this feature. Graphical schema compare, native, no add-ins, in preview. You pick a source and a target, it shows you every difference, and it will write the script to make the target match -- or even apply the changes directly, if you let it.

That last part is where I want to spend some time because the compare is the easy half. The Apply button is the piece that can hurt you.

What It Is and What You Need

Schema compare works against any two of a live database, a SQL database project, or a .dacpac file, in either direction. A .dacpac is a data-tier application package -- a single file containing a database's full schema definition, no data, the same format SSDT has used for deployments for years. A SQL database project is the source-control side of the same thing: a folder of CREATE scripts, one per object, that builds into a .dacpac. If neither of those is part of your world, no worries! The database-to-database comparison is the bread and butter here, and it's likely all most of us will use.

Required version is SSMS 22.7.0 or later. The feature ships with the base install, so comparing live databases or .dacpac files needs nothing extra. Only SQL database projects require the Database DevOps workload, which is one of SSMS 22's optional components. To install it, launch the Visual Studio Installer, choose Modify on your SSMS 22 installation, check the Database DevOps workload, and let it update. It is still a preview feature, so expect some rough edges and remember that it's likely to change before GA.

There are three ways to open it, and where you start determines what gets filled in for you:

Object Explorer

Right-click a database, Tasks > Schema Compare (Preview). That database becomes your source.

Solution Explorer

Right-click a SQL database project, Schema Compare (Preview). This is the one that requires the Database DevOps workload.

Tools menu

Tools > Schema Compare. Opens empty, and you pick both source and target yourself.

However you get there, the workflow is the same. Run the comparison and the differences come back in a grid, grouped by action -- adds, changes, and deletes. Selecting an object shows its side-by-side difference in the lower pane, and from there you decide what happens next. You can check or uncheck individual changes, then either Generate Script, which opens the change script in a new query window for your review, or Apply, which runs the sync against the target right then and there. And, if it's a comparison you'll run regularly, like a weekly sync between production and dev, you can save the whole configuration, source, target, options and all, as an .scmp file. Then you reopen and reuse it next week instead of having to set everything up all over again.

If you've spent any time with schema compare in Visual Studio database projects, all of this will feel familiar. It's the same workflow, finally living where DBAs actually work.

The Sandbox

To test it, I need two databases that disagree. The setup below builds a 'dev' and a 'prod' copy of the same schema, then plants five specific differences between them. Each one is a kind of drift I've met in the wild, and each one should land in the compare results differently.

This is schema only, no data load. Schema compare doesn't read your rows, so this post doesn't need any.

USE master;
CREATE DATABASE CompareDev;
GO
CREATE DATABASE CompareProd;
GO

-- the matching objects in both databases
USE CompareDev;
GO
CREATE TABLE dbo.tblCustomers
(
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Region VARCHAR(20) NOT NULL
);
CREATE TABLE dbo.tblOrders
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME NOT NULL,
    OrderTotal DECIMAL(10,2) NOT NULL
);
GO
CREATE PROCEDURE dbo.usp_GetOrdersByRegion
    @Region VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.OrderID, o.OrderDate, o.OrderTotal
    FROM dbo.tblOrders o JOIN dbo.tblCustomers c 
      ON c.CustomerID = o.CustomerID
    WHERE c.Region = @Region;
END;
GO

USE CompareProd;
GO
CREATE TABLE dbo.tblCustomers
(
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Region VARCHAR(20) NOT NULL
);
CREATE TABLE dbo.tblOrders
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME NOT NULL,
    OrderTotal DECIMAL(10,2) NOT NULL
);
GO
CREATE PROCEDURE dbo.usp_GetOrdersByRegion
    @Region VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.OrderID, o.OrderDate, o.OrderTotal
    FROM dbo.tblOrders o JOIN dbo.tblCustomers c 
      ON c.CustomerID = o.CustomerID
    WHERE c.Region = @Region;
END;
GO


-- now the drift. five intentional differences
-- 1. new table in dev only (an add)
USE CompareDev;
GO
CREATE TABLE dbo.tblLoyaltyTier
(
    TierID INT IDENTITY(1,1) PRIMARY KEY,
    TierName VARCHAR(30) NOT NULL
);
GO

-- 2. new column in dev only (a change)
ALTER TABLE dbo.tblCustomers ADD Email VARCHAR(255) NULL;
GO

-- 3. proc modified in dev only (a change)
ALTER PROCEDURE dbo.usp_GetOrdersByRegion
    @Region VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.OrderID, o.OrderDate, o.OrderTotal, c.CustomerName
    FROM dbo.tblOrders o JOIN dbo.tblCustomers c 
      ON c.CustomerID = o.CustomerID
    WHERE c.Region = @Region
    ORDER BY o.OrderDate DESC;
END;
GO

-- 4. old table still in prod only (a delete, if you let it)
USE CompareProd;
GO
CREATE TABLE dbo.tblLegacyImport
(
    ImportID INT IDENTITY(1,1) PRIMARY KEY,
    RawData VARCHAR(500) NULL
);
GO

-- 5. the emergency index. added in prod during a fire,
--    never backported to dev. we all have one of these.
CREATE NONCLUSTERED INDEX idx_tblOrders_OrderDate
    ON dbo.tblOrders (OrderDate)
    INCLUDE (OrderTotal);
GO

Five differences, and they are not all the same kind of dangerous:

#	Drift	Compare Should Say	Risk on Sync
1	tblLoyaltyTier in dev only	Add	None. New table.
2	Email column in dev only	Change	Low. Nullable add.
3	Proc changed in dev	Change	Low. ALTER PROC.
4	tblLegacyImport in prod only	Delete	High. DROP TABLE.
5	Index in prod only	Delete	High. Drops a prod index.

Numbers 4 and 5 are the real test here. Syncing dev to prod means making prod look exactly like dev -- and anything that exists only in production becomes targeted for removal. In this sandbox that's a leftover table and an index. Number 5 is one we'll all recognize. The index that went into production during an emergency, but never made it back to dev or into the next formal deployment. It's an old story, and a schema compare tool could help you avoid that the next time around.

Running the Compare

Getting started is quick. In Object Explorer, right-click CompareDev and select Tasks > Schema Compare (Preview). The Schema Compare window opens with CompareDev already set as the source, which leaves the target. Click the ellipsis next to the Target box, choose a database as the target type, connect to your instance, and pick CompareProd. With both sides set, click Compare and the tool goes to work.

Notice the direction I chose. Dev as source and prod as target is the standard deployment direction, but also a dangerous one. Everything in dev that prod doesn't have becomes an add, and everything in prod that dev doesn't have becomes a delete.

The Script It Writes

The next part is what matters most to me. I clicked Generate Script with everything included and nothing excluded. You know, the scenario that happens when a hurried DBA runs the compare.

The generated script runs about a hundred lines, and most of it is standard deployment scaffolding full of SET options with PRINT statements between steps. Two very interesting findings: First, it's a SQLCMD script, full of :setvar and :on error exit directives, so it will not run in a normal query window. You will need to enable SQLCMD Mode (Query menu > SQLCMD Mode) or it errors out immediately -- the script even checks for this itself. And secondly, there is no transaction wrapping. The :on error exit directive stops the script when a step fails, but anything already applied stays applied. Keep that in mind before running it against anything that matters.

Now the part I built this sandbox to see. Here's what it generated for the prod-only table:

Credit where due. Before dropping tblLegacyImport, the script checks whether the table contains data, and if it does, the deployment halts rather than destroy rows. My sandbox table is empty, so the drop proceeds, but the point is that a populated table stops the whole script. That's a real guardrail.

And here is what it generated for the prod-only index:

No check, no warning, no halt. A PRINT statement and a DROP. The table gets a data-loss guard because dropping data is understood to be dangerous, but the emergency index -- the one that went into production purposely -- is removed without a second thought. The script announces it politely on its way out the door. If you weren't reading closely, number 5 just happened to you.

Apply vs Generate Script

There are two buttons at the top of the Schema Compare window that I need to emphasize. Generate Script opens the changes in a query window and hands you the wheel. So you get to read it and run it, on your terms. Apply skips all of that and runs the sync against the target directly. I wondered, do you get any chance to verify? And how much time between clicking Apply and the changes landing?

Exactly the confirmation I hoped to see. After clicking Yes, the synchronization begins immediately and finishes with one of these:

I even ran it once more to be sure the system detects the changes, and it found 0 differences.

My Verdict

I have to say that I like it. Even though my test was minimal, it did exactly what I asked it to, and even had guardrails for me, in case I wasn't fully paying attention to my ask. Schema Compare is a feature I will use. Generate Script will probably remain my default button. Apply has earned my respect, but not my trust. Yet.

One last note: Microsoft is collecting feedback on this feature right now, before it goes GA. This means use it. Beat it up real good and if the options don't protect the things you think need protected, this is the time to say so. Complain early, complain often. Help them make this into something we can comfortably rely upon.

Cleanup, because we always clean up:

USE master;
DROP DATABASE CompareDev;
DROP DATABASE CompareProd;

More to Read

Microsoft Learn: Schema Compare (Preview) in SQL Server Management Studio
Microsoft: Announcing the Release of SSMS 22.7.0 -- and many previews
sqlfingers: SSMS 22: What's Different, What's Worth It, and What May Bite You