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

Replication data inconsistencies Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2014 1:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:52 PM
Points: 172, Visits: 714
Hi all,

I guess our company's db is messed up with some issues on replication, and we have some data inconsistencies. Now, basically I need to compare data between 2 live tables...one that resides in production and the other in test server. I know there is tablediff that can give me that stuff. However, if someone knows how to compare 2 tables from different servers using customized t-sql code that would be really helpful.

I would really appreciate any help on this.

Regards,
Faisal
Post #1579801
Posted Thursday, June 12, 2014 12:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:52 PM
Points: 172, Visits: 714
Hi,

Can someone update me on this? I bet someone might have done this in the past. Pls. I need someone's help on this asap.
Post #1580216
Posted Thursday, June 12, 2014 1:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:24 PM
Points: 2,829, Visits: 8,477
Do you have a linked server ?
Are you looking for missing/extra records ? Or need to check every column within records of the same key.

In other words, you need to better define the problem in order to get a useful answer.



Post #1580232
Posted Friday, June 13, 2014 4:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:52 PM
Points: 172, Visits: 714
Thanks! homebrew01. Is it a mandate that for setting up replication your require a linked server. Secondly, the key column "id" is unique and I need to compare using this key value to see if there is any inconsistencies that might have replicated across onto the subscriber.

I want a generic solution I can customize it accordingly.

Regards,
Faisal
Post #1580448
Posted Saturday, June 14, 2014 10:21 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:24 PM
Points: 2,829, Visits: 8,477
No, you don't need a linked server for replication. I was thinking you could use linked server to run queries to compare data and verify replication is working.

Something as simple as

SELECT count(*) from Table_A run on both servers will confirm the counts are the same, or close enough considering the lag time.

Are you missing ID values at the subscriber ? What are the "inconsistencies" you mention ?



Post #1580908
Posted Monday, June 16, 2014 3:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:38 PM
Points: 3,214, Visits: 2,335

SQL Data Compare might be of some use ...

http://www.red-gate.com/products/sql-development/sql-data-compare/?utm_source=google&utm_medium=cpc&utm_content=brand_aware&utm_campaign=sqldatacompare&gclid=CJq1xMun_74CFSJo7AodYxEAlA




Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #1581852
Posted Tuesday, June 17, 2014 7:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:05 AM
Points: 290, Visits: 1,671
If all you want to know is which rows are out of synch and you're using Transactional replication you could use the msrepl_tran_version stamp (I'm not sure whether this is for Updateable only).

Maybe, something like this:

SELECT
a.*,
b.*
FROM
[serverA].dbName.dbo.TableA a
INNER JOIN [serverB].dbName.dbo.TableA b ON
a.PrimaryKey = b.PrimaryKey
AND a.msrepl_tran_version <> b.msrepl_tran_version

Personally, we use tablediff when we're worried that our subscription's out of synch; using its output we can adapt a handful of script templates to get things back in synch.
Post #1582350
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse