How to Remove Node from a SQL Server Failover Cluster

By: Manvendra Singh | Updated: 2016-05-03 | Comments (3) | Related: > Clustering

Problem

I need to remove one of the SQL Server cluster nodes from our failover cluster configuration. Can you please provide the step by step process to complete this?

Solution

A Windows failover cluster is a group of servers that work together to maintain high availability of applications. The basic unit of a cluster is a single computer, also called a "node". Now suppose you have to remove a node from your failover cluster configuration, what are the steps?

To do this, we will run SQL Server setup on the node to be removed from the SQL Server failover cluster. We will choose the "Maintenance" tab in the left-hand pane to proceed with the removal of a node from a SQL Server failover cluster. Follow the steps below.

Removing a node from SQL Server failover cluster

Step 1: I have a two node cluster based on the Windows Server 2012 R2 operating systems where we have SQL Server 2014 installed. We will check both nodes in SQL Server using DMV sys.dm_os_cluster_nodes, so that post node removal we can validate the node was actually removed by comparing the original results with the final results of this query.

Check nodes in SQL Server via the sys.dm_os_cluster_nodes DMV

We can see both nodes in the above screenshot, so our task is to remove SQL-NODE2 from this failover cluster configuration. To do this copy or mount the SQL Server setup on the server where we will perform the SQL Server node removal. Go to the setup.exe file location, right click on setup.exe and choose "Run as administrator" as shown in the below screenshot.

Step 2: The SQL Server Installation Center window will appear on your screen to proceed with the node removal. Now click on the "Maintenance" option from left pane as highlighted in the figure below.

SQL Server Installation Center in the Maintenance tab

Step 3: Once you click on "Maintenance" from the left pane, multiple options will be displayed in the right pane for this tab. Click on "Remove node from a SQL Server failover cluster" link in the right pane. Setup will ask you to wait with the below window displayed.

Remove node from a SQL Server failover cluster

Step 4: The Remove a Failover Cluster Node window will be displayed and it will check the Global Rules for this configuration as per the screenshot below. You can see all the mandatory rules have passed. Now click on the "Next" button to proceed.

Global Rules checking for removing a failover cluster node

Step 5: Once you click on the "Next" button, the "Remove Node Rules" step will be checked. All rules have passed, so we can proceed to the next as shown in the figure below. If any rule fails, it is recommended to fix those rules first then proceed with the node removal.

Step 6: As all rules in the above window have successfully passed, click on the "Next" button to proceed. The next window is the Cluster Node Configuration page. You do not need to do anything here. This window will automatically display the node configuration information like the SQL Server Instance name, SQL Server network name, node names, and features which have been installed for this cluster in the right pane. Just verify the information and then click on the "Next" button to proceed.

Step 7: The next window is the Cluster Network Configuration which will verify the network configuration of the failover cluster. The virtual IP address for SQL Server failover cluster will be listed in the "Address" column along with the subnet mask address and network name. You do not need to do anything here, just verify the details of this failover cluster and click the "Next" button.

Step 8: Once you click the "Next" button, a final window will appear to validate the steps that will be performed. Since we want to remove the node from the SQL Server failover cluster, click the "Remove" button as highlighted in the screenshot below.

Summary information on the Ready to Remove Node page

Step 9: When you click the Remove button, the node removal process will start and it will show a screenshot as shown below.

Once the node has been successfully removed from the SQL Server failover cluster, you will get this window noting the successful removal of all components and features as shown below. Click on the "Close" button to close the setup windows.

Validate SQL Server Cluster Node Removal

We have successfully removed this node from this failover cluster. We will run the same command as in step 1, so we can determine if SQL-NODE2 node has been removed from this SQL Server failover cluster.

Query the sys.dm_os_cluster_nodes to validate SQL-NODE2 has been removed

Above you can see there is only one node in the failover cluster which is SQL-NODE1. So we have validated that SQL-NODE2 node has been removed from this SQL Server failover cluster.

Next Steps

Explore more knowledge on SQL Server Database Administration Tips
Check out the remainder of the SQL Server Clustering Tips

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2016-05-03

Wednesday, April 10, 2024 - 2:56:32 PM - saroj sainju	Back To Top (92171)
Does it steps required in 2 nodes WFCS SQL alwaysOn High availability environment?

Tuesday, February 4, 2020 - 7:21:39 AM - Jach	Back To Top (84142)
"To do this copy or mount the SQL Server setup on the server where we will perform the SQL Server node removal." Please specify which server, as we most likely would want to remove a secondary but this can only be run on the current primary as the Cluster Service needs to be running.