The sequence to install service packs or hotfixes on an instance of SQL Server that is part of Log Shipping/Database Mirroring/Replication/Failover Clustering environment

    When an instance of SQL Server is configured as part of Log Shipping, Database Mirroring, Replication, or Failover Clustering environment, it is important to install service packs or hotfixes in a correct sequence otherwise we may get unexpected issues. This blog post will give you a guild line of sequences in such as environment.

    Log Shipping

    There is no required sequence to apply a service pack or hotfix for Primary, Secondary and Monitor servers in a Log Shipping environment. The following is my preferable to apply service pack or hotfix:

    1. Apply the service pack or hotfix on the Monitor server.
    2. Apply the service pack or hotfix on the all Secondary servers.
    3. Apply the service pack or hotfix on the Primary server.

   Database Mirroring

    If you install service packs or hotfixes on servers in a database mirroring environment, you need to determine the role of the servers. If there are many mirroring sessions configured on the server, you need to determine all possible roles that could be. For instance, if the server is acting as a mirror server for any database mirroring session, update the server as the mirror role for all mirroring sessions. To do this, follow these steps:

    1. If a witness server is configured in the database mirroring session, disable the automatic failover during the update process. To do this, remove the witness server from the mirroring session. For more information, see How to: Remove the Witness from a Database Mirroring Session.
    2. If the safety level of the database mirroring session is OFF (asynchronous mode), change the safety level to FULL (this is required in step 3).
    3. Make sure all the database mirroring sessions to be in Synchronous mode and synchronized.
    4. Pause the database mirroring sessions that are present on the server. This step prevents changes to the principal database. For more information, see: How to: Pause a Database Mirroring Session.
    5. Install the service pack or hotfix on the mirror server.
    6. Resume the database mirroring sessions. For more information, see: How to: Resume a Database Mirroring Session.
    7. Perform manual failover (all the mirroring sessions on this principal server) to the mirror server so that mirroring server assumes the principal role.
    8. Pause the database mirroring sessions as step 4.
    9. Install the service pack or hotfix on the new mirror server (previous principal server).
    10. Resume the database mirroring sessions.
    11. If you changed the safety level in step 2, change the safety level back to OFF.
    12. If the database mirroring session has a witness server, undo the changes made in step 1. For more information, see: How to: Add or Replace a Database Mirroring Witness.

   Replication

    In a replication environment, there is no preferable sequence to apply service pack or hotfix for non-bidirectional replication typology. However, for bi-directional replication typology such as merge typology or transactional replication with updateable subscriptions, you must upgrade Distributor, Publisher, and Subscribers in the following order:

    1. Apply the service pack or hotfix on the Distributor server.
    2. Apply the service pack or hotfix on the Publisher server.
    3. Apply the service pack or hotfix on the Subscriber server.

   Failover Clustering

    In SQL Server 2005, if you want to install a service pack or hotfix, you must install the setup on the Active node (node that currently runs SQL Server services). When running the setup which will launch simultaneously “remote silence” on all passive nodes.

    However, in SQL Server 2008 and SQL Server 2008 R2, the service pack or hotfix deployment is changed to reduce the downtime. Now, you must install the service pack or hotfix on the passive node first. To do this, following these steps:

    1. Apply the service pack or hotfix on the passive node (or all passive node if you have more that one).
    2. Reboot the passive node.
    3. Failover the SQL Server failover cluster to the passive node (and the passive node becomes active now).
    4. Apply the service pack or hotfix on the new passive node (previous active node).
    5. Reboot the passive node.
Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Setup & Upgrade and tagged , . Bookmark the permalink.

2 Responses to The sequence to install service packs or hotfixes on an instance of SQL Server that is part of Log Shipping/Database Mirroring/Replication/Failover Clustering environment

  1. Balakrishna says:

    good one

  2. Sandip Patel says:

    Good One, This helps. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s