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:
Stop the SQL Server service.
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:
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: