Post reply

Database Mirroring

  • November 21, 2016 at 2:08 am

    #323112

    I have to reboot both the principle and mirror servers (a witness server is present) and I do not want a failover to occur during the reboot process . I just wonder what is the proper steps to do it. I've done a search on the internet and am confused about the uses of "alter database xxx set witness off", "alter database xxx set partner suspend" and "alter database xxx set partner off".

  • November 21, 2016 at 4:09 am

    #1913647

    Hi

    If you don't want to have a failover, then you'll have to have a downtime while rebooting.

    You can go with ALTER DATABASE XXX SET PARTNER OFF, and then re-establish the mirroring.

    Igor Micev,My blog: www.igormicev.com

  • November 21, 2016 at 9:37 pm

    #1913905

    Since I have time limit on the reboot progress and I don't want to run the risk of needing to reconfigurate the mirroring and run into some errors. Would it be better if I use "alter database xx set partner suspend" and run "alter database xx set partner resume" after the reboot?

  • November 22, 2016 at 3:12 am

    #1913933

    alexlihw (11/21/2016)

    Since I have time limit on the reboot progress and I don't want to run the risk of needing to reconfigurate the mirroring and run into some errors. Would it be better if I use "alter database xx set partner suspend" and run "alter database xx set partner resume" after the reboot?

    This is how I'm re-establishing the mirroring on a test database (nd03srv is the Principal). Simple.

    --Turn off mirroring

    ALTER DATABASE Northwind SET PARTNER OFF;

    --Reestablish mirroring:

    --Execute this against the Mirror Instance.

    ALTER DATABASE Northwind

    SET PARTNER = 'TCP://nd03srv.cluster.com:5022'

    GO

    --Execute this against the Principal Instance.

    ALTER DATABASE Northwind

    SET PARTNER = 'TCP://nd04srv.cluster.com:5023'

    GO

    ALTER DATABASE Northwind

    SET WITNESS = 'TCP://WITNESSSERVER:5024'

    GO

    For pausing/suspending, I'd first read the official reference https://msdn.microsoft.com/en-us/library/ms175082.aspx

    Then you decide how would you go. I think both ways are going to finish your job.

    Igor Micev,My blog: www.igormicev.com

  • November 27, 2016 at 11:53 pm

    #1914657

    I rebooted the principle and mirror servers last Friday and things didn't go quite as expected. I started off by using "set partner suspend" and then proceeded to reboot the mirror server and unfortunately the mirror server froze at the Windows startup screen because of a newly installed antivirus agent conflict . At that point, I was expecting the principle database to continue to work but user calls started to pour in. I found out that status of the principle database had changed to disconnected. I eventually had to use "set partner off" to remove the database mirroring to make the principle database work again. My question is why the principle database didn't work when the mirror server was down. Is it by design or just a misconfiguration on the servers? Thanks

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

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