How to un-cluster SQL Server 2005 Cluster
(How to uninstall SQL Server 2005 Cluster / How to break SQL Server 2005 Cluster )
There will be situations where you want to perform any of below task:
1. Convert two node SQL cluster to standalone instance of SQL
2. Retire one node of cluster or convert two node SQL cluster to single node SQL cluster
3. Retire old nodes and add new nodes to replace them
If you are a full time SQL DBA, you might know how to do this but for those who are not full time DBA’s might need a handy article with compilation of different methods that are available in multiple locations. This blog post is meant to be a one-stop guide for them. Please note that steps discussed here are not applicable for SQL Server 2008 and above. So let’s take a look at solutions one-by-one to perform above tasks.
Solution for Task 1:
The only supported method is to uninstall clustered SQL Instance and install Standalone instance with same instance name, IP Address, Network name. Here are the steps:
1.0 Note down below details of the existing SQL clustered instance:
è SQL Server architecture (32-bit or 64-bit)
è SQL Server edition (SELECT SERVERPROPERTY('Edition'))
è Features installed (Database Service, Reporting Services, Analysis Services…)
è Data and log directory location of all system and user databases
è SQL instance name (SELECT @@SERVERNAME)
è SQL build version (SELECT SERVERPROPERTY('ProductVersion'))
è Script out all logins from clustered SQL instance by following https://support.microsoft.com/kb/282227 and save the output to a local backup drive location.
è SQL Server network name and virtual IP address details.
1.1 Take a copy of user and system databases (MDF’s, NDF’s & LDF’s) and also copy of FTDATA directory which contains the Full text catalogs to a local backup directory.
1.2 Uninstall Clustered SQL Server instance in Active or Passive node from Add/Remove Programs or Programs and Features.
1.3 Once uninstallation completes successfully, un-cluster all participating nodes at Windows level. Refer: https://support.microsoft.com/kb/282227 for detailed steps.
1.4 Change IP Address of node which will host the standalone SQL instance to old SQL Server Virtual IP Address
1.4 Change Computer Name of node which will host the standalone SQL instance to old SQL Server Virtual Server Name.
1.5 Reboot the Server.
1.6 Install standalone instance of same architecture +edition and upgrade to same build version of old clustered SQL Server instance. If your clustered instance was a named instance then try to make this new standalone instance also as a named instance(with instance same) so that you will not have connectivity issues from clients.
1.7 Stop SQL Server.
1.8 Take a copy of the fresh system databases installed to a backup location.
1.9 Replace the contents of new DATA folder with the backup copy of old clustered system databases and user databases. Replace the FTDATA folder with the backup copy of old clustered instance. It is very important to note that Edition and architecture of new standalone installation should be the same as old clustered installation. Please do not replace Standard SQL instance system databases with Enterprise edition clustered system databases or vice-versa. I’m not covering the edition and architecture change migration in this blog.
1.10 Start SQL Server and it should start perfectly.
1.11 Follow https://support.microsoft.com/kb/914171 and change the path of MSDB subsystem dll’s else jobs may fail to run if the path of new installation is different from old one.
1.12 Make sure that SELECT @@VERSION and SELECT SERVERPROPERTY('SERVERNAME') return the old SQL virtual server name or the new Computer name (Both should be same if you exactly followed above steps)
1.13 Done. Standalone instance is back online. You can verify logins, jobs, schedules, linked servers, user databases, and security permissions etc. to confirm everything is fine.
Solution for Task 2:
Simple solution for this task is to evict the node you are going to retire so that it will no longer be part of Windows cluster. The problem is you will not be able to uninstall SQL Server from the Evicted Node later, so the only option later is to cleanup SQL install or format the OS.
If you want to cleanup SQL Server before evicting the retiring node:
2.1 Make sure all resources in the SQL group are online.
2.2 Follow https://msdn.microsoft.com/en-us/library/ms191545(SQL.90).aspx and remove the node which will be retired from SQL Cluster.
2.3 Follow the section “To remove the cluster service from a failover node” to uncluster the node which is going to retire https://support.microsoft.com/kb/282227 from Windows cluster.
Solution for Task 3:
3.1 Follow steps from 2.1 till 2.3 for all nodes which are going to retire.
3.2 Make sure that new node is added as part of windows cluster. Refer https://technet.microsoft.com/en-us/library/cc781276(WS.10).aspx for steps.
3.3 Follow https://msdn.microsoft.com/en-us/library/ms191545(SQL.90).aspx to add new node to SQL Cluster
3.4 Test failover to new node
What to if windows cluster is broken before breaking SQL Cluster
If you break cluster at windows level before uninstalling SQL Server, SQL Server will no longer work because clustered SQL Server will look for IP Address / Network name dependency during startup. We recommend you to follow the steps given below to uninstall SQL Server and then you can refer to the “Solution for Task 1” given above to re-install SQL Server:
Steps:
4.1 Change registry key SqlCluster in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\Setup\ from 1 to 0
4.2 Uninstall SQL Server from Control Panel -> Add/Remove Programs
4.3 Reboot the server.
4.4 Refer Solution in Task 1 to re-install SQL Server and attach the databases to new instance.
Sakthivel Chidambaram
SE, Microsoft SQL server
Reviewed by
Rakesh Singh & Sudarshan Narasimhan
Technical Lead, Microsoft SQL Server.
Comments
Anonymous
July 20, 2010
very nice article AmruthaVarshinij, but Have you done it before? I am actually planning to do it soon. we should retire the cluster and convert to standalone regular SQL servers. Does this apply for standard edition?Anonymous
July 20, 2010
Hi Ali , this article was actually penned by Sakthivel . He will reply to your query soonAnonymous
July 22, 2010
great help on if windows cluster is broken first -- I could really use 4.7 getting tcpip working again though.Anonymous
December 08, 2010
Hi, I have a SQL 2005 cluster with two nodes in active/passive mode. I want to remove one server from the cluster and use it for other purpose. What is the best way to do it without affecting the configuration. I think this situation is part of Task2 above. However, I want to use the removed server to load SQL 2008 for other purpose. Just want to know, if I just shutdown the node2 (passive) and remove the physical server without making any changes to the sql cluster configuration, will it affect the functionality of my SQL cluster. Thanks, Ravi KumarAnonymous
December 08, 2010
Hi, I have two node SQL cluster in active/passive mode. I want to remove the passive node server from the cluster and use it for SQL 2008 R2 installation. I see this situation is related to Task2 above. If I just shutdown my passive node server and pull the physical server without making any changes to SQL cluster configuration, will it affect the functionality of my SQL installation which will be just with one node now. Thanks, RaviAnonymous
July 31, 2011
Hi great article, but I miss one thing - how to enable TCPIP protocol in a last case, is it possible. You say that when cluster is broken only connections via Pipes are available.Anonymous
October 10, 2014
The comment has been removedAnonymous
February 09, 2015
Thanks a lot, the one and only good SQL unclustering manual in the internet. Do you have the same for SQL 2008/2008R2?