Steps to Apply a Service Pack or Patch to Mirrored SQL Server Databases

By:   |   Comments (33)   |   Related: > Database Mirroring


Problem

I'm planning on patching my SQL Servers to the latest service pack, but I'm not sure how to complete this for a environment that is using database mirroring.

Solution

In this tip, I am going to outline my environment and then walk through the process of patching mirrored servers.

My test environment consists of two SQL Server 2005 SP2 servers named SSQL1 (principal) and SSQL2 (mirror) that contain eleven mirrored databases. The database mirroring operating mode is set to asynchronous and I'm upgrading to SP4.

Here is a look at the two mirrored instances.

sql server ssms mirrored databases

Step 1
Always backup all system and user databases before applying patches. Refer to this SQL Server backup tutorial for more information about creating backups.

Step 2
Remote Desktop into the "Mirror" server (SSQL2 in our example) and download/copy the patch to the server.

Step 3
Stop all SQL Services on the "Mirror" server.

Step 4
Run the patch on the "Mirror" server.

Step 5
Once the patch is complete, reboot the "Mirror" server.

Step 6 (optional)
If your database mirroring is set to asynchronous (High Performance mode), we will need to synchronize the databases first. To do this we will need to issue the following statement for every database on the "Principal" server that is mirrored.

ALTER DATABASE databasename SET SAFETY FULL

Here are the commands for the 11 databases on my server.

sql server mirrored databases set safety full

Step 7
The databases might change to "synchronizing" while the transactions catch up. Once all of the databases show "synchronized", as shown below, we can perform the manual failover.

database mirroring synchronized

We can perform the failover using the following statement on the principal server for each database:

ALTER DATABASE databasename SET PARTNER FAILOVER

Here are the commands for the 11 databases on my server.

sql database mirroring set partner failover

We can now see that the servers have switched roles.

ssms db mirrroring state

Step 8 (optional)
Once the failover has taken place and all the databases have switched roles we can set the operating mode back to asynchronous using the following statement on the new principal server:

ALTER DATABASE databasename SET SAFETY OFF

Here are the commands for the 11 databases on my server.

sql server database mirror set safety off

Patch the New Mirror Server

Now that the roles have been swapped, we will patch our "new" mirror server (SSQL1 in our example).

Step 9
Remote Desktop into the "new" "Mirror" server (SSQL1 in our example) and download/copy the patch to the server.

Step 10
Stop all SQL Services.

Step 11
Run the patch on the server.

Step 12
Once the patch is complete, reboot the server (SSQL1).

Step 13
Upon reboot and login, make sure all databases come online clean and synchronized.

Step 14
You now have two choices, leave the databases alone and leave SSQL2 as the principal and SSQL1 as the mirror or you can fail back to return the environment back to how it was from the beginning.

Step 15 (optional)
If you want to failback again follow these steps.

If you are using asynchronous mode issue this statement for each database:

ALTER DATABASE databasename SET SAFETY FULL

Once the databases are synchronized then run this statement for each database:

ALTER DATABASE databasename SET PARTNER FAILOVER

If you need to turn the asynchronous mode on again run this statement for each database:

ALTER DATABASE databasename SET SAFETY OFF

Here is an example of the commands to run on both servers.

database mirroring commands
Next Steps
  • If you are already using synchronous mirroring please ignore the "SET SAFETY" steps
  • To learn how to create a mirrored environment check out this tip


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, June 15, 2023 - 5:18:34 AM - rusdi Back To Top (91296)
thanks for this articles, but why not pause mirror before install upgrade?

Thursday, August 1, 2019 - 10:01:45 AM - Prakash Back To Top (81929)

I have SQL server 2012 with synchronized database (like Active and Passive) the Server 1 is for Application uses and server 2 is Read only. now i want to upgrade the SQL server patch for the server 1 and will it impact synchronization.(patch upgrade to - 13.0.4574.0 - May 16, 2019)


Saturday, January 13, 2018 - 10:09:27 PM - krishna Kumar Rai Back To Top (74951)

 Thanks for this article. Yesterday i faced same question in interview.

 


Wednesday, April 27, 2016 - 11:26:58 PM - ripon datta Back To Top (41353)

 awesome brady...great representat ion...

 


Thursday, April 14, 2016 - 2:27:08 AM - Anoop Agarwal Back To Top (41216)

 good tip.

One question. It is advisable to change safety from full to OFF when we are patching mirror, so that our Tlog on principle does not grow too be big.

 


Monday, February 1, 2016 - 12:46:43 AM - Ramanath Back To Top (40559)

  Hi Brady Upton,

Great Exposure with screens and used simple words to understand everyone and commands for the applying patches on Mirror Databases.

Ramanath Kunda,

ATOS, Benagalore 


Monday, February 1, 2016 - 12:41:13 AM - Ramanath Back To Top (40558)

Looks the way its present with screens, its live presenataion and easy to undesrand.

thanks for the lovely and beatiful presentaion. 

 


Tuesday, September 15, 2015 - 2:00:22 PM - Raghav Back To Top (38687)

Brady, Thanks for such a nice article. I have been working on MySQL and Oracle environments but recently started working on SQL Server. I am really excited and hope my patching goes well. 

 


Saturday, April 18, 2015 - 10:25:13 AM - robert jaudon Back To Top (36977)

Brady,  Thank you for the post.  I just recently got put into a posiion of having to reboot my companies SQL env.  We have a mirrored setup and your post is a great start for me to understand the process.  I am friggin printing this sucker out to compare and take notes on.  

 

Rob


Tuesday, January 20, 2015 - 1:02:00 AM - Abhishek Joshi Back To Top (35991)

Nice one ..


Friday, May 16, 2014 - 4:43:43 AM - jason Back To Top (30803)

if we set partner to suspend mode (once all of the databases are synchronized )  before stop the partner sql server instance, will it be more safer ?  


Tuesday, January 21, 2014 - 3:08:44 AM - Mahesh Back To Top (28155)

Usefull information Brady....

 

Thanks


Monday, October 28, 2013 - 11:03:25 AM - Logicinside22 Back To Top (27295)

Very helpful Information Brady..

Thanks for posting


Thursday, October 3, 2013 - 12:44:39 AM - yogi Back To Top (27020)

Hi Brandy,

 

it is a nice article, i have one doubt ! , why we are changing asynchronous to full safety. 

can any one clear my doubt


Wednesday, November 28, 2012 - 11:36:41 PM - shankar Reddy Back To Top (20618)

 

 

Hi,

 

Nice explanation Brady thanks .


Friday, October 12, 2012 - 9:06:34 AM - Praveen Back To Top (19891)

hi,

I have sql server 2008 R2 server with RTM with mirror configured in it... now i would like to install sp2 on Principle & Mirror servers.. is it possible to apply SP2 without failover to principle server...downtime is ok with me.

 

Regards,

Praveen


Tuesday, October 9, 2012 - 10:00:25 PM - Nathan Back To Top (19842)

Thanks for the helpful guide. I'm planning the patch process for an environment featuring SQL Mirroring with a Witness server.  Do you have any thoughts on the safest point at which the Witness instance can be patched?  I'm inclined to think that it could be inserted as:

Step 5a: (Optional) - Patch the Witness server at this point, if you have one.

 

Regards,
Nathan


Wednesday, June 27, 2012 - 2:50:57 PM - Brady Back To Top (18228)

Chandan,

I've never ran into a situation like this so I can't speak from experience.

If the mirror patch failed I would continue to try and fix the failure and pause mirroring. Once the patch was applied successfully I would start mirroring and allow the transactions to catch up.


Wednesday, June 27, 2012 - 4:19:46 AM - Chandan Back To Top (18216)

Thanks for the good explanation. however, I have one question here and need your inputs on the same.

 

Once you patched the mirror server first, its version gets updated to a higher one. Till this remains principal it is okay but what if the patch fails on original principal which is in the mirror role now. It might take considerable time to fix the issue and meanwhile if failover occurs, the database will be inconsistent because it has a higher version and cant be used on a lower version.

 

Will it not be the same scenario where mirroring can be done between 2005 and 2008 where 2005 can be in principal but once failover happens and 2008 becomes pricinpla, logs cannot be sent to 2005mirror because higher to lower is not supported.

 

Please correct me if you find any discrepancies in my thoughts.

 

Regards

Chandan


Friday, June 22, 2012 - 2:22:39 AM - Mahesh Back To Top (18169)

Thank you for your ecellent explanation.

 

How to patch mirrored SQL Server databases if it is active active Cluster environment.

 


Wednesday, June 13, 2012 - 10:30:01 AM - karthik Back To Top (17971)

The way you present things is excellent brady,

do we need to stop the application services also when you are applying any patch

 


Saturday, June 9, 2012 - 3:12:06 PM - Brady Back To Top (17908)

@SM - yes, if downtime is acceptable you can patch both without manually failing over.

@eric81 - You don't "have" to run the SET SAFETY scripts. Running them ensures that the databases are synchronized before failing over.

@Tony C - Steps are the same for 2008R2. I haven't tested mirroring with 2012 but I would assume they are the same as well.

 

Thanks for the feedback!

 


Saturday, June 9, 2012 - 12:43:45 PM - Tony C Back To Top (17905)

Very cool. Thanks Brady! Are the steps the same in 2008R2/2012?

~Tony


Saturday, June 9, 2012 - 10:26:26 AM - Nilesh Back To Top (17904)

thank you Brady sharing the steps for upgrading the Mirrored Server. Could you please share the steps to upgrade Cluster Server, basic one like Active-Passive node cluster.

 

- Nilesh


Friday, June 8, 2012 - 3:16:13 PM - eric81 Back To Top (17888)

So even though your databases are set to High Performance you still have to run ALTER DATABASE databasename SET SAFETY FULL used for Synchronous Database Mirroring (High-Safety Mode)?


Friday, June 8, 2012 - 3:07:09 AM - Muhammad Azim Back To Top (17875)

It was very helpful, thanks for sharing.


Friday, June 8, 2012 - 2:49:10 AM - SM Back To Top (17873)

Do you have to failover?  Can you not just patch the principle and mirror without failing over if the downtime is acceptable?


Friday, June 8, 2012 - 2:37:40 AM - Shamas saeed Back To Top (17872)

Thanks, I am actually running Mirror server but did not get chance to update any patch but if i need this will definately required. So I am happy now to read this.


Thursday, June 7, 2012 - 10:16:01 PM - karthik Back To Top (17870)

HI Brady,

Very good explanation Brady it was helpful for me.

 


Thursday, June 7, 2012 - 3:09:19 PM - Brady Back To Top (17853)

Abhas,

Best practice is to start with the mirrored server, though I suppose it wouldn't hurt starting with the primary server. I would definitely test this in a test environment before implementing in production.


Thursday, June 7, 2012 - 10:06:40 AM - Rao K Back To Top (17844)

Hi Brady,

wow! one of my interview same question they asked to me.  I never worked on SQL server mirror db. I told directly I am not worked sql data mirror and I could not get opportunity any my worked companies.   I worked mostly cluster,log shipping and Replication sql servers only.  It is very good explanation.  Thank you.


Thursday, June 7, 2012 - 7:39:41 AM - abhas mittal Back To Top (17834)

Hi Brady,

Thanks for a good explanation. Just one thing coming across my mind of whats the impact if we start with the primary database server in the similar setup. Since the services will be restarted mirroring will be kept on hold and resumed post restart. We can also try implementing it parallely across the mirror and mirrored database servers. Do let me know your opinion about this.


Thursday, June 7, 2012 - 12:48:26 AM - Gopalakrishnan Arthanarisamy Back To Top (17820)

Hi Brady Upton,

Great Stuff and Good Explanation for applying patches on Mirror Databases.

Gopalakrishnan Arthanarisamy,

Unisys, Bangalore.















get free sql tips
agree to terms