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.
    01-InitialDCCheck
  2. As you can see, your SQL Instance is down.
    02-SQLDown
  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.
    03
  4. Click Yes.
    04
  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.
    05-RemoveDisks
  6. Click Yes.
    06
    07-DiskAvailable
  7. Make sure there is no other resources assigned to this cluster.
    08-CleanNode
  8. Now you need to rename the old role to something else. Right click on the role’s name, and click Properties.
    09-RenameNode
  9. Change the name of the role (i.e. add -Old to the end of it), and click OK.
    10-RenameNode2
  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-NodeDownInDC
  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-ConfigureROle
  12. Select the role Other Server, and then click Next.
    13-OtherServer
  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-ClientAccessPoint
  14. Select all the disks belonging to this cluster, and then click Next.
    15-AddDisks
  15. In the Select Resource Types page, select the SQL Server and SQL Server Agent resource types, and then click Next.
    16-AddSQLResource
  16. Click Next.
    17-Confirm
  17. Click Finish, but we are not done yet!
    18-Finish
  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-NodePartialUp
  19. Right click on New SQL Server and go to Properties.
    20-NewSQLServer
  20. Go to Dependencies tab and add the network name (ESPDBUAT01), the IP address, and all the Disks that our cluster uses.
    21-Dependencies
  21. Go to Properties tab and set VirtualServerName and InstanceName to their correct values.
    22-Properties
  22. Go to General tab and change the Name to SQL Server (SQLINSTANCE01), and then click OK.
    23-General
  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-NewSQLServerAgent
  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-Properties
  25. Click on Dependencies tab and add SQL Server (SQLINSTANCE01).
    26-Dependecies
  26. Go to General tab and update the Name of the agent to SQL Server Agent (SQLINSTANCE01). Then click OK.
    27-General
  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-StartRole
  28. Ensure that the two services are Online and you can connect to the SQL cluster in SSMS.
    29-RoleOnline
    30-SQLUp
  29. Check the Domain Controller and the cluster object ESPDBUAT01 is back online.
    31-DCUp
  30. Now you can clean up the Failover Cluster Manager. Right click on the old role ESPDBUAT01-Old and click Remove, and then Yes.
    32-RemoveOldRole
    33-Confirm

And you are done! 🙂

Leave a reply