mirror

Question

mirror

bodhilove

SSCarpal Tunnel

Points: 4035
More actions
September 22, 2008 at 4:52 pm

#193378

Hey Folks,
how can you verify a mirror is working in SQL 2005 standard?
What I mean is -can I check the database will be ok when I need to failover without actually failing over.

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

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

DNA_DBA SSCrazy Eights Points: 9132 More actions · Answer 1

You can see if the databases are in step by using the db mirroring monitor tool or system views The only real way of testing if the mirror works in your environment with your application is to try switching over to the mirror.

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 2

so how do people go about assuring that the mirror is good in preparation for a disaster if you are unable to check the structure of the mirror database. I would assume that there must be some method considering microsoft recommends mirroring.

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 3

"How do people verify that a mirrored database is ready to go (accurate?) in the event of failover?"

I guess it's somewhat an article of faith. You can check the Mirroring Properties of the principal database, using Enterprise Manager, and it will report the status of the mirroring. Usually this is something like "Synchronized: The databases are fully synchronized". This means that the mirrored database is an exact replica of the principal database. (You can also check using the Dynamic Mgmt views, if you prefer)

While you cannot "see" inside the mirrored copy, you could create a test database and mirror it (with or without a witness) and conduct some testing. Perform some changes in the principal database, fail it over, and check the mirror to verify that it is in exactly the same state as the principal.

A more likely point of difficulty is preparing to re-point your applications so that they "follow" the failover. There is only limited utility in having a mirrored database if the applications cannot reach it in the event of a problem with the Principal server.

Our experience (using dot Net applications) has been very successful in this issue, but it's something that definitely should be tested before disaster strikes.

HTH

Elliott

David Leaver Mr or Mrs. 500 Points: 599 More actions · Answer 4

If you need to see "into the database", you can create a snapshot of the mirror. That way you can query the data to prove it's in a state ready for failing over.

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 5

yes that right david . I know about snapshots but I mentioned I was using 2005 standard not enterprise.

What are the system view that will help me look into a mirror?

Mark Sumner SSCommitted Points: 1851 More actions · Answer 6

To create the mirror you restored the transaction log with the 'No Recovery ' option.

This means you can not query the mirror as it is not online. You can think of its state as in a perpetual restore. Every transaction written to the live DB is also 'restored' to the tail of the mirror DB transaction log.

If msdb.sys.database_mirroring view telling you that the databases are synchronised is not sufficient for you then you will need to Remove mirroring then bring the 2nd DB online (Restore with recovery)Now you can query it.

If no changes were made to the Principal DB whilst this takes place you 'should be able to' set up the mirror again without having to backup & restore the log.

I'd recommend believing what you see in sys.database _mirroring.

You could also try stopping SQL server on the Mirror server for 10 minutes and see what gets written to event logs and the Mirror Monitor.

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 7

I suppose the same argument can be posed for a Disaster Recovery Site - how do you know that it really works, without actually doing a full failover? For most companies, this just isn't an option.

The second database option, suggested earlier, is a good one. This will show that the DB is being mirrored, and fail-overed, correctly. It won't test vulnerabilities in your applications though (again, mentioned above).

But, and this is important, the only way you can be sure that everything is set up correctly, is to kill the Principal MSSQL Server and see what happens. Obviously thoroughly plan the operation, warn users, implement at an off-peak time, etc, etc, etc. But this is the only real test. Just bear in mind that it will always be easier to fix problems in this test scenario, where the Principal is not really dead, than in a real-world situation where the Principal really has been destroyed and lost forever.

Just my thoughts,

Andy