This article contains steps that you can follow to install service packs and hotfixes on an instance of Microsoft SQL Server that has one of the following characteristics:
- 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.
Follow these steps to maintain the enhanced protection of data security.
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 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:
- 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:
For more information, visit the following Microsoft Developer Network (MSDN) Web site:
- 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.