How to apply a hotfix for SQL Server in a transactional replication and database mirroring topology

Article translations Article translations
Article ID: 977051 - View products that this article applies to.
Expand all | Collapse all

On This Page

INTRODUCTION

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.

More information

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:
  1. The mirror
  2. The witness/distributor
  3. The principal/publisher
  4. The subscriber(s)
If the witness and the distributor are not on the same server, the installation steps are as follows:
  1. The mirror
  2. The witness
  3. The distributor
  4. The principal/publisher
  5. The subscriber(s)

Procedure


  1. 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:
  2. 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.
  3. Install the service pack or the hotfix on the mirror server. Remember that you may have to update multiple servers at this point.
  4. Install the service pack or the hotfix on the witness server.
  5. 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.
  6. Resume the database mirroring sessions.

    For more information about how to resume a database mirroring session, visit the following MSDN Web site:
    How to: Resume a Database Mirroring Session (Transact-SQL)
  7. Perform a manual failover to the mirror server so that the mirror server resumes the principal and publisher role.

    For more information about how to manually perform failover to the mirror server, see the "Manually Failing Over to a Secondary Database" topic in SQL Server 2005 or SQL Server 2008 Books Online.
  8. Run the DBCC CHECKDB command on the principal server.
    Note This step is optional, but recommended.
  9. Pause the database mirroring sessions.
  10. Install the service pack or the hotfix on the new mirror server.

    Note The new mirror server is the same as the original principal/publisher server. Remember that you may have to update multiple servers at this point.
  11. Resume the database mirroring sessions.
  12. 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 Developer Network (MSDN) site:
    How to: Add or Replace a Database Mirroring Witness (SQL Server Management Studio)


    Note When you undo the changes that you made in step 1, the witness server is added back into the database mirroring session.
  13. 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.

Properties

Article ID: 977051 - Last Review: January 18, 2013 - Revision: 2.0
Applies to
  • 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 Standard
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
Keywords: 
kbexpertiseadvanced kbinfo kbsurveynew kbhowto KB977051

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com