Problem: You had just finished up installing cluster setup in your Primary node. After verifying your setup is complete and working in your Failover Cluster Manager window, you see that the SQL Agent resource is not online or you don’t see the SQL Agent resource at all. Or maybe after the Finish window, you see the following results like the one below:
Solution : One of the reasons that could cause this error to happen is having a corrupt installer. This is exactly what happened during setup of our DR servers.
There’s a quick solution for this. You can add the SQL Server Agent resource and bring it online manually. There are two ways to do this. Either via the Failover Cluster Manager or via the cluster.exe in the cmd window.
Using the Failover Cluster Manager
Open a cmd window and run as administrator. Type the following command ;
cluster restype “SQL Server Agent” /create /DLL:sqagtres.dll
You should get the similar result as in the screen above. This means that the SQL Server Agent was created successfully.
Then go back to your Failover Cluster Manager window. Go to your SQL service right click >Add a Resource >More Resources >Add SQL Server Agent
You should be able to see the SQL Server Agent in your resources. Then the next step would be to modify the private properties of your SQL Server Agent. For this part, you have to prepare the VirtualServerName and your InstanceName ready.
In your Failover Cluster Manager, go to your SQL Server Agent resource and double click. This will open the SQL Server Agent properties. Go to the Properties tab and update the values for VirtualServerName and InstanceName. In my case, my VirtualServerName happens to be also my hostname and my InstanceName is the universal default MSSQLSERVER.
On the same private property window, go to Dependencies tab. From the Resources, select SQL Server from the drop down list and click OK.
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. Click OK to save the changes.
The above steps can be done via the cluster.exe and summarised in four (4) steps.
To create the SQL Server Agent resource:
cluster.exe restype “SQL Server Agent” /create /DLL:SQAGTRES.DLL
To add the SQL Server Agent in your cluster resources:
cluster resource “SQL Server Agent” /create /group:”SQL Server (MSSQLSERVER)” /type:”SQL Server Agent”
To update the VirtualServerName and InstanceName:
cluster resource “SQL Server Agent” /priv VirtualServerName=MYHOSTNAME
cluster resource “SQL Server Agent” /priv InstanceName=MSSQLSERVER
To add the dependency for SQL Server:
cluster resource “SQL Server Agent” /adddep:”SQL Server”
Update the Registry
After you have completed the above steps either by Failover Cluster Window or via the cluster.exe, you need to update the registry keys in your registry. Because the SQL Server Agent installation was not complete or corrupt, the registry maybe not properly written or updated.
Open the registry in the following path, (in my case, i had the default instance. It might be a named instance in your case.)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ConfigurationState
Verify the values of the registry keys. If the value is greater than 1, it my case it was a 2, that means the installation was not complete. Edit all of them to a value of 1. Then close your registry window.
Bring the SQL Server Agent resource online
After completing the above steps, either via GUI in the Failover Cluster Manager or via the cluster.exe in cmd window, you should be able to bring the SQL Server Agent resource online.
Right click on the SQL Server Agent and click on ‘Bring this resource online.’ If you prefer to do this via cluster.exe;
cluster resource “SQL Server Agent” /online
Run the Repair of SQL Server 2008 R2 Installation
If you have completed all the steps above and after attempting to bring the SQL Server Agent online still fails, you may need to run the SQL setup again but this time in Repair mode.
You may visit this link for a detailed instructions.