Recovering From a Deleted Cluster Resource

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.

About jdanton1
A DBA, cyclist, cook (who likes to play chef occasionally)

12 Responses to Recovering From a Deleted Cluster Resource

  1. Lin says:

    cool, recovered SQL 2008 R2 instance in 20 min following this article.

  2. jdanton1 says:

    Glad I could help–what lead to your resource being deleted? Just curious.

  3. matt says:

    hi
    I have a strange issue.
    While playing with a SQL 2008R2 test cluster environment to resolve a problem we had in production, I finished (don’t know how and don’t know why, I think deleting some resources or mapping/hiding some iSCSI LUNs) with a SQL instance perfectly running, but out of the cluster.
    I can see disks under available cluster storage, but I’m not able to re-add all the resources to create a new clustered service.
    If I try to add IP and Client Access resource, it tells me that the name and address are yet in use on the network, If I try to add storage (that I see as reserved into disk manager) it fails with strange unknown error or something like “cannot write on an existing file”…
    If I try to add a new Failover Cluster SQL node through setup, it finds the running instance but cannot add it.
    If i try to create new Failover Cluster SQL node, it fails saying the resource yet exists…

    The question is:
    Is it possible to cluster a yet running SQL instance (as I do, for example, with a Hyper-v virtual machine running on a local Hyper-v Manager)?

    Thanks

  4. jdanton1 says:

    Matt–

    I haven’t done it using Hyper-V storage. I recommend using Starwind software to configure your shared storage. Matt Velic has detailed the config here. http://mattvelic.com/virtual-lab-p5/

  5. thomas says:

    master, you’re a life saver.
    thank you so much.

  6. Magoo says:

    Very helpful article. Got a deleted SQL resource group back up and running in no time after I managed to accidently delete it. Luckily it wasn’t the production instance :-D

  7. Jr says:

    Hi All, I have a similar problem however I recreated the cluster after upgrading to windows 2012 r2 and I was running sql 2012 cluster before the inplace grade, now I am getting this error message.

    Error: 17182, Severity: 16, State: 1.
    2013-10-23 09:22:41.59 spid14s TDSSNIClient initialization failed with error 0x5, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Access is denied.
    any ideas ?

  8. Jr says:

    Hi, no luck with that attempt.

  9. jdanton1 says:

    Email me at jdantoni@anexinet.com and we can discuss further.

  10. Pingback: Don’t $%^&ing Delete Your SQL Server Cluster Resource | The SQL Herald

  11. Pooja says:

    Very useful article! recovered one in 10 mins…following this! :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,818 other followers

%d bloggers like this: