none
best method to reboot sql server which was setup for mirroring

    Question

  • We have sql server 2008R2 setup for asynchronous mirroring with 12 databases. We need to apply the microsoft quarterly patches which requires the reboot of sql server. We came up with the below steps but wondering if this looks good OR any modifications are required.

    1.Apply the patch on mirrored sql server and reboot
    2.Verify the mirroring sync
    3.Failover dbs to mirrored SQL server
    4.Verify the mirroring sync
    5.Apply the patch on principal server and reboot
    6.Verify the mirroring sync
    7.Failover dbs to principal SQL server
    8.Verify the mirroring sync
    9.Basic IVP for the application

     

    Tuesday, February 22, 2011 9:39 PM

Answers

All replies

  • Hello,

    To know more read the following Order for Installing Service Pack on a Database Mirroring Servers?  to know more on the process to be followed.  Also refer the following article.

    Hope that Helps!

    Cheers,
    Ashish Kumar Mehta

    Please click the Mark as Answer button if a post solves your problem and Vote as Helpful if a Post is Helpful!

    Wednesday, February 23, 2011 12:30 PM
  • As it is set to asynchronous mode . please take the below notes into consideration

    Manual Failover: This kind of failover is possible only in High Protection configuration. If mirroring is running in High Performance configuration, it has to be changed to High Protection in order to be failed over manually to mirror server.

    This can be done right-clicking on mirrored database on principal server and selecting ‘Mirroring’ tab under ‘Properties’.

    Once High Protection configuration is selected, click ‘Failover’ will initial the failover process.

    In case of High Performance configuration, if the principal server goes down, the mirror server can be brought online using restore command with ‘recovery’ option. This can also be termed as a manual failover.

     

    Steps to note :

    Step1: As a Best practice take a full backup on production server

    Step2: Always perform the maintenance on your Mirror server first. You may perform reboots, install service packs, etc even without touching the principal server and once you are done performing maintenance on the mirror server you should make sure that the mirroring state on the principal server becomes ―SYNCHRONIZED

    Step3: Once you confirm that the mirroring state has become ―SYNCHRONIZED on the principal server, switch the principal to mirror by running the Alter Database Failover statement from the Principal server:

    Alter Database <Database_Name> SET PARTNER FAILOVER;

     

     Above statement will perform a failover from your principal server to the mirror server, during this failover all the users connected to the principal server will be disconnected and any new users connecting to the database will be auto-redirected to the mirror server.

    It is recommended that you stop the application for a brief moment of manual failover and restart after the failover succeeds. In this case, you will need to point your application to the new principal database server. You can now perform the hardware and software changes on the old principal server. As soon as you are done with the change and the database becomes available, it automatically re-establishes the mirroring session and assumes the role of the mirror

     

    Refer the following link please
    http://technet.microsoft.com/en-us/library/bb497962(SQL.90).aspx


    -----------------------------------
    Thanks,Suhas V

     

    Wednesday, February 23, 2011 1:32 PM
  • The method for applying patches to mirrored database servers is well documented by Microsoft. 

    Please see:

    http://msdn.microsoft.com/en-us/library/bb497962.aspx

    • Marked as answer by SM_SQL Wednesday, February 23, 2011 4:22 PM
    Wednesday, February 23, 2011 1:38 PM