Installing Patches to Servers Involved in Database Mirroring with Witness

SQLServerF1

Prior to SQL Server 2012, Database Mirroring was heavily used for High Availability for Critical Databases in most of the Organization. It is common to install patches at OS and SQL Server level regularly, so this had to be planned in such a way that the amount of downtime during the patching process in minimal and that we can run the application safely on one of the servers. Below is the environment setup.

Server01 – Principal
Server02 – Mirror
Server03 – Witness
DB1 – Database Mirroring is setup with High-Safety(Synchronous) with Automatic Failover.

Below are steps to apply SQL Server Service Pack or Cumulative Updates or Operating System patches to all the servers(Principal, Mirror, Witness) involved in Database Mirroring.

– Document the current Database Mirroring Configuration settings of all the databases having Mirroring setup.
– Ensure the same SQL Server Logins exist on both the servers.
– Ensure the same SQL Server Jobs exist on both the servers and are to disabled on the Mirror Server Server02(Just in case if there are problems with patching and databases had to be run from the Mirror Server Server02).
– Perform Full backup and Log backup of all the System and User databases on Principal Server and keep it safe. Perform backup of all system databases on Mirror and Witness servers.
– Remove Witness server from the Database Mirroring Configuration. Connect to Principal server and run the below command by changing DBName to remove the witness server. This step needs to be performed on all the databases where database mirroring is setup with witness.

ALTER DATABASE DBName SET WITNESS OFF

Optional, Incase, Database Mirroring session is running in high-performance mode, change it from high-performance mode to high-safety mode by running the below command on Principal Server Server01. This needs to be run on all the databases where database mirroring is setup.

ALTER DATABASE DBName SET PARTNER SAFETY FULL

– Make sure there are no databases acting as Principal on Mirror Server Server02.
– Apply the Service Pack or Cumulative Update to the Mirror Server Server02.
– Reboot the Mirror Server Server02.
– Once the server is back online, monitor and make sure database mirroring started catching up and should get in to Synchronized state.
– Perform a manual failover of Database Mirroring from Server01 to Server02. Run the below command on Principal server Server01. This needs to be run on all the databases where database mirroring is configured.

ALTER DATABASE DBName SET PARTNER FAILOVER

– Make sure you are able to connect to the databases on new Principal server Server02.
– Check SQL Server Errorlogs and Eventlogs to make sure there are no errors.
– If everything looks good, then proceed further.
– Apply the Service Pack or Cumulative Update to the Witness Server Server03.
– Reboot the Witness server Server03.
– Apply the Service Pack or Cumulative Update to the Mirror Server Server01.
– Reboot the new Mirror server Server01.
– Once the server is back online, monitor and make sure database mirroring started catching up and should get in to Synchronized state.
Optionally, change back to high-performance mode by running below query. Run this command on new Principal Server Server02. This needs to be run on all databases that have database mirroring setup.

ALTER DATABASE DBName SET PARTNER SAFETY OFF

– Verify there are no errors in Witness server SQL errorlog
– Add back the Witness Server Server03. Run the below command from new Principal Server Server02. This needs to be run on all database that have database mirroring setup.

ALTER DATABASE DBName SET WITNESS = ‘TCP://Server03:4053’

In case of any issues adding witness back, check if the witness endpoint is present or not and also check the permissions on the endpoint. Below article should help.
http://msdn.microsoft.com/en-us/library/ms190430.aspx

Optionally failover the Database Mirroring back to original Principal Server Server01. You can perform a manual failover of Database Mirroring from Server02 to Server01 by running the below command on Principal server Server02. This needs to be run on all the databases where database mirroring is configured.

ALTER DATABASE DBName SET PARTNER FAILOVER

– Verify SQL errorlog and eventlogs on all three servers to make sure there are no issues.
– Test the Application to make sure there are no issues.
– This completes the successful patching of the servers part of database mirroring.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.