Patching a Newly Added Node for an Existing SQL 2005 Clustered Instance

In my previous article “Adding Node for Existing SQL Server 2005 Failover Cluster“, I detailed the steps of how to add a new node for an existing SQL 2005 clustered instance that had been running for quite some time. This article continues the topic of how to apply a service pack and hotfix for the successfully added clustered nodes. It’s mandatory that all clustered nodes should be in synch in terms of version update.

Pre-requisite: Checking Version Number and Download the Service Pack and Hotfix

First, we need to check the version of the Clustered SQL instance. You can quickly check the version of the clustered instance by simply connecting the SQL instance through SSMS.

Check the version of the clustered instance

Check the version of the clustered instance

Version 9.0.3282 means SQL Server 2005 SP2 with CU9. In this case, we will need to patch the newly added node with SQL Server 2005 SP2 and then follow with the patching of cumulative update 9. You can download the service pack and cumulative update from Microsoft website.

Step 1: Failover the SQL Clustered Instance to the Newly Added Node.

Remote desktop onto the newly added node for the clustered SQL instance, open Cluster Administrator, then right click on the SQL Cluster Group, select Move Group, click on the newly added node under “Best Possible”

Select Move Group

Select Move Group

Click on the newly added node under "Best Possible"

Click on the newly added node under “Best Possible”

Step 2: Pausing Old Clustered Nodes

Now the SQL Clustered instance is running on the newly added node. We can proceed with pausing the old nodes.

In this case, I have three clustered nodes for the SQL instance, two old nodes, say serverA and ServerB and one new node, say ServerC. Right click on ServerA and ServerB, select “Pause Node”. This step is very crucial, since you do not want to apply service pack and hotfixes onto the nodes that have been patched previously.

 

Select "Pause Node"

Select “Pause Node”

After you pause the old nodes, you will see something similar to the picture below. Again, to protect the privacy of the company, I need to mask the server name. You will see a warning sign on the servers you paused. We will not pause the newly added node, since we need to apply service pack and hotfix on the new node.

Pause the old nodes

Pause the old nodes

Step 3: Launch SQL Server 2005 Service Pack 2 Executable

Right click on the SQL Server 2005 Service pack 2 executable, run the process with a domain login with administrative permission on the nodes that you are about to patch.

Run the process with a domain login with administrative permission

Run the process with a domain login with administrative permission

Microsoft SQL Server 2005 Service Pack 2 Setup Welcome page

Microsoft SQL Server 2005 Service Pack 2 Setup Welcome page

The welcome page shows up and depicts the purpose of the executable, click Next to proceed.

Microsoft SQL Server 2005 Service Pack License Terms

Microsoft SQL Server 2005 Service Pack License Terms

Standard License Terms agreement, Click “I accept the agreement” then click Next.

Feature Selection page

Feature Selection page

Feature Selection page –> The SQL instance name will be displayed on the masked portion. In this case, it’s a straightforward upgrade for database engine and SQL components. I would recommend updating all pre-selected components. Click Next.

Windows Authentication

Windows Authentication

Authentication page –> you can choose to patch service pack by Windows Authentication or SQL Authentication. The default is Windows Authentication, the user name will default to the login used to launch the service pack 2 executable, you can test the connection of the login by click Test. Click Next to proceed…

Remote User Account

Remote User Account

Remote User Account page –> you should provide the username, password and domain of a login that has administrative permission on all of the clustered nodes for the clustered SQL instance in case the patching is also being performed remotely on other nodes.

You may have two newly added nodes that require patching, the login information you provided in this page will be used to log onto the nodes to perform the patching procedure.

Error and Usage Reporting Settings

Error and Usage Reporting Settings

Error and Usage Reporting Settings page –> Depending on your company policy, you should decide whether or not to enable for both automatic reporting settings. Click Next to proceed.

Running Processes

Running Processes

Running Processes Page –> you will see the services running for the clustered instance you are about to patch; they lock some files needed for the service pack update. Click Next to proceed.

Ready to Install

Ready to Install

Ready to Install –> Click Install once you are ready with the service pack update.

Installation Progress

Installation Progress

Installation Progress page –> You can see the progress of service pack update patching on each component of the clustered instance.  You will see the Clustered instance name on the masked portion.

Depending on your environment, this step may take some time.

Reminder to Reboot

Reminder to Reboot

You will also receive a reminder for “Computer Reboot Required”. Click OK to complete the service pack update.

Step 4: Reboot Server

Reboot the server right away after patching the service pack to make sure all necessary changes are in effect on the new node.

Step 5: Launch SQL Server 2005 Cumulative Update Executable

  1. Download the cumulative update (CU) to be applied after service pack 2.
  2. Open Cluster administrator, make sure the SQL instance is running on the new node.
  3. Pause the old nodes.
  4. Right click on the CU executable and run the setup process with a domain login with administrative permission on all of the clustered nodes for the SQL instance you are about to patch.
  5. Let the wizard guide you through patching all necessary components for the clustered instance.
  6. Complete the patching.
  7. Reboot a new clustered node to ensure all necessary changes are in effect after the patching.

Step 6: Resume Paused Clustered Nodes

Once you have completed both service pack and hotfix update for the new nodes, you need to resume the paused node. Simply open the cluster administrator, right click on the paused node, then select “Resume Node”

Resume Node

Resume Node

Step 7: Test Cluster Failover

Now all of the nodes are online, you will need to test the SQL instance by failing it over to all the nodes. Open the cluster administrator, then right click on the SQL Clustered Group, select “Move Group”, pick the server name listed under “Best Possible”, test the failover one by one.

  Test the SQL instance by failing it over 

Test the SQL instance by failing it over

Open SSMS and connect to the SQL instance after each failover. You will want to see the same version number no matter which clustered node the SQL instance is running on. 

Check the version of the clustered instance

Open SSMS and connect ot the SQL instance

What’s the Take Away?

Pausing node is the simple but most vital step when patching new nodes on existing SQL instances. Without pausing the node, Service Pack update wizard will attempt to apply service pack on an already patched clustered node and hence fail the patching process.

Conclusion

This wraps up the 2 part series of adding and patching new nodes for an existing clustered SQL instance. I hope this will benefit readers that are thinking about increasing the scalability of SQL 2005 clustered instance and have a smooth process on setting up new a node.

See all articles by Claire Hsu

Claire Hsu
Claire Hsu
Claire Hsu has nearly 10 years' experience in database administration, architecture, engineering and development, ranging from SQL 7.0, 2000, 2005 and 2008 environments. Currently, she is a senior DBA for a global infrastructure team in an investment management firm located in New York City. She is certified in MCP, OCP 10G, MCTS and MCITP on SQL Server 2005, and has a master's in Electrical Engineering from University of Massachusetts, Amherst. Her focus as DBA includes project management, handling critical projects like server upgrades, migration, high availability features deployment, performance tuning, monitoring, ensuring business continuity, and client facing and engineering automated processes.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles