Validating large tables replicated

  • 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

  • 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.

  • Whenever we interfere with replication here we then use Redgate SQL Data Compare to check the data. It may be worth investing in a copy, or I believe it is available for a free trial period.

  • Thanks Braindonor, I'll give that a try.

  • 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.

  • 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

  • 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.

  • 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