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
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!
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!
- Proposed as answer by Ashish Kumar Mehta 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