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:
Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Developer, SQL Server 2012 Enterprise Core, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Developer, 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