Friday, October 07, 2011

Adding a Node to a SQL Server 2005 Failover Cluster

Setting up multi-node clustered SQL instances from scratch is common practice for companies that want to implement high availability, but adding a new node to an existing clustered SQL instance that has been running for a while may have a few sticking points, especially for a SQL 2005 instance. In this series, I will walk through how to add new node for existing clustered SQL Server instance for SQL 2005 environments, and applying a post-configuration service pack.

Step by step procedure for adding a node

Below, I detail step by step instructions for how to add a node and then apply a service pack or hot fix afterward.
Step 1: Log onto the primary node for the existing SQL 2005 Clustered instance. The primary node is the clustered node where your SQL 2005 clustered instance is currently running.

Step 2: It's always advisable to run the setup procedure by a service account with administrative privilege on all the clustered nodes. To launch Add or Remove Programs in Control Panel with a service account, follow these steps:

1.       Navigate down to C:\WINDOWS\system32
2.       Look for appwiz.cpl

3.       Right click on appwiz.cpl, select click RunAs, then specify service account "User Name" and "Password" and click OK

Step 3: Once you have seen the Add or Remove Program window below, it means you have successfully launch it with the designated domain account.

Step 4:  If your OS is 64 bit, look for "Microsoft SQL Server 2004 (64-bit)," and then click Change. The wizard for "Microsoft SQL Server 2005 Maintenance" will start as below (I have masked the instance name for Reporting Services and Database Engine for confidentiality of the company) In your environment, you should expect to see the instance name for the masked portion. You can also see common components that were already installed on the server.

Step 5: If you have multiple SQL 2005 instances then you will need to perform one instance at a time. Here I only have one SQL 2005 clustered instance, so I selected the instance by clicking the radio button and then clicking Next.

Step 6: In the Feature Maintenance page, select a component to change; Database Engine (Clustered) is selected by default. Click Next, then the SQL Server 2005 System Configuration Checker starts.

After few seconds, the Welcome page for "Microsoft SQL Server installation Wizard" will pop up, as below.

Step 7: Simply click Next to proceed

Step 8: Installation wizard will perform system check and make sure it can proceed without Error or critical warning.  Review all the warning and error message provided by System Configuration Checker. Address any threatening warning and error if needed, then re-launch from Step 2.
Click Next once system configuration check is completed.

Step 9:  in the Change or Remove Instance page, select Maintain the Virtual Server.

Step  10: On the Cluster Node Configuration page. Here is where you will see the ActiveNode, PassiveNode and the new node you'd like to add. Since the installation wizard needs to be launched from the active node for the clustered instance, you will see the machine you logged onto appears on "Required node." For the existing passive node for the clustered instance, the server name will appear on "Selected nodes," and the new node you are working on to add into the clustered instance will appear on "Available nodes."
Simply click Add to move the available node to selected nodes. You can now see both Passive node and the new node being listed under "Selected nodes," like the below screenshot.

Click Next to proceed.

Step  11: Remote Account Information page shows up. Here you just need to provide the password for the domain account you used to launch the Setup wizard from step 2. Make sure the login you used to launch the wizard has administrative permission on all the three clustered nodes for the instance you are operating. Then click Next to proceed.

Step 12: Here you will be asked to provide the password for the existing service account used to run SQL Server service, SQL Agent service and Browser service.  Once you fill in the correct password information, click Next.

Step 13: On the Error and User Report Settings page, depending on your company's policy, select either or not to send error reports and usage data to Microsoft, then click Next.

Step 14: Ready to Update page shows up, click Install.

Step 15: Configuring Microsoft SQL Server page will show up, do not click anything, let it run through progress bar.

Step 16: Then a windows explorer navigation window will pop up; here you have to browse through your directory to help the installation wizard to locate SqlRun_SQL.msi. You can find this msi under SQL 2005 installation binary folder ...\disk1\Setup, depending on where you place the SQL 2005 install binary. You can also do a search on SqlRun_SQL.msi under the install binary to find its exact location.

Step 17: Once you successfully locate the SqlRun_SQL.msi, click OK to continue.

Step 18: Don't click on anything, Let the setup wizard do its duty.

Step 19: After a few minutes, you will receive the above warning message that indicates that existing nodes for the SQL 2005 clustered instances are already patched. So you will also need to patch the new node to match rest of the clustered nodes. Click OK to acknowledge.

Step 20: You will see this "Setup Progress" page once Setup Wizard is about to install components on the new node. Do not click anything and wait for its setup progress page.

Step 21: Finally, Setup Wizard has come down to the business of installing all necessary components on the new node. Please wait as it indicates on the page.

*If you are adding two new nodes instead of one new node, the setup wizard will provide a drop down so you can scroll the node and see the install progress on each node, like the below diagram.

The installation procedure to add two new clustered nodes and to add one new clustered node are exactly the same, except that you can scroll the drop-down list of server names to review the installation progress. Below, I will continue to use the screenshot I took for adding one new node to an existing clustered SQL 2005 instance.

Step 22: Keep your fingers crossed and hopefully you can see the Green Check Mark that indicates successful setup of the respective components; below three screenshots display successful progress of the installation.

Step 22: Once you see that all components have the status "setup finished," you can relax and click Next because Setup wizard has successfully install the selected SQL components on the new node.

Step 23: You can review the setup log by clicking the summary log, or clicking Finish to complete the procedure of adding a new node.

Step 24: It's a good practice to recycle your affected servers. Make sure the SQL 2005 clustered instance is back online and healthy after adding the new node. Do not failover the SQL instance to the newly added node just yet.

Author: Claire Hsu