Post reply

db Mirroring: The principle server has been fixed - tips to make it the primary server again

  • July 23, 2010 at 10:21 am

    #221640

    I'm test; I have a test environment. SQL-1 & SQL-2

    I've performed manual failover, and production continues (on SQL-2) ... excellent.

    In the meanwhile I discover the problem on the principle server and correct it.

    What is the procedure or best-practice to return (SQL-1) back to the principle server?

    thanks much,

    bob

  • July 26, 2010 at 11:26 am

    #1198326

    Is SQL-1 healthy and shows "Restoring" now?

  • July 26, 2010 at 3:25 pm

    #1198462

    Here is the procedure to fail over back to the original principal server:

    (1) Switch mirroring from asynchronous to synchronous mode (if already running in sync mode, no need for this step):

    ALTER DATABASE dbName SET PARTNER SAFETY FULL -- run this on your CURRENT principal server, SQL-2

    (2) Fail over from the current principal to the secondary (originally primary) server:

    ALTER DATABASE dbName SET PARTNER FAILOVER -- run this on your CURRENT principal server, SQL-2

    (3) Switch mirroring from sync to async mode (optional step - only if your requirement is for mirroring to run in async mode):

    ALTER DATABASE dbName SET PARTNER SAFETY OFF -- run this on your OTHER server, SQL-1

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • July 26, 2010 at 3:53 pm

    #1198470

    Thank you - That's what I was looking for.

  • July 26, 2010 at 6:43 pm

    #1198505

    Glad it was helpful.

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

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