Post reply

Disaster Recovery Issue with Database Mirroring

  • August 7, 2008 at 4:52 am

    #196717

    I've setup database mirroring with three servers:

    A: Principal (Site A)

    B: Mirror (Site B)

    C: Witness (Site A)

    Witness Server was placed in Site A, because at night time the link between sites is busy and would cause an unwanted automatic failover to DR site.

    I've successfully tested loss of A: Principal server and automatic failover occurs.

    However now I trying to test Disaster Recovery by taking the servers in Site A offline. To simulate this I shutdown C: Witness Server and then immediately shutdown A: Principal Server.

    This means no automatic failover takes place, so how do I manually failover to the mirror when A: and C: are unavailable?

    I logged onto B: Mirror Server in Site B, opened SQL Server Management Studio and right clicked a Database and clicked Mirror. This gives an error message.

    Does anyone have any advice on this?

  • August 7, 2008 at 9:50 am

    #854561

    Try something like this...

    --Manually Failover.

    --Run on Primary.

    ALTER DATABASE MirrorTest SET PARTNER FAILOVER

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • August 11, 2008 at 9:11 pm

    #856465

    You will need to do this on the mirror server:

    [font="Courier New"]ALTER DATABASE DBName SET PARTNER FAILOVER WITH FORCE_SERVICE_ALLOW_DATA_LOSS[/font]

    Since the mirror server can no longer see the primary server and negotiate a clean failover, you have to tell it the primary server is not there and to fail over anyway, potentially incurring loss of data.

    From BOL:

    [font="Times New Roman"]FORCE_SERVICE_ALLOW_DATA_LOSS

    Forces database service to the mirror database after the principal server fails with the database in an unsynchronized state or in a synchronized state when automatic failover does not occur.

    We strongly recommend that you force service only if the principal server is no longer running. Otherwise, some clients might continue to access the original principal database instead of the new principal database.

    FORCE_SERVICE_ALLOW_DATA_LOSS is available only on the mirror server and only under all the following conditions:

    - The principal server is down.

    - WITNESS is set to OFF or the witness is connected to the mirror server.

    Force service only if you are willing to risk losing some data in order to restore service to the database immediately. For information about alternatives to forcing service, see Asynchronous Database Mirroring (High-Performance Mode). [/font]

    Check the ALTER DATABASE entry in BOL for more details.


    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare

  • August 12, 2008 at 1:11 am

    #856516

    sql (8/7/2008)

    I've setup database mirroring with three servers:

    A: Principal (Site A)

    B: Mirror (Site B)

    C: Witness (Site A)

    Witness Server was placed in Site A, because at night time the link between sites is busy and would cause an unwanted automatic failover to DR site.

    I've successfully tested loss of A: Principal server and automatic failover occurs.

    However now I trying to test Disaster Recovery by taking the servers in Site A offline. To simulate this I shutdown C: Witness Server and then immediately shutdown A: Principal Server.

    This means no automatic failover takes place, so how do I manually failover to the mirror when A: and C: are unavailable?

    I logged onto B: Mirror Server in Site B, opened SQL Server Management Studio and right clicked a Database and clicked Mirror. This gives an error message.

    Does anyone have any advice on this?

    With db mirroring in full mode, a quorum must be available.

    That means that a server must be able to connect to the other server or to the witness !

    A automatic failover can only be performed if your witness is available to your second server.

    So in an 'ideal' situation, ServerC (witness) should reside on another electric circuit than serverA and ServerB.

    So a mirrored db in this setup will only be available if a quorum can be achieved.

    However, you do not need to have a witness, if you don't want automatic failover, but always want to control the failover yourself.

    Books online has good info on mirroring.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 4 posts - 1 through 3 (of 3 total)

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