I recently had the joy of hearing about a security issue on one of our SQL Server Failover Cluster instances, in an attempt to resolve the problem, someone inadvertently deleted the clustered SQL service from Failover Cluster Manager. Why? I have no idea—but I set about trying to figure out what to do to recover from it. Note—all of these screen shots are from Windows Server 2012 and SQL Server 2012, but the same technique applies in 2008R2—I don’t have a 2003 Cluster to test on, and I think the procedure may be a little more manual there (you may have to make a registry entry).
So how do we delete the service?—easy, right click on it and select remove.
Windows will try to warn us, but for the purposes of science we will continue:
After we click yes, the cluster deletes the service, and SQL Server goes to sad panda mode. This happens because the SQL Server was dependent on our Virtual Server Name, which disappeared when we deleted the service. I’m not sure why the Agent looks like it’s still running—it to is down.
A quick look over at our Domain Controller, shows that our cluster object (SQL2012CL1) is still in AD, so no recovery is needed there—so we don’t need to recover it. However, our DNS entry is gone.
So where do we start the repair process? Select Roles in Failover Cluster Manager, and then select Configure Role
Select the role “Other Server”
Set your Client Access Point—use the same name and IPAddress as your previous service.
Select your applicable disks—this is my demo instance, so I only have one cluster LUN.
Here’s where it starts to get good. We select the SQL Server and SQL Server Agent resource types.
It looks like we may be done—we aren’t.
This is where we need to set our dependencies and properties. So first right click on “New SQL Server” and select Properties
SQL is going to be dependent on the network name, the IP address, and the Disk that our cluster uses all being online.
So we add those—next go to properties in that same window. We need to set VirtualServerName and InstanceName to their correct values.
Lastly, I set the service name to the right value, but I don’t think it matters.
We need to do a similar process for the SQL Agent. It is only dependent on the SQL Server service.
We also need to define the parameters for it.
Then we can try to start everything up and yes, it’s all green!!
Hopefully this never happens to you, and it shouldn’t but in the event that it does, you now know what to do.