Validate the replicated database

Question

Post reply

Validate the replicated database

Ed7

SSCrazy Eights

Points: 8519
More actions
January 11, 2007 at 8:53 am

#115654
Hi room,
I am newbie DBA!!! Just completed replication from Server A to Server B.
Server A is running SQL Server 2005 and Server B is running SQL Servr 2000.
The snapshoot is running successully to pull or copy database from Server B.
Questions:
- I need to validate my Server A database is exactly identical from Server B database. How can I do that?
Please helps!!!
Thank youuuuuuuuuuuuuu.
Edwin

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

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

DB_Andrew Ten Centuries Points: 1089 More actions · Answer 1

There are a number of methods you could use to do this.

1. The simple and quick way is to right click on the publication and select the "Validate Subscriptions..." option.

2. Another way is to manually query and count the number of records in tables to make sure that they are the same. The only problem with this method is depending on the amount of activity your going to have to be very fast as a record could be inserted into the publisher database and by the time you run the same query on the subscriber database it may or may not have been commited by replication.

Example:

SELECT COUNT(*) FROM PublisherTable WITH(NOLOCK)

SELECT COUNT(*) FROM SubscriberTable WITH(NOLOCK)

MohammedU SSCoach Points: 19644 More actions · Answer 2

Snapshot replication is snapshot at a single point so when it runs it will populate the all data it got in snapshot or it will fail...

Check BOL topic "Validating Replicated Data"

MohammedU
Microsoft SQL Server MVP

DB_Andrew Ten Centuries Points: 1089 More actions · Answer 3

He didn't state which replication model he was using. He could be doing transactional which uses a snapshot. If he is doing transactional just because the snapshot succeeded doesn’t mean replication is in synch or working correctly.

Either way it doesn’t matter which model he is using. The easiest way is to use the "Validate" method from with in the Replication Monitor.

MohammedU SSCoach Points: 19644 More actions · Answer 4

I was under the impression that he is using snapshot...

Thanks...

MohammedU
Microsoft SQL Server MVP

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 5

Hi room,

Thank you for your responses. I am setup Transactional Replication using snapshoot.

Thank youuuuuuuuuu

MohammedU SSCoach Points: 19644 More actions · Answer 6

How to: Validate Data at the Subscriber (Replication Transact-SQL Programming)

http://msdn2.microsoft.com/en-us/library/ms147366.aspx

MohammedU
Microsoft SQL Server MVP

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 7

any database compare tool will do the job, SQL Compare, Kentico sql compare , dbghost etc.

In certain circumstances the database schema will not be identical, depends upon the source of course and what options you defined during replciation.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/