Hi I have two databases on same server and both databases.These do databases are same (we can say replica) .There is a table name 'Transactions' on both databases.Now I have to create a job which runs on daily basis and check whether the data in Table 'Transaction' in both the databases is same or not (i.e to check whether table in both database is in sync) .How can I do this.Please suggest.

jain_abhishek (6/10/2009)Hi I have two databases on same server and both databases.These do databases are same (we can say replica) .There is a table name 'Transactions' on both databases.Now I have to create a job which runs on daily basis and check whether the data in Table 'Transaction' in both the databases is same or not (i.e to check whether table in both database is in sync) .How can I do this.Please suggest.

you can create a checksum for the values in the table (see checksum http://msdn.microsoft.com/en-us/library/ms189788(SQL.90).aspx and cheksum_agg (http://msdn.microsoft.com/en-us/library/ms188920(SQL.90).aspx) and then compare the checksums. You can do this on a per row level (you may want to do this via a linked server), or the whole table. The fewer checksums you compare the less accurate the result will be (i.e. if you calculate a checksum for the whole table you may encounter cases where the checksums match, but the tables are different. You should also consider calculating the checksums based on some inside knowledge (e.g. do the checksums on only a few columns if that is sufficient to determine a difference). The checksum calculation may be expensive, so do some initial tests (like row counts). If you need to compare all the data without checksums, use either linked servers or there are third party tools too.

After you get the first compare done, you could add an update_dt field and have it default to the current getdate() value. Then, if there are dates since the last comparison, then those are the ones that are different.

There's also merge replication, once you got them in a spot where they are the same.