Checking if 2 tables ( on different servers ) have the same exact data

Question

So it seems that the company who is hosting our SQL Servers has been having some trouble with the replication from the production server to the backup server... I believe some of the tables have been replicating correctly though. Replication is done daily (after hours).

Is there a way I can compare 2 of the same tables, 1 from the backup vs 1 from the production server to see if last nights replication worked?

The only way I could find was to run the following query on both servers and seeing if the result matched which "might" mean that to 2 tables contain the same information.

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
        FROM   (

                    SELECT  * 
                    FROM    table_to_compare
               ) t1

using the above code, It seems that the table did replicate successfully as the checksum values are the same but I'm not sure how reliable this method is.

Does anyone know of a better method to check this out or if this is a good way?

I'm running SQL Server 2008 on a windows server 2008 computer.

Thanks.

Answer 1

I believe you're looking for the tool tablediff which lets you do exactly that - compare two replicated tables for differences. You might find this article useful, to get started.

Here is a GUI for tablediff

Answer 2

I found this article particularly interesting: http://networkprogramming.wordpress.com/2011/01/14/binary_checksum-vs-hashbytes-in-sql/

"If you need speed over accuracy – use CHECKSUM, not BINARY_CHECKSUM.

If you need accuracy over speed – use HASHBYTES."

Answer 3

How large is the data and how fast are the links between the databases and each other (and you)? There are a number of ideas:

If the data is small enough that this is practical, run SELECT * FROM <table> ORDER BY <pk> on each DB, save the results to a tab or comma delimited file (not space aligned as that blows up the resulting file size massively) and compare the resulting output with your preferred diff type utility such as winmerge. That way you are comparing absolutely all the data.

If the databases can see each other (most likely as they are able to operate as replication partners) and the link between them is high enough bandwidth and low enough latency, you could use the linked server functionality (see http://msdn.microsoft.com/en-us/library/ms190479.aspx and related documentation) to and compare compare the contents of the tables in a couple of SQL statements (listing the the rows in <table> that do not have identical rows in <linked_server>.<db>..<table> and vice versa) just as you would compare the contents of two local tables. This is a relatively slow option potentially, but could be quite a powerful check to automate.

If you are needing to use checksums because you want to greatly reduce the amount of data needing to be transferred, use HASHBYTES rather than the CHECKSUM family of functions as you can use better quality hashes so you are more assured by them coming out equal. This is more CPU intensive, but for large amounts of data you will be I/O bound not CPU bound anyway so will have many cycles going spare (and for small amounts is simply won't matter).

As a middle-ground between comparing all the data character-for-character and comparing a single checksum covering all of the data, you could export SELECT <pk>, HASHBYTES('SHA1', <all-other-fields-concatenated>) ORDER BY <pk> from each database and compare those results to see if they are identical (or SELECT HASHBYTES('SHA1', <all-other-fields-concatenated>) ORDER BY <pk> to reduce the amount of data flowing, but having the PK in the output will mean you can identify the rows that differ, if any do, with less further queries). Of course this last option is pointless if the data in the average row is smaller than the resulting hash, in which case the "compare everything" option will be more efficient.

Answer 4

While tablediff compares only tables in live databases, there are a lot of 3rd party tools that can compare a database backup to a live database.

You can also execute the following script to see the data that exists in one table and doesnt exist in another, but you can do that only against 2 live databases:

SELECT * FROM MyTest.dbo.testtable WHERE NOT EXISTS (SELECT * FROM MyTest2.dbo.TestTable WHERE MyTest2.dbo.testtable.f1= MyTest.dbo.testtable.f1)