SQL Server Performance

Manual Failover

Discussion in 'SQL Server 2005 Database Mirroring' started by kdavia, Mar 16, 2007.

  1. kdavia New Member

    I've set up a test mirrored database with the following steps:<br />-- created endpoints<br />-- backed up Primary database<br />-- restored with no recovery on Mirror<br />-- Set partner: ALTER DATABASE test <br /> SET PARTNER = 'TCP://instance<img src='/community/emoticons/emotion-4.gif' alt=':p' />ort'<br />on both Primary and Mirror<br />-- I kept the default of High protection (Safety FULL) - no witness<br /><br />The primary shows (Principal, Synchronized). The mirror shows (Mirror, Syncronized / restoring). <br /><br />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: <br />Msg 1455, Level 16, State 106, Line 1<br />The database mirroring service cannot be forced for database "test" because the database is not in the correct state to become the principal database.<br /><br />Any suggestions? What step have I forgotten/missed?? Thanks much!
  2. satya Moderator

    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.
  3. kdavia New Member

    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!
  4. satya Moderator

    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.

Share This Page