Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When you have SQL Server mirroring set up, do you trust that your mirror database will stay the same as the primary, even after months and months of mirroring?  I know we all check the status, and of course it says the database is synchronized...  but I worry that somehow the mirror might fall out of sync due to network glitches, software bugs etc. and the fault won't be detected.  Then one day we need the mirror and we're horrified to learn that it's corrupted.

One thing I could do is periodically refresh the mirror from the primary, since our database is not terribly large.  But perhaps I'm being overly paranoid.

I would like to hear from DBAs, either of SQL Server or any other platform, as to whether you share this concern about the integrity of mirror DBs, and if so, what you do to mitigate that concern.  Do you occasionally re-copy your primary DB over to the mirror?  Do you take other measures to ensure consistency between the two?  Thanks for any advice you can provide.

share|improve this question

migrated from serverfault.com May 20 '13 at 8:44

This question came from our site for professional system and network administrators.

2  
In MySQL/Percona/MariaDB world, we have pt-table-checksum, which is used to verify that replicated tables are really in sync with their source. –  EEAA May 19 '13 at 22:05
add comment

3 Answers 3

up vote 4 down vote accepted

I don't trust mirroring 100%, but I do trust it about 99%, same with everything else. It never hurts to periodically check your mirror database and DR scheme. We test our DR plan across the board several times a year, and it would not hurt your scenario to set aside a DR test time and simulate a failure of your primary database to fully test the mirror if you have concerns.

share|improve this answer
add comment

You can try with mysqldbcompare. It compares two databasese and show diferences, and the sql statement needed to correct them, also gererating a script.

http://dev.mysql.com/doc/workbench/en/mysqldbcompare.html

share|improve this answer
add comment

As long as you're monitoring and alerting based on the status of the mirroring for each database then refreshing the secondary shouldn't be needed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.