SQL Server Performance Forum – Threads Archive

Manual Failover

I’ve set up a test mirrored database with the following steps: – created endpoints – backed up Primary database – restored with no recovery on Mirror – Set partner: ALTER DATABASE test SET PARTNER = ‘TCP://instance<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />ort' on both Primary and Mirror – I kept the default of High protection (Safety FULL) – no witness The primary shows (Principal, Synchronized). The mirror shows (Mirror, Syncronized / restoring). I’m trying to test a simulated failure of the Principal so I can’t manually failover from the Principal. According to what I’ve read on BOL, I need to use Alter database name set partner FORCE_SERVICE_ALLOW_DATA_LOSS if the Principal is not available. However, when I do that I get the following error message: Msg 1455, Level 16, State 106, Line 1 The database mirroring service cannot be forced for database "test" because the database is not in the correct state to become the principal database. Any suggestions? What step have I forgotten/missed?? Thanks much!

Have you checked the log on mirror server and see whether the REDO log has completed the logs restore and no pending on SEND queue. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Thank you for your reply. My problem was that the customer told me that he had stopped the services on the Principal when I tried to force the manual failover, but the instance was still available. Once I shutdown the Principal, everything worked as expected. Thanks for the idea, though!

For a failover initiate you have to be ensure no longer transactions are pending, it will take more time.
Check thishttp://msdn2.microsoft.com/en-us/library/ms186348.aspx for more information,. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>