Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Validating large tables replicated Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 11:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 11, 2014 5:09 PM
Points: 27, Visits: 145
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
Post #1495219
Posted Tuesday, September 17, 2013 2:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:19 AM
Points: 49, Visits: 333
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.
Post #1495395
Posted Tuesday, September 17, 2013 4:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 1,541, Visits: 8,191
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.

BrainDonor
Linkedin
Blog Site
Post #1495432
Posted Tuesday, September 17, 2013 12:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 11, 2014 5:09 PM
Points: 27, Visits: 145
Thanks Braindonor, I'll give that a try.
Post #1495627
Posted Thursday, September 19, 2013 11:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:24 AM
Points: 388, Visits: 1,906
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.
Post #1496509
Posted Thursday, December 19, 2013 8:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 8:14 PM
Points: 16, Visits: 432
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
Post #1524598
Posted Thursday, December 19, 2013 8:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.
Post #1524616
Posted Thursday, December 19, 2013 8:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.
Post #1524620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse