How to remove and replace MS SQL 2016 cluster nodes?

Question

Hi!

I have a 2 node active/passive MS SQL 2016 failover cluster on Windows Server 2016 Standard. The SQL Server is also Standard Edition. I need to replace the physical servers hosting the SQLs due to lifecycle management but still want to keep the SQL instances.

Since the SQL Server is Standard Edition I can't add a third and fourth node to the existing cluster.

As far as I understand it I remove the passive SQL Server from the cluster by running the SQL Server installer and using the maintenance option to remove it and then add one of the new servers by running the SQL Server installer and chosing add a node.

I have added the failover clustering feature to both of the new physical servers but not yet added them to the cluster.

My question is if I should add the new servers to the actual failover cluster without any SQL components installed and then run the SQL Server installer and chose add a node to existing cluster when the failover cluster is set up?

Or do the SQL Server add node option also add the node to the Windows server to the failover cluster as well?

BR

Peter

Answer 1

Windows Server Failover Clustering (WSFC) is different from SQL Server failover clustered instance. SQL Server runs on top of the WSFC for it to be highly available. This means you can have an extra node in a WSFC but not have SQL Server running on top of it. This also means failover of the SQL Server instance is not possible on this node even though it is part of the WSFC.

if I should add the new servers to the actual failover cluster without any SQL components installed and then run the SQL Server installer and chose add a node to existing cluster when the failover cluster is set up?

Yes. You need a maintenance window, just in case something goes wrong.

First, add the servers as a nodes in the WSFC. Next, remove the old node from the SQL Server failover clustered instance using the maintenance option to remove it. Then, add the new node to the SQL Server failover clustered instance.

Repeat until you removed all of the old nodes from the SQL Server failover clustered instance. This will require failing over to the new node.

You can end up having a 4-node WSFC with a combination of the old hardware and new hardware, with SQL Server running only on the 2 new nodes. Once you're happy with it, evict the old servers from the WSFC.

Treat this as a project. Get a project manager involved to coordinate everything between the different teams.

Answer 2

Hi

you need to add the Server to the Failover Cluster first, then during the SQL install the underlying machine is detected as a Cluster Node and is added to the roles on the existing Cluster. Follow the steps in these articles, Article 4 shows how to add a server to an existing Cluster:

https://www.mssqltips.com/sqlservertip/4769/stepbystep-installation-of-sql-server-2016-on-a-windows-server-2016-failover-cluster-part-1/

https://www.mssqltips.com/sqlservertip/4797/stepbystep-installation-of-sql-server-2016-on-a-windows-server-2016-failover-cluster-part-2/

https://www.mssqltips.com/sqlservertip/4813/stepbystep-installation-of-sql-server-2016-on-a-windows-server-2016-failover-cluster-part-3/

https://www.mssqltips.com/sqlservertip/4840/stepbystep-installation-of-sql-server-2016-on-a-windows-server-2016-failover-cluster-part-4/

Hope this helps,

Thanks

Michael Durkan

• If the reply was helpful please upvote and/or accept as answer as this helps others in the community with similar questions. Thanks!

Answer 3

Hi @peter A ,

Agree with Michael Durkan. You need to add the Server to the Failover Cluster first. Alwayson is based on Windows clusters.

Please also refer to this link:

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/install/add-or-remove-nodes-in-a-sql-server-failover-cluster-setup?view=sql-server-ver16

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 4

Hi all!

I'm familiar with the actual SQL-cluster setup and config, but I couldnt remember if I had to add the new nodes to the failover cluster first.

Anyway, the hardware replacement is now complete.

Worked perfectly fine as you all have answared to:

1. Add the new nodes to the existing failover cluster.
2. Remove the passive node from the SQL-cluster by running the SQL setup on the passive node choosing the maintenance tab and remove node from cluster. (also verified this by select * from sys.dm_os_cluster_nodes )
3. Adding one of the new nodes to the SQL-cluster by running SQL setup and add node to existing cluster.
4. Failover to the new node.
5. Repeat 2 and 3.
6. Evicting the old nodes that no longer had any resources.

Everything works fine.

Thanks for answers so far.

One last question, since I went from Server 2016 to 2019 on the OS (still kept the same SQL version) i guess that I should run Update-ClusterFunctionalLevel since the old nodes are gone. Just want to make sure that this is a non-disruptive command or will it cause failovers when running?

BR

Peter

Answer 5

double entry