locked
database mirroring without witness server RRS feed

  • Question

  • Hi,

    If my principal server goes down and we dont have witness server configured then how to make the mirroe server to principal server manually???

    I need to know the answer despretly.

    • Moved by Tom Phillips Monday, April 2, 2012 2:19 PM Database Mirror question (From:SQL Server Database Engine)
    Saturday, March 31, 2012 1:36 PM

Answers

  • Hi,

    Right click the database that you want to failover, select properties, then select the mirroring tab and click on failover.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Proposed as answer by Marek ChmelMVP Saturday, March 31, 2012 1:54 PM
    • Marked as answer by farrukh635 Saturday, March 31, 2012 2:03 PM
    Saturday, March 31, 2012 1:47 PM

All replies

  • Hi,

    Right click the database that you want to failover, select properties, then select the mirroring tab and click on failover.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Proposed as answer by Marek ChmelMVP Saturday, March 31, 2012 1:54 PM
    • Marked as answer by farrukh635 Saturday, March 31, 2012 2:03 PM
    Saturday, March 31, 2012 1:47 PM
  • Hi,

    Thank you ver much for the reply.

    Saturday, March 31, 2012 2:03 PM
  • If my principal server goes down and we dont have witness server configured then how to make the mirroe server to principal server manually???

    Hi,

    If the principal server does down, the option to 'select the mirroring tab and click on failover' will not be available on the mirror server.

    I suspect the correct answer is to use FORCE_SERVICE_ALLOW_DATA_LOSS as per http://msdn.microsoft.com/en-us/library/ms189270.aspx

    Saturday, March 31, 2012 8:57 PM
  • I have seen lots of Post and quires regarding Database Mirroring Failover options without “Witness”. Hope it will help.

    There Might be Scenarios like-

    Sc-1, Principal database is Available and wanted to Failover

    Sc-2, Principal Server is down Mirror is up.

     Sc-1 Follow these Steps-

    Two Types of Manual Failovers

    -By Using GUI

    -By Using T-SQL

    To manually fail over database mirroring Using GUI

    1. Connect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.
    2. Expand Databases, and select the database to be failed over.
    3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
    4. Click Failover.

    To manually fail over a database mirroring session by T-SQL

    1. Connect to the principal server.
    2. Set the database context to the master database:

    USE master;

    1. Issue the following statement on the principal server:

    ALTER DATABASE database_name SET PARTNER FAILOVER, where database_name is the mirrored database.

    This initiates an immediate transition of the mirror server to the principal role.

    On the former principal, clients are disconnected from the database and in-flight transactions are rolled back.

    Sc-2, Principal Server is down Mirror is up.

    To force service in a database mirroring session

    1. Connect to the mirror server.
    2. Issue the following statement:

    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    where <database_name> is the mirrored database.

    The mirror server immediately transitions to principal server, and mirroring is suspended.

    Note- We strongly recommends 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.

    For More Ref- http://technet.microsoft.com/en-us/library/ms189270.aspx

    Hope It will be Helpful, If yes please marked as helpful, If It solves your problem marked as Answer

    Wednesday, April 4, 2012 7:29 AM
    Answerer
  • Hi,

    Right click on your principal server then click on tasks & then click on mirror.

    Then an window will appear there click on failover option.

    Then your principal server becomes mirrored server and mirrored server becomes principal server.


    • Proposed as answer by Vimarsh Monday, April 30, 2012 9:07 AM
    Monday, April 30, 2012 9:06 AM