SQL Data Compare is a simple SQL tool from Red Gate Software that can complement, and sometimes replace, SQL Server replication.

Replication is the functionality used by SQL Server to move data between databases, generally to synchronize data. This document discusses how to troubleshoot replication problems using SQL Data Compare, and how SQL Data Compare can often replace replication.

Troubleshooting replication issues with SQL Data Compare

SQL Data Compare was designed to address the most common question when troubleshooting replication – what happened when replication took place? SQL Data Compare allows you to graphically compare the contents of two databases, down to individual records, and see exactly where they are the same or, if they are different, how they differ.

Figure 1. A comparison overview between two databases. You can see that, although a replication has been attempted, the data is still different between the two databases. It is possible to drill down and see exactly what data hasn't been moved.

One other functionality of SQL Data Compare is the ability to synch the databases so, if a replication has failed, work can continue while issues are addressed.

Figure 2. SQL Data Compare's options for synchronization

Move data using SQL Data Compare as an alternative to replication

Generally, a great deal of expertise and time is required to set up replication, but doing the same thing with SQL Data Compare is simple. However, there are some tasks which are better accomplished by replication than by SQL Data Compare or the SQL Toolkit. The table below indicates what SQL Data Compare can do easily, and what is better done using replication.

Replication Type	Push	Pull	In	Frequent
Snapshot	No	Yes	N/A	N/A
Merge	No	Yes	Yes	Toolkit*
Transactional	No	No	N/A	N/A

*Red Gate's Comparison and Synchronization Toolkit (the SQL Toolkit) is a SDK that allows you to schedule data movements using SQL Data Compare's APIs.

It is much easier to move data using SQL Data Compare than by using replication, particularly if you have something unusual about your data or databases. To illustrate this, the table below outlines the steps required to move data between two databases using SQL Data Compare, and using replication.

SQL Data Compare	Replication (merge)
Select databases	Ensure administrator rights
Compare the contents*	Select publisher database
Choose data to be synchronized	Configure publisher database (identity, GUID etc.), extra tables etc.
Synchronize the contents	Configure publication (choose data to be replicated)
	Configure publication agent
	Decide push or pull
	Select subscriber(s)
	Configure subscriber database (identity non-replication fields, etc.)
	Configure subscription
	Configure subscription agent
	Configure merge agent (decide merge rules etc.)
	Initiate snapshot
	Troubleshoot
	Run agents (this might be auto-configured at agent set-up time)

*In replication, you don't get the chance to see the differences between the two databases. If there is valuable data in a subscriber database, it is quite likely that this will be overwritten unless you've written very good merge rules.

Clearly, there are many circumstances where replication is the best option, but there are also many when the time and effort could be reduced by using SQL Data Compare.

If you are interested in seeing SQL Data Compare for yourself, try out our free trial.