Replication data inconsistencies

Question

Post reply

Replication data inconsistencies

Feivel

SSCertifiable

Points: 7524
More actions
June 11, 2014 at 1:33 pm

#287436

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Feivel SSCertifiable Points: 7524 More actions · Answer 1

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.

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

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.

Feivel SSCertifiable Points: 7524 More actions · Answer 3

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

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 ?

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

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

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Steve Thompson-454462 SSCrazy Points: 2624 More actions · Answer 6

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.