Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DB Mirror failover script, initiated from Mirror, fails Expand / Collapse
Author
Message
Posted Monday, December 7, 2009 8:39 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:17 AM
Points: 656, Visits: 948
I setup DB Mirroring (on 2008) w/ SQLSrvr1 as Principal and SQLSrvr2 as Mirror (I did not use a witness)

When I tried to failover the DB from the Mirror server (SQLSrver2) using script:

ALTER DATABASE myDB SET PARTNER FAILOVER

... it failed.. (forget the exact msg but it said you need to be on Principal to do so) .. then I issued same script from Principal (SQLSrver1) and it worked fine.

Problem is -- when the Principal is in a disaster and not available, HOW can I failover to the Mirror without getting this error?


BT
Post #829941
Posted Monday, December 7, 2009 8:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
You will need to add a Witness server to get automatic failover, or you can end up in the situation where you are unable to access the Primary server in the event of a crash to initiate a manual failover

I should add that it is possible to manually force the mirror to start up when the principal is unavailable, however there can be possible data loss, it really is the same action as removing mirroring and restoring the database to a live state.

ALTER DATABASE database_name SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS 

http://msdn.microsoft.com/en-us/library/ms189977.aspx
Post #829943
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse