Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Updated: 2012-06-07   |   Comments (32)   |   Related: More > 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


Last Updated: 2012-06-07


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, August 01, 2019 - 10:01:45 AM - Prakash Back To Top

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

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

 


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

 awesome brady...great representat ion...

 


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

 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 01, 2016 - 12:46:43 AM - Ramanath Back To Top

  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 01, 2016 - 12:41:13 AM - Ramanath Back To Top

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

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

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

Nice one ..


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

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

Usefull information Brady....

 

Thanks


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

Very helpful Information Brady..

Thanks for posting


Thursday, October 03, 2013 - 12:44:39 AM - yogi Back To Top

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

 

 

Hi,

 

Nice explanation Brady thanks .


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

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 09, 2012 - 10:00:25 PM - Nathan Back To Top

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

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

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

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

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 09, 2012 - 3:12:06 PM - Brady Back To Top

@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 09, 2012 - 12:43:45 PM - Tony C Back To Top

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

~Tony


Saturday, June 09, 2012 - 10:26:26 AM - Nilesh Back To Top

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 08, 2012 - 3:16:13 PM - eric81 Back To Top

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 08, 2012 - 3:07:09 AM - Muhammad Azim Back To Top

It was very helpful, thanks for sharing.


Friday, June 08, 2012 - 2:49:10 AM - SM Back To Top

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


Friday, June 08, 2012 - 2:37:40 AM - Shamas saeed Back To Top

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 07, 2012 - 10:16:01 PM - karthik Back To Top

HI Brady,

Very good explanation Brady it was helpful for me.

 


Thursday, June 07, 2012 - 3:09:19 PM - Brady Back To Top

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 07, 2012 - 10:06:40 AM - Rao K Back To Top

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 07, 2012 - 7:39:41 AM - abhas mittal Back To Top

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 07, 2012 - 12:48:26 AM - Gopalakrishnan Arthanarisamy Back To Top

Hi Brady Upton,

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

Gopalakrishnan Arthanarisamy,

Unisys, Bangalore.


Learn more about SQL Server tools