LLI Wiki — My Digital Knowledge Base

SQL Server 2005: un-cluster an instance or retire a cluster node

Convert a clustered SQL Server 2005 instance to standalone, retire or replace a node, and uninstall after the Windows cluster was broken.

Procedures to un-cluster a SQL Server 2005 failover cluster instance: convert it to a standalone instance, retire a node, or replace old nodes. These steps apply to SQL Server 2005 only, not to SQL Server 2008 and later.

Case 1: convert the cluster to a standalone instance

The only supported method is to uninstall the clustered instance and install a standalone instance with the same instance name, IP address and network name.

  1. Record the instance details: architecture (32/64-bit), edition (SELECT SERVERPROPERTY('Edition')), installed features (engine, Reporting Services, Analysis Services...), data and log file locations of all databases, instance name (SELECT @@SERVERNAME), version/build (SELECT SERVERPROPERTY('ProductVersion')), network name and virtual IP address. Script out all logins (Microsoft KB 918992 on transferring logins) and save the output on a local drive.
  2. Copy the user and system databases (MDF, NDF, LDF) and the FTDATA folder (full-text catalogs) to a local backup directory.
  3. Uninstall the clustered instance from Add/Remove Programs (on the active or passive node).
  4. When the uninstall succeeds, remove the cluster service from all nodes at Windows level (see KB 282227).
  5. On the node that will host the standalone instance, assume the IP address and computer name of the old SQL virtual server, then reboot.
  6. Install a standalone instance of the same architecture and edition, and patch it to the same build as the old instance. If the clustered instance was named, create a named instance with the same name so clients can still connect.
  7. Stop SQL Server and copy the freshly installed system databases to a backup location.
  8. Replace the contents of the DATA folder with the backup copy of the old system and user databases, and replace FTDATA with the old one. Edition and architecture must match: never replace Standard edition system databases with Enterprise ones, or the reverse.
  9. Start SQL Server.
  10. If the install path differs from the old one, fix the path of the MSDB subsystem DLLs (see KB 914171), or jobs may fail.
  11. Check that SELECT @@VERSION and SELECT SERVERPROPERTY('SERVERNAME') return the old virtual server name (or the new computer name, which is the same if the steps were followed).
  12. Verify logins, jobs, schedules, linked servers, user databases and permissions.

Case 2: retire a node from the cluster

The simplest way is to evict the node from the Windows cluster, but SQL Server can no longer be uninstalled cleanly from the evicted node: you must clean up the installation or reformat the OS.

To clean up SQL Server before evicting:

  1. Make sure all resources in the SQL group are online.
  2. Remove the node from the clustered SQL instance (Microsoft documentation: How to: Remove a node from a SQL Server 2005 failover cluster instance).
  3. Then remove the cluster service from the node at Windows level (section "To remove the cluster service from a failover node" of KB 282227).

Case 3: replace old nodes with new ones

  1. Apply steps 1 to 3 of case 2 to each node being retired.
  2. Add the new node to the Windows cluster.
  3. Add the new node to the clustered SQL instance (same documentation as above).
  4. Test a failover to the new node.

If the Windows cluster was broken before the SQL instance

Clustered SQL Server looks for its IP address and network name dependencies at startup, so it will no longer start. To uninstall it:

  1. In the registry, change the SqlCluster value under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\Setup\ from 1 to 0.
  2. Uninstall SQL Server from Add/Remove Programs.
  3. Reboot the server.
  4. Reinstall SQL Server as in case 1 and attach the databases.

Notes

Mastodon LLIMastodon SocialFosstodon