Keep Your SQL Servers Synchronized with SQL Compare and SQL Data Compare

Understanding the Synchronization Challenge

Keeping several copies of a database in sync across development, testing, production, and disaster‑recovery sites is a recurring pain point for many DBAs and developers. Even a small discrepancy in table structure or a missing row can break an application, trigger a costly outage, or compromise data integrity. The problem grows the more environments you have, the more developers you have to coordinate, and the greater the volume of data you must move.

Traditionally, synchronization has been a manual, script‑driven process. You write a set of T‑SQL commands to create missing objects, drop unwanted ones, or update data. Then you run those scripts in a specific order, usually via SQL Server Management Studio or SQL Server Enterprise Manager. Each run carries the risk of human error, version drift, or oversight. When you add a new feature or patch, the cycle repeats. Over time, you accumulate a lot of ad‑hoc code, test cases, and an unwritten set of conventions that must be remembered and reproduced by anyone who touches the environment.

Because of these complexities, many teams rely on a “copy‑and‑paste” model: generate scripts from one database, manually edit them, and execute them on another. That model works for very small environments but quickly becomes unsustainable. Teams look for a solution that can detect differences, generate scripts automatically, and apply them reliably. Two tools from Red‑Gate - SQL Compare and SQL Data Compare - are designed for this exact scenario. They cover schema differences (tables, stored procedures, indexes, etc.) and data differences (missing rows, updated values, duplicates). By automating both stages, you dramatically reduce the chance of a mis‑synchronization.

In the following sections we’ll walk through setting up your environment, using SQL Compare to sync database objects, then using SQL Data Compare to sync data. Along the way we’ll share practical tips that make the whole process smoother and easier to maintain.

Preparing Your Environment

Before you can start synchronizing, you need a clean, consistent testing ground. Pick a version of SQL Server that is common across your environments - ideally SQL Server 2019 or later, though the tools support older versions down to SQL Server 2000. If you’re working with a 2000 database, be aware that some features (like JSON columns) aren’t available, but the comparison logic still works.

Clone a production database or create a new copy that matches the production schema but has no data. In our example, we’ll call the source database Pubs (the official Microsoft sample database) and the target Pubs_Copy. You can obtain Pubs by running the script in the Microsoft SQL Server Sample Database folder. Then create a new database named Pubs_Copy and leave it empty.

Install the latest 64‑bit versions of SQL Compare 3.0 and SQL Data Compare on a workstation that has network access to both the source and target servers. Make sure you use the same Windows credentials you use to log into SQL Server Management Studio, or have a SQL account with sufficient rights. Red‑Gate’s tools will prompt for authentication and remember your settings for future runs.

With your environment ready, launch SQL Compare. The first screen you’ll see is the Comparison Settings dialog. Here you specify the source and target databases. Select Pubs as the source and Pubs_Copy as the target. You can use a dropdown to pick the server name, or type it manually. Click Compare to let SQL Compare gather metadata from both databases. The tool reads object definitions, indexes, constraints, triggers, and more.

Once the comparison completes, SQL Compare displays a status window. A green check mark indicates that the comparison finished successfully; if any errors appeared, you’ll see a red cross. Assuming everything is clear, click Results to view the differences. The result screen shows a grid of objects. Each row contains the object type, name, status, and a checkbox to include it in the synchronization script.

Use the top filter to narrow down the objects you want to sync. If you only want to copy tables and stored procedures, tick the appropriate boxes. For a full sync, leave everything selected. The status column uses symbols: + for objects that exist only in the source, for objects that exist only in the target, and ! for objects that differ. Once you’re satisfied with the selection, click Synchronize at the top.

SQL Compare then asks you to confirm the direction of the sync. Choose Pubs as the source and Pubs_Copy as the target. The next screen shows the actual T‑SQL script that will be run. You can review the script, tweak it if necessary, or simply click Execute to run it immediately. The tool will show a progress bar and a log of each step. If everything goes well, the status icons on the result grid will turn to =, indicating that the schemas match.

At this point, the target database contains all objects from the source but still has no data. If you run SELECT FROM Pubs.dbo.Authors and SELECT FROM Pubs_Copy.dbo.Authors, both will return the same number of rows - zero in the target.

Synchronizing Data with SQL Data Compare

With the schema in sync, the next step is to copy the actual data. Launch SQL Data Compare and repeat the same comparison settings: source Pubs, target Pubs_Copy. After you hit Compare, the tool presents a list of tables that exist in both databases. You can deselect tables that you don’t want to sync, but for a complete copy, keep all tables selected.

Below the table list, you’ll see options to choose key columns and to specify the comparison strategy. For most applications, you want the tool to look at primary keys or unique indexes. Check Use key columns for comparison to speed up the process and avoid duplicate rows. The next step is to decide what data differences to report: identical rows, different rows, missing rows, and additional rows. SQL Data Compare can also merge or delete rows, but in this example we’ll focus on synchronizing data from source to target.

After you hit Compare again, SQL Data Compare will scan the tables and produce a report. The top panel lists each table with the number of rows that differ. For each table, you can click a tab at the bottom of the screen to view a detailed list: Identical, Different, Missing, or Additional. If you click the Missing tab for the Authors table, you’ll see a list of rows that exist in Pubs but not in Pubs_Copy

When you’re ready to copy the missing data, click Synchronize at the top. As before, the tool will ask you to confirm the direction and show you the T‑SQL script that will be executed. Review the script; it contains INSERT statements that insert the missing rows, and UPDATE statements for rows that differ. Once you click Execute, SQL Data Compare runs the script and reports success or any errors.