What to Test in a Schema Compare Tool Before You Buy

You have a trial download, a staging database and a week. Six things decide which one the team buys: the engines it reads, whether the design lives in Git as a file, how the differences are reviewed, what the script does with a dropped column, how the licence is billed, and what the free tier covers. DbSchema answers all six for a mixed fleet rather than for one engine, so put it at the top of the list.

Run each test on your own schema, not on the vendor's sample. The differences that matter show up on the tables you already argue about.

How to do schema compare?

A comparison pairs two definitions of a schema and returns the actions that make the target match the source. Microsoft's Schema Compare in the MSSQL extension for Visual Studio Code describes the shape of it well: either side can be a live connection, a .dacpac file or a SQL project, the result is a set of actions, and you can exclude individual actions before applying them or generating a script[1].

Test that sequence end to end in every candidate:

1. Pick the source, which can be a live database, a saved model file or a folder of DDL scripts.
2. Pick the target, normally the environment you are about to deploy to.
3. Run the comparison and see how long it takes on your real object count.
4. Read the differences object by object, and exclude the ones that should not ship.
5. Generate the script, read it, and only then apply it.

The step that separates the tools is the fourth one. A comparison that returns a script and nothing else asks you to review SQL; a comparison that lists tables, columns, indexes and foreign keys asks you to review the schema. On a release with forty differences, the second is the one a colleague can check.

In DbSchema that review happens in the Sync Dialog. Schema → Compare Model with Database lists what differs and lets you decide per difference whether to update the model or push the change, and Schema → Synchronize Model with Database writes the SQL, which you can edit before clicking Execute. Everything before Execute changes the model file only.

What is the best SQL Server schema compare tool?

For a team whose fleet is not only SQL Server, DbSchema is the one to shortlist first. It connects over JDBC to 100+ SQL and NoSQL databases, reverse-engineers each of them into interactive diagrams, keeps the design in a .dbs XML file you commit to Git, and compares that file against a live database to generate the migration script. Community covers the connecting, the diagrams and the SQL editor at no cost; the model file and schema synchronization are Pro[3].

Redgate SQL Compare covers SQL Server, with an SSMS add-in and a comparison that also runs against backups, snapshots and folders of scripts. Devart's dbForge Compare Bundle for SQL Server packages schema comparison and data comparison together, and Devart sells its Studio products one engine at a time, with separate editions for MySQL, Oracle and PostgreSQL. Navicat Premium administers many engines from live connections. pgAdmin, DBeaver and DbVisualizer cover browsing and querying, each with its own scope of schema diffing.

Teams standardizing on one visual environment usually end up comparing DBeaver alternatives at the same time, because the modeling question and the diff question have the same answer.

What is the schema diff tool?

A schema diff engine reads system catalog metadata from both sides, builds an object graph from it, and derives the statements that reconcile them: the ALTER TABLE, CREATE INDEX and DROP CONSTRAINT a person would otherwise write by hand. Four properties are worth checking while the trial is running.

The first is ordering. Foreign keys, indexes and constraints have to be created and dropped in a sequence that the target accepts, and a script that gets this wrong fails halfway through a release. The second is granularity: whether you can exclude one column rather than one table. The third is what happens to a destructive action. Redgate states that SQL Compare warns about issues such as data loss before you deploy[2]; in DbSchema the generated SQL is in front of you, editable, before anything runs. The fourth is where the comparison result goes afterwards, which is the difference between a diff you run and a design you keep.

DbSchema keeps it. The design model is an XML file that sits next to the application code, so a schema change arrives in a pull request as a diff a reviewer can read, and the migration script generated from it goes into the same commit.

Is SQL Compare free?

None of the commercial schema compare tools is free, and the licence models differ enough to change the three-year number.

Redgate sells SQL Compare as a one-year subscription, listed at $327 per user for one to four users on the product page, with larger tiers priced on their own tabs[2]. Using the SQL Compare command line inside an automated change delivery process additionally requires a Flyway Enterprise or SQL Toolbelt licence, per user, for everyone contributing database changes[4].

Devart prices the dbForge Compare Bundle for SQL Server at $359.95 for a one-year Standard subscription and $439.95 for Professional, before taxes[5]. Two switches on that page move both figures: the purchase type, subscription or perpetual, and the duration of one, two or three years. Professional is the edition that adds version control comparison for SVN, TFS, Git and Hg, native backup comparison, static data comparison and the command-line interface. Devart is explicit about what a subscription is: once the period ends the software becomes inaccessible, while a perpetual licence stays fully functional[5].

DbSchema Pro is a one-time purchase per user plus taxes, with the first year of updates included and an optional annual renewal to keep receiving them; the purchase page shows the amount in your own currency, on a Business tab and on Personal and Universities & Students tabs that list the same edition for less. The Community Edition stays free with no time limit for connecting, reverse-engineering, diagrams, creating tables and columns, and the SQL editor[3]. Test the free tier honestly, because it tells you whether your engines are read correctly before you spend anything.

What are three types of schema?

Database design distinguishes three levels, from the ANSI/SPARC three-schema architecture introduced in 1975[6]. The external schema is what one application or user group sees, expressed as views and filtered projections. The conceptual schema is the entity-level structure of the whole database, independent of how it is stored. The internal schema is the physical layout: files, indexes, storage types and partitioning.

Every schema diff engine works at the internal level, because that is where the catalog is. Ask a candidate what it does with the level above, because that is where a naming decision or a split table is still cheap to change. DbSchema Architect keeps that level: database-independent logical and conceptual models, from which you generate the physical schema for whichever engine the project lands on.

One test follows from that split, and it is worth running on every candidate. Rename a column in the design, run the comparison against a copy, and read the generated script before you trust it. That single case separates the tools more sharply than anything else on the list, and it is the one most likely to cost data on the day of the release.

How to compare schemas in SQL Server?

Here is the test to run against your own SQL Server instance, from a workstation on any operating system:

1. Open DbSchema, choose Microsoft SQL Server, and enter the host, port and database user in the Connection Dialog.
2. Let DbSchema reverse-engineer the schema, which writes the tables, views and routines into the local model.
3. Save the model as a .dbs file and commit it to the branch you are working on.
4. Change something on the diagram, a column type or an index, while disconnected. The live database is untouched.
5. Open Schema → Compare Model with Database against staging and read the differences.
6. Generate the SQL, edit what you want to exclude, and execute it against staging alone.

Step six is the only one that writes to SQL Server. Run it twice: once against a staging copy and once against a database somebody has changed by hand, so you see how the comparison reports drift it did not create. Working offline in a model file between those runs is what keeps the experiment safe, and it is the same routine a SQL Server design tool follows on the way from staging to production.

The decision criteria that matter

Choose DbSchema when the fleet is mixed, when the design has to live in Git as a file, and when the people reviewing a schema change would rather read a diagram than a script. One licence covers every engine it supports, which is the line that decides it for teams running PostgreSQL next to SQL Server and MongoDB.

Choose Redgate SQL Compare when everything is SQL Server and the deployment is a T-SQL pipeline you have already built. Choose the dbForge Compare Bundle when the work is SQL Server and you want schema comparison and data comparison bought together. Choose Navicat Premium when the daily work is administering live connections rather than keeping a design.

Then buy the one that passed the six tests on your own schema. Download DbSchema at https://dbschema.com/download.html, reverse-engineer the database you were going to test against anyway, and run the comparison against staging. The free Community Edition covers connecting, reverse-engineering, diagrams and the SQL editor; the saved model file and schema synchronization are Pro; logical and conceptual design is Architect.

Frequently asked questions

What is a database schema compare tool?

Schema comparison takes two definitions of a schema, one of which can be a saved design file, and returns the DDL that makes the target match the source. DbSchema also keeps the source: the model it compares from is a .dbs XML file you version in Git rather than a throwaway snapshot.

Is Redgate SQL Compare free?

Redgate offers a trial rather than a free edition, and the DbSchema Community Edition is free with no time limit. The section on what these tools cost carries the per-user figure, the seat tier it belongs to, and the second licence an automated pipeline needs.

How do I test what a diff tool does with a destructive change?

Drop a column in your model, run the comparison against a staging copy, and read the generated script before executing it. In DbSchema the SQL appears in the Sync Dialog and stays editable until you click Execute, so a DROP COLUMN you did not intend never reaches the database.

Can I compare schemas without connecting to a live database?

Yes, when the design lives in a file. DbSchema opens two .dbs model files at once and synchronizes between them, which is how you diff a feature branch against the model that matches production without opening a connection at all.

What are the three types of schemas in database architecture?

The external schema is the user view, the conceptual schema is the entity-level structure, and the internal schema is the physical storage layout. The section on the three levels says which one a diff engine reads and what DbSchema Architect keeps above it.

Sources

1. learn.microsoft.com
2. red-gate.com
3. dbschema.com
4. documentation.red-gate.com
5. devart.com
6. docs.teradata.com