Microsoft KB Archive/926824

Knowledge Base

How to install service packs and hotfixes on an instance of SQL Server 2005 that is configured to use database mirroring

Article ID: 926824

Article Last Modified on 11/20/2007

---

APPLIES TO

• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Enterprise Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL Server 2005 Standard X64 Edition
• Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
• Microsoft SQL Server 2005 Enterprise X64 Edition
• Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems

---

INTRODUCTION

This article contains steps that you can follow to install service packs and hotfixes on an instance of Microsoft SQL Server 2005 that has one of the following characteristics:

• The instance of SQL Server 2005 has one or more databases that are configured to use database mirroring.
• The instance of SQL Server 2005 is acting as a witness server for a database mirroring session.

Follow these steps to maintain the enhanced protection of data security.

SQL Server 2005 does not have to be performing a particular server role in a database mirroring session when you apply a service pack or a hotfix. For example, SQL Server 2005 may be performing the principal role or the mirror role. However, we recommend that you update partner servers when they are running in the mirror role, and then update the witness server.

MORE INFORMATION

If you install service packs or hotfixes on servers that are in a database mirroring environment, you must determine the role of the servers. If there are many database mirroring sessions, you must determine all possible roles that could apply to a server. For example, if the server is acting as a mirror server for any database mirroring session, update the server as the mirror role. If the server is only a witness server for all database mirroring sessions, update the server as the witness role. To do this, follow these steps:

1. If a witness server is in the database mirroring session, disable the automatic failover during the update process. To do this, remove the witness server from the database mirroring session. If the server is not a partner server of some other database mirroring sessions, follow these steps to disable automatic failover on the witness server:
   1. Stop the SQL Server service.
   2. Use the ALTER ENDPOINT Transact-SQL statement to disable the database mirroring endpoint.

   For more information, visit the following Microsoft Developer Network (MSDN) Web site:

   http://msdn2.microsoft.com/en-us/library/ms366340.aspx

2. If the safety level of the database mirroring session is set to OFF (the Asynchronous mode), change the safety level to FULL (Synchronous mode). This step is required to perform the manual failovers in later steps.
   
   Note After you change the safety level to FULL, data changes are still permitted in the principal database if the database mirroring session is paused.
3. Wait for all the database mirroring sessions to be in Synchronous mode. If you are running a version of SQL Server 2005 earlier than service pack 2, you must perform manual failover to the mirror before going to the next step. The mirror server assumes the principal role.
4. Pause the database mirroring sessions that are present on the server. This step prevents changes to the principal database.
   
   For more information, visit the following MSDN Web site:

   http://msdn2.microsoft.com/en-us/library/ms175539.aspx

5. Perform a full database backup on the principal database, and then, run the DBCC CHECKDB command on the principal database. This step is optional but recommended.
6. Install the service pack or the hotfix on the mirror server. Remember that you may have to update multiple servers at this point.
7. Resume the database mirroring sessions. For more information about how to resume a database mirroring session, visit the following MSDN Web site:

   http://msdn2.microsoft.com/en-us/library/ms191125.aspx

8. Perform manual failover to the mirror server so that the mirror server assumes the principal role.
   
   For more information about how to manually perform failover to the mirror server, see the "Manually Failing Over to a Secondary Database" topic in SQL Server 2005 Books Online.
9. Run the DBCC CHECKDB command on the principal server. (This step is optional, but recommended.)
10. Pause the database mirroring sessions.
11. Install the service pack or the hotfix on the new mirror server.
    
    Note The new mirror server is same as the original principal server. Remember that you may have to update multiple servers at this point.
12. Resume the database mirroring sessions.
13. If you changed the safety level in step 2, change the safety level back to OFF.
14. If the database mirroring session has a witness server, undo the changes that you made in step 1. For more information about how to do this, visit the following Microsoft Web site:

    http://msdn2.microsoft.com/en-us/library/ms365603.aspx

    Note When you undo the changes that you made in step 1, you add the witness server back into the database mirroring session.

Additional query words: Database Mirroring

Keywords: kbhowto kbinfo kbexpertiseadvanced kbsql2005engine KB926824

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.