October 3, 2012 at 11:27 am
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
October 3, 2012 at 12:14 pm
xoom (10/3/2012)
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
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2012 at 12:27 pm
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
October 3, 2012 at 12:43 pm
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.
October 4, 2012 at 2:39 am
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.
October 4, 2012 at 3:13 am
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.
October 4, 2012 at 4:30 am
o.k sounds good. Since I am a beginner in SQL Server environment, how can I do that? Detailed description would be great.
THX
October 4, 2012 at 4:32 am
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
October 9, 2012 at 5:40 am
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]
October 11, 2012 at 2:26 pm
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....
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply