Fix SQL Server Agent on Windows Failover Cluster

Problem

I was trying to install SQL Server 2008 R2 on a Windows Server 2008 R2 Failover Cluster, but the setup did not complete successfully. Upon further investigation, the issue seems to be caused by a DNS entry that was then fixed by the systems administrator. While the SQL Server cluster resource group was successfully brought online after the DNS issue was fixed, I noticed that the SQL Server Agent was not listed as a resource type under the Other Resources section of the cluster resource group. How do I manually add the SQL Server Agent to the cluster resource group?

Solution

Not seeing the SQL Server Agent on the Other Resources section of the SQL Server cluster resource group means that it has not been created successfully. This is usually caused by an incomplete or corrupt installation when you perform a single-node cluster installation of SQL Server. In this particular case, the single-node cluster installation failed because the virtual server name could not be registered to the DNS. You can verify this by trying to add a new resource in the clustered resource group. In the screenshot below, you do not see the SQL Server Agent in the list of available resources - only the SQL Server resource is available.

While searching for a Microsoft KB article that would provide a resolution for this issue, I saw a problem similar to mine that was posted on the MSDN Forums.
We can manually add the SQL Server Agent as a clustered resource type. But before we do this, we need to make sure that the SQAGTRES.DLL file has already been copied to the C:\WINDOWS\SYSTEM32 folder. This DLL file gets copied as part of the failover cluster installation together with the main DLL used by the SQL Server database engine -SQSRVRES.DLL. While the functions of these two Resource DLLs are beyond the scope of this tip, you can learn more from this SQL-Server-Performance.com article.
To fix the SQL Server Agent resource on the clustered resource group, you need to perform the following steps below:
NOTE: These steps can be performed either via the Failover Cluster Manager, cluster.exe or Windows PowerShell. Since we all have different preferences, you decide which method to use. I am including all three options where applicable. When choosing to use Windows PowerShell, make sure you import the Windows PowerShell modules, in particular, the FailoverClusters module using the Import-Module cmdlet as defined here.

---

Manually add the SQL Server Agent resource type to the SQL Server cluster resource group

Step 1
Create the SQL Server Agent resource type

Using cluster.exe,

cluster.exe restype "SQL Server Agent" /create /DLL:SQAGTRES.DLL

Using Windows PowerShell,

Add-ClusterResourceType "SQL Server Agent" C:\Windows\system32\SQAGTRES.DLL

Step 2
Add the SQL Server Agent resource to the SQL Server Cluster Resource Group.

Using the Failover Cluster Manager, right-click on the SQL Server cluster resource group, select Add a resource ->More resources ... -> A - Add SQL Server Agent

Using cluster.exe,

cluster resource "SQL Server Agent" /create /group:"SQL Server (MSSQLSERVER)" /type:"SQL Server Agent"

Using Windows PowerShell,

Add-ClusterResource -Name "SQL Server Agent" -ResourceType "SQL Server Agent" -Group "SQL Server (MSSQLSERVER)"

Step 3
Set the private properties of the SQL Server Agent resource.

We need to assign the VirtualServerName and InstanceName properties of the SQL Server Agent resource to match those of the SQL Server resource. In my environment, the VirtualServerName property is SQLCLUS and the InstanceName is MSSQLSERVER since I am using a default instance.
Using the Failover Cluster Manager, double-click the SQL Server Agent resource to open up the Properties window. Click on the Properties tab to display the VirtualServerName and InstanceName properties. Enter the appropriate values for the properties and click OK.

Using cluster.exe,

cluster resource "SQL Server Agent" /priv VirtualServerName=SQLCLUS
cluster resource "SQL Server Agent" /priv InstanceName=MSSQLSERVER

Using Windows PowerShell,

Get-ClusterResource "SQL Server Agent" | Set-ClusterParameter VirtualServerName SQLCLUS
Get-ClusterResource "SQL Server Agent" | Set-ClusterParameter InstanceName MSSQLSERVER

Step 4
Add the SQL Server resource as a dependency for the SQL Server Agent resource you just created.

This is the same as adding the SQL Server service as a dependency to the SQL Server Agent service in a stand-alone instance.
Using the Failover Cluster Manager, click on the Dependencies tab of the SQL Server Agent Properties dialog box and select SQL Server under the Resource drop-down list. Click OK.

Using cluster.exe,

cluster resource "SQL Server Agent" /adddep:"SQL Server"

Using Windows PowerShell,

Add-ClusterResourceDependency "SQL Server Agent" "SQL Server"

You can also verify if the SQL Server Agent resource has all of the nodes in the cluster listed as possible resource owner. Usually, if this is done correctly at the cluster resource group level, all of the resource types inherit the settings.
Using the Failover Cluster Manager, click on the Advanced Policies tab of the SQL Server Agent Properties dialog box to see the list of Possible Owners.

Using cluster.exe,

cluster resource "SQL Server Agent" /listowners"

Using Windows PowerShell,

Get-ClusterResource "SQL Server Agent" | Get-ClusterOwnerNode

---

Modifying SQL Server registry keys

Having an incomplete or corrupted SQL Server installation also means that there are registry keys that have not been properly written or updated. It is important to backup your registry prior to performing these tasks.

1. Open the Registry Editor and navigate to the following registry hives.For default instance:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ConfigurationState
   For a named Instance
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft Microsoft SQL Server\MSSQL10_50.INSTANCENAME\ConfigurationState
2. Check the values of all the registry keys. If the value is greater than 1, it means that there was a failure either during the installation or configuration phase while running the setup process. In my environment, all of the registry keys have a value of 2.
    
   
   
   
3. Change all of the registry key values to 1.
   
   
   
   

---

Run a Repair of the SQL Server 2008 R2 installation

After all of the ground work has been done, you can now perform a repair of the SQL Server instance. To do this, run the setup.exe from the SQL Server 2008 R2 installation media and click the Maintenance link on the left-hand side. You can then click the Repair link to run the repair process. A more detailed procedure is defined here.
NOTE: Make sure that you run the repair process on the node that does not own the SQL Server cluster resource group, or in other words, the passive node. If you are, manually fail over the SQL Server cluster resource group to the other node before proceeding with the repair.

---

Bring the SQL Server Agent Resource Online

Once the repair process completes successfully, you can now bring the SQL Server Agent resource online.
Using the Failover Cluster Manager, right-click the SQL Server Agent resource and select Bring this resource onlineoption.

Using cluster.exe,

cluster resource "SQL Server Agent" /online

Using Windows PowerShell,

Start-ClusterResource "SQL Server Agent"