This article contains steps that you can follow to install service packs and hotfixes on an instance of Microsoft SQL Server with the following characteristics:
The instance of SQL Server has one or more databases that participates in both a database mirroring, and transactional replication topology.
The database participates as a publisher, as a distributor, or as a subscriber.
Note The distribution database cannot be mirrored. However, it can co-exist with the principal/publisher database, or with the database mirroring witness.
The steps to apply hotfixes to a SQL Server that participates in either a database mirroring or transactional replication are documented in the following Microsoft Knowledge Base (KB) articles.
941232 How to apply a hotfix for SQL Server 2005 in a replication topology
926824 How to install service packs and hotfixes on an instance of SQL Server 2005 that is configured to use database mirroring
In an environment where a SQL Server is configured to participate in both database mirroring and transactional replication topology, if the witness and the distributor are on the same server instance, the installation steps are as follows:
If the witness and the distributor are not on the same server, the installation steps are as follows:
If a witness server is in the database mirroring session, you must disable the automatic failover feature 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:
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:
Perform a full database backup on the principal/publisher database, and then run the DBCC CHECKDB command on the principal database. Note This step is optional, however, it is recommended. . Note This step will impede production activity. Therefore, you should schedule a maintenance window for this step.
Install the service pack or the hotfix on the mirror server. Remember that you may have to update multiple servers at this point.
Install the service pack or the hotfix on the witness server.
Install the service pack or hotfix on the distributor. If the distributor is located on the same server instance as the witness, these server roles will be updated at the same time. Note Replication will be temporarily suspended while the update is being applied. Transactions will remain in the publisher transaction log during the update and will then be replicated as soon as the SQL Service is restarted on the distributor.
Resume the database mirroring sessions.
For more information about how to resume a database mirroring session, visit the following MSDN Web site:
Note When you undo the changes that you made in step 1, the witness server is added back into the database mirroring session.
Install the service pack or hotfix on the subscriber(s). During this process, replication from the distributor to the subscriber(s) will be temporarily suspended, and transactions will be queued in the distribution database. If the subscriber is mirrored and a different witness server is used, follow steps 1 to 3 to update the mirror server first, followed by the witness.