Deleted the SQL Server Resource from SQL Cluster? Recover it….

There is a chance that any of the resources in the SQL Cluster is removed from the cluster by mistake. This will cause the cluster to go into a Partial Online or Failure status. In order to fix the issue you need to recreate the resources in the cluster.

Following shows what to do when the SQL Server resource “SQL Server (SQLINSTANCE01)” and SQL Server Agent resource “SQL Server Agent (SQLINSTANCE01)” have been deleted from your SQL Cluster.

  1. First, using Domain Controller take a look at your OU to make sure that the cluster object belonging to your SQL Cluster is still there (ESPDBUAT01). A quick look shows that our cluster object (ESPDBUAT01) is still in AD, so no recovery is needed there.
  2. As you can see, your SQL Instance is down.
  3. So you have to clean the role before you can anything, which means deleting the server associated with this role. So right click on Name: ESPDBUAT01 under Server Name and click Remove.
  4. Click Yes.
  5. Now you need to remove all disks from the cluster, so go to Storage, click on Disks, and highlight all the disks that are assigned to ESPDBUAT01 role, then click Remove from ESPDBUAT01.
  6. Click Yes.
  7. Make sure there is no other resources assigned to this cluster.
  8. Now you need to rename the old role to something else. Right click on the role’s name, and click Properties.
  9. Change the name of the role (i.e. add -Old to the end of it), and click OK.
  10. Now if you check the Domain Controller you’ll see that the cluster object is disabled. Now you can begin recreating the entire thing from scratch.
  11. So now we are ready to add the deleted resource. In order to do so, right click on Roles in Failover Cluster Manager, and then select Configure Role…
  12. Select the role Other Server, and then click Next.
  13. In Client Access Point page, add the same name and IP Address that had used to setup the cluster object. Once done click Next.
  14. Select all the disks belonging to this cluster, and then click Next.
  15. In the Select Resource Types page, select the SQL Server and SQL Server Agent resource types, and then click Next.
  16. Click Next.
  17. Click Finish, but we are not done yet!
  18. As you can see in the following screenshot, all disks are added and are online. However, the server and the two SQL services are offline. They need to be configured their dependencies and properties before bringing them online.
  19. Right click on New SQL Server and go to Properties.
  20. Go to Dependencies tab and add the network name (ESPDBUAT01), the IP address, and all the Disks that our cluster uses.
  21. Go to Properties tab and set VirtualServerName and InstanceName to their correct values.
  22. Go to General tab and change the Name to SQL Server (SQLINSTANCE01), and then click OK.
  23. Now we need to do a similar process for the SQL Server Agent. So right click on New SQL Server Agent, and go to Properties.
  24. Go to Properties tab and set VirtualServerName and InstanceName to their correct values. Note: The order of the rows in this tab (VirtualServername and InstanceName) might be different than the screenshot bellow. Make sure that you don’t type them in reverse. Also, any typo in this section causes the entire thing not to come online. So, double check your spelling!
  25. Click on Dependencies tab and add SQL Server (SQLINSTANCE01).
  26. Go to General tab and update the Name of the agent to SQL Server Agent (SQLINSTANCE01). Then click OK.
  27. Now you are ready to start the role. Right click on the role (ESPDBUAT01), and click on Start Role. If you have typed everything correctly the role will start without any issues.
  28. Ensure that the two services are Online and you can connect to the SQL cluster in SSMS.
  29. Check the Domain Controller and the cluster object ESPDBUAT01 is back online.
  30. Now you can clean up the Failover Cluster Manager. Right click on the old role ESPDBUAT01-Old and click Remove, and then Yes.

And you are done! 🙂

One comment on “Deleted the SQL Server Resource from SQL Cluster? Recover it….

  1. Rakesh Sharma

    Thanks you so much, nice article helped me in fixing my issue

Leave a reply