DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 958734 - Last Review: January 18, 2013 - Revision: 6.0

On This Page

INTRODUCTION

This article describes how to apply a Microsoft SQL Server update to a failover cluster instance.

Important
  • If you update a SQL Server failover cluster instance on an active node, SQL Server services will be stopped. This causes SQL Server downtime. To avoid downtime, install a rolling update on passive nodes as explained in this article.
  • To avoid a mixed version of possible owner nodes in the failover cluster instance, use the Cluster Administrator tool (Cluadmin.exe), to remove nodes from the possible owner nodes list in the failover cluster instance. You should avoid having a mixed version of possible owner nodes to prevent possible data corruption.
  • In Microsoft SQL Server 2008 and later versions, the cluster service checks the SQL Network Name resource for a list of possible owners. The SQL Network Name resource is also checkpointed. This behavior differs from Microsoft SQL Server 2005. In SQL Server 2005, the SQL Server resource itself is checkpointed.

More information

Installing SQL Server rolling updates on a failover cluster

Note The information in this section applies to SQL Server 2008 and later versions.
  1. Before you start updates, make sure that you collect a list of possible owners for the specific SQL Server clustered instance. To find a specific SQL Server network resource name, run the following command:
    cluster.exe resource
    You see output that resembles the following:
    Resource                          Group                             Node              Status
    --------------------              --------------------              ----------        ------
    Cluster Disk 1                    Cluster Group                     NODE1             Online
    Cluster Disk 2                    SQL Server (INST1)                NODE1             Online
    Cluster Disk 3                    SQL Server (INST2)                NODE1             Online
    Cluster Disk 4                    Available Storage                 NODE1             Online
    Cluster IP Address                Cluster Group                     NODE1             Online
    Cluster Name                      Cluster Group                     NODE1             Online
    SQL IP Address 1 (SQLVS1)         SQL Server (INST1)                NODE1             Online
    SQL Network Name (SQLVS1)         SQL Server (INST1)                NODE1             Online
    SQL Server                        SQL Server (INST1)                NODE1             Online
    SQL Server Agent                  SQL Server (INST1)                NODE1             Online
    SQL IP Address 2 (SQLVS2)         SQL Server (INST2)                NODE1             Online
    SQL Network Name (SQLVS2)         SQL Server (INST2)                NODE1             Online
    SQL Server                        SQL Server (INST2)                NODE1             Online
    SQL Server Agent                  SQL Server (INST2)                NODE1             Online
    
    In this example, you can use the following commands to create a list of possible owners for each SQL Network Name resource:
    • cluster.exe resource “SQL Network Name (SQLVS1)” /listowners > c:\SQLVS1_list_of_owners.txt
    • cluster.exe resource “SQL Network Name (SQLVS2)” /listowners > c:\SQLVS2_list_of_owners.txt
  2. In the Cluster Administrator tool, use the corresponding SQL Network Name resource to remove half of the nodes from the possible owners list in the failover cluster instances on which you want to apply the updates. Remove the passive nodes first from the possible owners. Keep the list of nodes that you remove from the possible owners for future reference during this update process.

    Note We recommend that you remove half of the nodes from the possible owners to maintain high availability.
  3. After you have removed the nodes from the possible owners list in the failover cluster instance, apply the SQL Server update on the nodes that you removed in step 2. To apply the update on each node that has been removed from the possible owners, refer to the documentation that is supplied within the update package that you are applying.

    Note If this is the first time that you are applying the update on an instance for a node, move any active resource groups to another node before you apply the update. This helps avoid downtime or a restart operation that may occur when shared components that are being used are updated.
  4. After the update has been applied on the nodes that were removed from the possible owners, use the SQL Network Name resource on the Cluster Administrator tool to add the updated nodes back to the possible owners list in the failover cluster instance.

    To make sure that you will add back nodes for the correct SQL Server instances, use the list of possible owners that you collected before you performed the update. Make sure that you mark nodes that were already updated in your list.
  5. Based on the SQL Network Name resource, find the correct resource group, application, or services in Windows Server 2008, and then move that group, application, or service to one of the nodes that was updated.
  6. Verify that all SQL Server resources are online on the currently active node.
  7. In the Cluster Administrator tool, use the SQL Network Name resource to remove the nodes that were not updated from the possible owners in the failover cluster instance.
  8. Apply the SQL Server update to the nodes that you removed in step 7.
  9. Repeat step 4 to add all the nodes that were updated back to the possible owners for the failover cluster instance that you updated.
  10. Verify that the cluster is running without any issues. Try to move the instance of SQL Server between nodes in the cluster.

Uninstalling SQL Server patches on a failover cluster in a rolling update scenario

Note The information in this section applies to SQL Server 2008 and later versions.

Use the procedure that is applicable to your situation:

Scenario 1: You applied the patch on all the nodes of your cluster

In this scenario, the procedure to uninstall the patch is the exact opposite of installing the patch, that is discussed in the above section. The detailed procedure would be as follows:
  1. Before you remove updates, make sure that you collect a list of possible owners for the specific SQL Server clustered instance. To find a specific SQL Server network resource name, run the following command:
    cluster.exe resource 
    You see output that resembles the following:
    Resource Group Node Status -------------------- -------------------- ---------- ------
    Cluster Disk 1 Cluster Group NODE1 Online Cluster Disk 2 SQL Server (INST1) NODE1 Online
    Cluster Disk 3 SQL Server (INST2) NODE1 Online Cluster Disk 4 Available Storage NODE1 Online
    Cluster IP Address Cluster Group NODE1 Online Cluster Name Cluster Group NODE1 Online SQL IP Address 1 (SQLVS1)
    SQL Server (INST1) NODE1 Online SQL Network Name (SQLVS1) SQL Server (INST1) NODE1 Online SQL Server SQL Server
    (INST1) NODE1 Online SQL Server Agent SQL Server (INST1) NODE1 Online SQL IP Address 2 (SQLVS2) SQL Server (INST2) NODE1 Online
    SQL Network Name (SQLVS2) SQL Server (INST2) NODE1 Online SQL Server SQL Server (INST2) NODE1 Online
    SQL Server Agent SQL Server (INST2) NODE1 Online

    In this example, you can use the following commands to create a list of possible owners for each SQL Network Name resource:
    • cluster.exe resource “SQL Network Name (SQLVS1)” /listowners > c:\SQLVS1_list_of_owners.txt 
    • cluster.exe resource “SQL Network Name (SQLVS2)” /listowners > c:\SQLVS2_list_of_owners.txt 
  2. In the Cluster Administrator tool, use the corresponding SQL Network Name resource to remove half of the nodes from the possible owners list in the failover cluster instances on which you want to remove the updates. Remove the passive nodes first from the possible owners. Keep the list of nodes that you remove from the possible owners for future reference during this update removal process.
    Note We recommend that you remove half of the nodes from the possible owners to maintain high availability.
  3. After you have removed the nodes from the possible owners list in the failover cluster instance, remove the SQL Server update on the nodes that you removed in step 2. To remove the update on each node that has been removed from the possible owners, refer to the documentation that is supplied within the update package that you are applying.

    Note If this is the first time that you are removing the update on an instance for a node, move any active resource groups to another node before you remove the update. This helps avoid downtime or a restart operation that may occur when shared components that are being used are updated.
  4. After the update has been removed on the nodes that were removed from the possible owners, use the SQL Network Name resource on the Cluster Administrator tool to add the downgraded nodes back to the possible owners list in the failover cluster instance.

    To make sure that you will add back nodes for the correct SQL Server instances, use the list of possible owners that you collected before you performed the update. Make sure that you mark nodes that were already downgraded in your list.
  5. Based on the SQL Network Name resource, find the correct resource group, application, or services in Windows Server 2008, and then move that group, application, or service to one of the nodes that was downgraded.
  6. Verify that all SQL Server resources are online on the currently active node.
  7. In the Cluster Administrator tool, use the SQL Network Name resource to remove the nodes that were not downgraded from the possible owners in the failover cluster instance.
  8. Remove the SQL Server update to the nodes that you removed in step 7.
  9. Repeat step 4 to add all the nodes that were downgraded back to the possible owners for the failover cluster instance that you downgraded.
  10. Verify that the cluster is running without any issues. Try to move the instance of SQL Server between nodes in the cluster.

Scenario 2: You are in the middle of rolling update process and only some nodes are updated.


There could be various permutations for this as discussed below:

Note The following discussion assumes that you are following the procedure discussed in the "Installing SQL Server rolling updates on a failover cluster" section of this article and you are in the middle of updating either the first half or second half of the nodes.

Scenarios for first half - (the first half of the nodes are removed from possible owners)

Case 1A: You decide not to proceed further with patch update process.
Use the following procedure:
  1. Remove the update on each node that has been patched and using SQL Network Name resource on the Cluster Administrator tool to add them back to the possible owners list in the failover cluster instance.
  2. Verify that the cluster is running without any issues. Try to move the instance of SQL Server between nodes in the cluster.

Case 2A. After applying the patch on few nodes, you discover (either by searching the web or when working with Microsoft CSS) that there are known issues with the patch and hence cannot proceed further.
  1. If there is a newer version of the update that fixes the issue, use that in conjunction with Steps 3 to 10 of the procedure documented in "Installing SQL Server 2008 rolling updates on a failover cluster" section of this article to proceed with the update process.
  2. If either newer version is unavailable or you cannot upgrade to the newer version immediately, use the procedure that is documented for Case 1A above.

Case 3A. You are not able to apply the patch on one or more nodes:

In this case the recommendation would be to further troubleshoot why the update is failing on that node and continue patching the other nodes with the update. When adding the nodes back as possible owners to SQL Server resource, leave the problematic node or nodes out of that list.

If troubleshooting is not an option and you need all your nodes to be available for your cluster operation, you can use the procedure that is documented for Case 1A above.

Scenarios for the second half: (the first half of the nodes are already updated and added back to the possible owners list and you are either in the process of removing or had already removed the second half from the list of possible owners):

Case 1B: You notice that you are not able to move the SQL Server group to the updated nodes(Step #5 of the update process)due to a possible issue with the patch (SQL server group essentially fails back to a node that has not been updated).

Use the following procedure:

  1. Check if a newer version of patch that fixes the issue is available. If it is available, use that in conjunction with the procedure documented in "Installing SQL Server 2008 rolling updates on a failover cluster" section of this article to proceed with the whole update process.

    Note You do not need to remove the regressed patch from the first half. You just need to remove the first half of the nodes that are updated, from the possible owner list for the SQL Server instance and then proceed as if you are updating the whole cluster with a new patch using the rolling update process discussed at the beginning of this article.
  2. If either newer version is unavailable or you cannot upgrade to the newer version immediately, use the following procedure:
    1. Using the list that you collected at the beginning of the update process, remove the first half of the nodes that are patched as possible owners for the SQL Server network name.
    2. Add the second half of “removed” nodes back to the possible owners of the SQL Server network name.
      Note The failover failed, but you might have removed some or all the nodes on the second half. Also notice that none of the nodes on the second half had been updated yet.
    3. After the update has been removed, use the SQL Network Name resource on the Cluster Administrator tool to add these nodes back to the possible owners list in the failover cluster instance.
    4. Verify that the cluster is running without any issues. Try to move the instance of SQL Server between nodes in the cluster.
Case 2B: After applying the patch on few nodes, you discover (either by searching the web or when working with Microsoft CSS) that there are known issues with the patch and hence cannot proceed further.(SQL Server group is online on one of the first half nodes that have been updated).

Use the following procedure:
  1. Check if a newer version of patch that fixes the issue is available. If it is available, use that in conjunction with the procedure documented in "Installing SQL Server rolling updates on a failover cluster" section of this article to proceed with the whole update process, but starting with the complete second half in your list.

    Note Essentially, when the update process is complete, all the nodes from the first half as well as those nodes that are updated in the second half of your list will get two sets of updates - the first with the problematic patch and the second with a corrected patch.
  2. If either newer version is unavailable or you cannot upgrade to the newer version immediately, use the following procedure:
    1. Remove the patch on each of the nodes that are updated in the second half by referring to the documentation that is supplied within the update package that you are applying.
    2. Use the SQL Network Name resource on the Cluster Administrator tool to add the second half of nodes back to the possible owners list in the failover cluster instance.

      To make sure that you will add back nodes for the correct SQL Server instances, use the list of possible owners that you collected before you performed the update. Make sure that you mark nodes that were already updated in your list.
    3. Based on the SQL Network Name resource, find the correct resource group, application, or services in Windows Server 2008, and then move that group, application, or service to one of the nodes from the second half.
    4. Using the list that you collected at the beginning of the update process, remove the first half of the nodes that are patched as possible owners for the SQL Server network name.
    5. Remove the update on each of these nodes by referring to the documentation that is supplied within the update package that you are applying.
    6. After the update has been removed, use the SQL Network Name resource on the Cluster Administrator tool to add these nodes back to the possible owners list in the failover cluster instance.
    7. Verify that the cluster is running without any issues. Try to move the instance of SQL Server between nodes in the cluster.
Case 3B: You decide not to proceed further with patch update process: Use the following procedure.
  1. Remove the patch on each of the nodes that may have already been updated in the second half by referring to the documentation that is supplied within the update package that you are applying.
  2. Use the SQL Network Name resource on the Cluster Administrator tool to add the second half of nodes back to the possible owners list in the failover cluster instance.
  3. To make sure that you will add back nodes for the correct SQL Server instances, use the list of possible owners that you collected before you performed the update. Make sure that you mark nodes that were already updated in your list.
  4. Based on the SQL Network Name resource, find the correct resource group, application, or services in Windows Server 2008, and then move that group, application, or service to one of the nodes from the second half.
  5. Using the list that you collected at the beginning of the update process, remove the first half of the nodes that are patched as possible owners for the SQL Server network name.
  6. Remove the update on each of these nodes by referring to the documentation that is supplied within the update package that you are applying.
  7. After the update has been removed, use the SQL Network Name resource on the Cluster Administrator tool to add these nodes back to the possible owners list in the failover cluster instance.
  8. Verify that the cluster is running without any issues. Try to move the instance of SQL Server between nodes in the cluster.

Applies to
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2012 Business Intelligence
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
Keywords: 
kbexpertiseadvanced kbinfo KB958734
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support