Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Validating large tables replicated


Validating large tables replicated

Author
Message
grantbanjo
grantbanjo
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 170
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
andreas.kreuzberg
andreas.kreuzberg
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 597
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.
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 11203
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.

Steve Hall
Linkedin
Blog Site
grantbanjo
grantbanjo
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 170
Thanks Braindonor, I'll give that a try.
muth_51
muth_51
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 2862
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.
Ralph Thomas
Ralph Thomas
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 508
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
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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.
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search