Compare two Databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Compare two Databases

Compare two Databases

Rate Topic

Display Mode

Topic Options

Author

Message

xoom

Posted Wednesday, October 03, 2012 11:27 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 14, 2013 4:36 AM
Points: 10, Visits: 43

Hi,

i have to solve the following problem. We used DoubleTake for the event of a disaster in SQL Server 2008 R2 environment. The prod database server is mirrored 1:1 to a backup server, which is handeld by double Take Software. After a disaster event, the original database is off-line. The system will switch to the mirrored backup System. So far so good but i must prove that the backup System (database) is identical in content and data to the prod DB ?
I've no idear how to do that ? May be a script which compare some Tables ???
in any case, I wait for your ideas, how can I solve the problem and can prove that the database are identical in content or not.

THX

Post #1367879

Sean Lange

Posted Wednesday, October 03, 2012 12:14 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 8,575, Visits: 8,222

xoom (10/3/2012)

Redgate (the people who own and run this site) make a tool called SQLCompare. It is a very handy way to discover any ddl differences.

The data is going to be a little more challenging to prove. You could start with row counts from each db. If they are the same that is a reasonable baseline. Assuming you have primary keys, or some other way to uniquely identify a row, in every single table you could create a query for every table that does a left join to the same table in the mirrored db where the primary key is null. That would give you any rows that are in the main db and NOT in the mirror. Then reverse the query so the mirror is the main table. Both directions should give you no results, meaning there are no primary key values in either table missing from the other. If you have to compare every single data element you could do the same as I just described, but it is going to be painfully tedious to setup.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1367935

xoom

Posted Wednesday, October 03, 2012 12:27 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 14, 2013 4:36 AM
Points: 10, Visits: 43

Thank you for the quick response but the prod DB is in time of a disaster point no longer available! for that reason I can't compare any table with an inner join query. Please keep in mind that the prod db is physically offline in disaster time. I have only the mirrored DB on hand !!

THX

Post #1367952

jerry-621596

Posted Wednesday, October 03, 2012 12:43 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:18 PM
Points: 363, Visits: 574

Just so I understand...

You are being asked to provide a comparison between the mirrored DB and the offline DB during a disaster, before the mirrored DB goes online?

If this is so, like you said, the production DB will not be online which is by definition, the disaster you are preparing for. This will simply not be possible.

The only way you can compare the DBs is while both are online during your disaster recovery drills, or at random intervals before a disaster.

Back to RedGate, they have SQLCompare and SQLDataCompare. Both fantastic products. SQLCompare is great at comparing schemas. SQLDataCompare is used to verify that the data in both databases is identical. Both products can sync any differences found.

In the event of an actual disaster taking down the production DB, you will probably not meet your SLA on the recovery if you have to figure a way to do a compare before bringing the DB mirror online.

Most larger organizations with serious disaster recovery plans do recovery simulations at least once a year. This is a time to rehearse a recovery in realtime. After each drill, you can do a compare of the two databases using the suggested products. This will demonstrate whether the mirroring is occuring as expected.

Hope this helps.

Post #1367965

xoom

Posted Thursday, October 04, 2012 2:39 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 14, 2013 4:36 AM
Points: 10, Visits: 43

I been thereby to provide a solution, which will bee use in test case, to proves that the two DB's are equal. This can happen in following way. I create a deduction from prod system, then shut down the system and bring backup system online. Now I make a deduction from the backup and compare the two.

Post #1368233

anthony.green

Posted Thursday, October 04, 2012 3:13 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Surely what you want is something like logshipping, mirroring etc which automates all this for you, so whatever happens at the primary is sent to the secondary and you dont have to worry about syncing the DB's as SQL does it all for you.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1368249

xoom

Posted Thursday, October 04, 2012 4:30 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 14, 2013 4:36 AM
Points: 10, Visits: 43

o.k sounds good. Since I am a beginner in SQL Server environment, how can I do that? Detailed description would be great.

THX

Post #1368291

anthony.green

Posted Thursday, October 04, 2012 4:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

They are all high availability or disaster recovery solutions in SQL, they are far to technical to get into in a forum, I would strongly recommend doing a google search on the topics and reading up on them yourself.

Places to start your research

logshipping
mirroring

Post #1368293

xoom

Posted Tuesday, October 09, 2012 5:40 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 14, 2013 4:36 AM
Points: 10, Visits: 43

It is only a simulated disaster and want to proves that the mirrored data is correctly on backup server.
I can't use something like this becouse no support for ntext, text etc....

select CHECKSUM_AGG(CHECKSUM(*)) FROM [dbo].[Table_name]

Post #1370334

barsuk

Posted Thursday, October 11, 2012 2:26 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:26 PM
Points: 2,642, Visits: 4,960

Maybe you can create a 'flag' table in your prod database and insert some meaningful values, like daystamp for example, say, every 15 minutes in it thru the job.
And then you can always compare the latest values in both tables in Prod and on DR site....

Post #1371779

« Prev Topic | Next Topic »

Permissions