September 16, 2013 at 11:58 am
Hello,
I am working with large tables (many millions of records) in SQL Server that are replicated from one server to another and I need to find a way to quickly compare the source table with the target. The key to this issue is that the tables do not have a primary key, otherwise, I would look for the max primary key value in both tables (source vs. target) and check the delta (difference). Applying a count of the records is not efficient. Also, checking the row count for the tables according to sys.sysindexes is not possible due to the inconsistency in which statistics are run on both servers.
Is there an approach using the trasactional replication where a quick comparison can be run between the source and target tables that will provide a delta? I have already read the following article regarding replication monitoring, but it was at a higher level than I'm seeking.
http://www.sqlservercentral.com/articles/Stairway+Series/72451/
Thanks,
Grant
September 17, 2013 at 2:10 am
Hi,
are you sure, that thare are no primary keys in your tables. It is not possible to replicate a table without a primary key.
September 17, 2013 at 12:25 pm
Thanks Braindonor, I'll give that a try.
September 19, 2013 at 11:22 am
you can't setup transactional replication for tables without primary keys. How did it work?
Coming to the comparision, you just want to check the row count or the data also? If row count you can use system tables to grab the info. If you would like to check the data you can use tablediff utility which is provided by microsoft.
December 19, 2013 at 8:03 am
I use a date column that changes regularly and compare that across the replication partners;
SELECT
MAX(LASTHANDLINGDATE) AS Source_LASTHANDLINGDATE
FROM [Source]
GO
SELECT
MAX(LASTHANDLINGDATE) AS Dest1_LASTHANDLINGDATE
FROM Dest1
GO
SELECT
MAX(LASTHANDLINGDATE) AS Dest2_LASTHANDLINGDATE
FROM Dest2
GO
December 19, 2013 at 8:19 am
muthyala_51 (9/19/2013)
you can't setup transactional replication for tables without primary keys. How did it work?
You cant publish articles without a primary key. You could have a subscriber without one if you've made changes or manually initialised the schema.
December 19, 2013 at 8:24 am
grantbanjo (9/16/2013)
I have already read the following article regarding replication monitoring, but it was at a higher level than I'm seeking.
If you want to perform simple comparisons I'd look at row counts and primary key comparisons.
For a more detailed comparison I'd use something like the MS tablediff tool or Red Gates SQL Compare. Due to the number of rows you may be better using the command line options.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply