How to install service packs and hotfixes on an instance of SQL Server that is configured to use database mirroring
- The instance of SQL Server has one or more databases that are configured to use database mirroring.
- The instance of SQL Server is acting as a witness server for a database mirroring session.
SQL Server 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 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.
- 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:
- Stop the SQL Server service.
- Use the ALTER ENDPOINT Transact-SQL statement to disable the database mirroring endpoint.
- 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.
- Wait for all the database mirroring sessions to be in Synchronous mode.
Note 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.
- 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:
- 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.
- Install the service pack or the hotfix on the mirror server. Remember that you may have to update multiple servers at this point.
- Resume the database mirroring sessions. For more information about how to resume a database mirroring session, visit the following MSDN Web site:
- Perform manual failover to the mirror server so that the mirror server assumes the principal role.
Note For more information about how to manually perform failover to the mirror server in SQL Server 2005, see the "Manually Failing Over to a Secondary Database" topic in SQL Server 2005 Books Online.
- Run the DBCC CHECKDB command on the principal server. (This step is optional, but recommended.)
- Pause the database mirroring sessions.
- 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.
- Resume the database mirroring sessions.
- If you changed the safety level in step 2, change the safety level back to OFF.
- 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: Note When you undo the changes that you made in step 1, you add the witness server back into the database mirroring session.
Article ID: 926824 - Last Review: 01/18/2013 23:27:00 - Revision: 3.0
- kbexpertiseadvanced kbhowto kbsql2005engine kbinfo KB926824