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,
Using Windows PowerShell,
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,
Using Windows PowerShell,
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 InstanceName=MSSQLSERVER
Using Windows PowerShell,
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,
Using Windows PowerShell,
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,
Using Windows PowerShell,
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.
- 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\ConfigurationStateFor a named Instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft Microsoft SQL Server\MSSQL10_50.INSTANCENAME\ConfigurationState - 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.
- Change all of the registry key values to 1.
