DB Mirror failover script, initiated from Mirror, fails

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » DB Mirror failover script, initiated from...

DB Mirror failover script, initiated from Mirror, fails

Rate Topic

Display Mode

Topic Options

Author

Message

Express12

Posted Monday, December 07, 2009 8:39 AM

SSChasing Mays

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:31 AM
Points: 618, Visits: 835

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

steveb.

Posted Monday, December 07, 2009 8:43 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

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 »

Permissions