Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Fix SQL Server Agent on Windows Failover Cluster


By:   |   Read Comments (25)   |   Related Tips: More > Clustering

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?

failover cluster sql server other resources
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.

add a sql server resource to a cluster

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

cluster.exe Create the SQL Server Agent resource type

Using Windows PowerShell,

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

powershell Create the SQL Server Agent resource type

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

cluster manager dd the SQL Server Agent resource to the SQL Server Cluster Resource Group

Using cluster.exe,

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

cluster.exe dd the SQL Server Agent resource to the SQL Server Cluster Resource Group

Using Windows PowerShell,

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

powershell dd the SQL Server Agent resource to the SQL Server Cluster Resource Group

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.

cluseter manager Set the private properties of the SQL Server Agent resource.

Using cluster.exe,

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

cluster.exe Set the private properties of the SQL Server Agent resource.

Using Windows PowerShell,

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

powershell Set the private properties of the SQL Server Agent resource.

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.

cluster manager Add the SQL Server resource as a dependency for the SQL Server Agent resource you just created

Using cluster.exe,

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

cluster.exe Add the SQL Server resource as a dependency for the SQL Server Agent resource you just created

Using Windows PowerShell,

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

powershell Add the SQL Server resource as a dependency for the SQL Server Agent resource you just created

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.

Cluster Manage list of Possible Owners

Using cluster.exe,

cluster resource "SQL Server Agent" /listowners"

cluster.exe  list of Possible Owners

Using Windows PowerShell,

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

powershell  list of Possible Owners

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.
     
    Modifying SQL Server registry keys

  3. Change all of the registry key values to 1.
    Modifying SQL Server registry keys

    Modifying SQL Server registry keys

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.

Run a Repair of the SQL Server 2008 R2 installation

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 online option.

failover manager Bring the SQL Server Agent Resource Online

Using cluster.exe,

cluster resource "SQL Server Agent" /online

cluster.exe Bring the SQL Server Agent Resource Online

Using Windows PowerShell,

Start-ClusterResource "SQL Server Agent"

powershell Bring the SQL Server Agent Resource Online
Next Steps


Last Update:






About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, October 15, 2015 - 1:46:17 AM - venu babu Back To Top

how to resolve the cluster resource group is failed?

SQL Server services are up and running,all disks are Online.But, SQL Server agent is not started?

 

Thanks for advance.

 

Regards,

venu babu

 

 


Monday, March 16, 2015 - 4:08:31 AM - Dinesh DBA Back To Top

I am trying move mssql resoueces to another node , mssql resouces is move but not start in another node.Plz share solution.


Monday, February 09, 2015 - 3:47:40 PM - Sean Back To Top

Very well explained and informative article. Solved my problems beautifully. Thank you for taking the time to share this knowledge.


Wednesday, June 18, 2014 - 6:18:32 AM - Gleb Back To Top

Nice article, thank you very much.


Wednesday, May 14, 2014 - 1:45:32 PM - Frank F Back To Top

This saved my bacon! Excellent post, thank you.


Wednesday, April 02, 2014 - 2:39:43 PM - Julio Cesar Romano Back To Top

this saved me, thx


Thursday, January 02, 2014 - 4:36:37 AM - Paul H Back To Top

Thank you very much for this article, Edwin. Without it I would have found it much more difficult or impossible to fix a broken cluster server.


Friday, October 25, 2013 - 10:37:24 AM - Hidayath Back To Top
Thursday, October 24, 2013 - 12:42:43 PM - bass_player Read The Tip

Identify the reason why the SQL Server Agent cluster resource does not come online. You may have other issues other than the service not starting. If you decide to add the resource, you only need to do it on the cluster since these are visible from any node in the cluster

Thanks for the reply. The reason SQL Server Agent cluster resource does not come online is it was never added to the sql server dependency during the initial setup.

I have this change scheduled tomorrow, do i need to do this change only using sql service accoung or i can perform this change with any administrator account. There are some issues with the sql service account password currently

regards,

Hidayath


Thursday, October 24, 2013 - 12:42:43 PM - bass_player Back To Top

Identify the reason why the SQL Server Agent cluster resource does not come online. You may have other issues other than the service not starting. If you decide to add the resource, you only need to do it on the cluster since these are visible from any node in the cluster


Thursday, October 24, 2013 - 10:38:32 AM - Hidayath Back To Top

 

Tuesday, October 22, 2013 - 3:39:36 PM - bass_player Read The Tip

That depends. If the installation failed before you add the second node to the cluster, then, you only need to do it on the first node. If it failed when you add the other node, then it only needs to be done on that node.

Thanks for the reply. In our case the installation was succseefull and we just need to add sql server agent to the sql server dependency. Reason being whenever there is a reboot sql server agent does not start automatically. In this case do we need to add resource and then add it to the sql server dependency on both nodes or just the virtual node

Regards,

Hidayath


Tuesday, October 22, 2013 - 3:39:36 PM - bass_player Back To Top

That depends. If the installation failed before you add the second node to the cluster, then, you only need to do it on the first node. If it failed when you add the other node, then it only needs to be done on that node.


Monday, October 21, 2013 - 4:08:23 PM - Hidayath Back To Top

Do we need to do the above steps on both the nodes to add the sql server agent or making the changes to one node is sufficient


Wednesday, April 10, 2013 - 4:02:40 AM - Nicolas Nerson Back To Top

Great post Edwin,

Your post is very usefull and very easy to understand!

Nicolas

 


Thursday, March 21, 2013 - 10:10:18 AM - resul Back To Top

Thanks  Edwin,

Thanks for helping this problem. This solving is definitely working. 

Have a nice day.

 

Resul


Wednesday, November 28, 2012 - 9:54:33 AM - Rhona Back To Top

Great Article. Thank you. I am having a very difficult time getting SQL Server 2008 R2 installed (Cluster). On the last go round it failed partly I think becasue the SQL Server Agent failed to start. Could you please give me step by step instructions on how to create the service accounts for the database engine service  and the SQL Server agent service?

Thank you

 


Monday, November 12, 2012 - 10:32:37 AM - bass_player Back To Top

From the error message, it seems that you are trying to add a new node to an existing clustered SQL Server instance. This has to be done on the non-active node as described in this article. 

http://www.mssqltips.com/sqlservertip/1721/install-sql-server-2008-on-a-windows-server-2008-cluster-part-4/

 


Monday, November 12, 2012 - 4:11:15 AM - Mams Back To Top

Hello,

Thanks you for this article, I have the same problem each time, i would add a new sql instance I got this error message.

But I cannot do repair cause the rsssource check failed. Can help me ? Thanks

 

------

Engine_SqlResourceIsOfflineIfCurrentNodeActive Si cette instance est en cluster et appartient au nœud actuel, vérifie que la ressource SQL Server est hors connexion ou en échec. Échec Si cette instance en cluster appartient au nœud actuel, la ressource SQL Server doit être hors connexion ou en échec.

Sorry it's in french


Tuesday, May 08, 2012 - 5:39:46 PM - Rick Gao Back To Top

I fixed it by adding SQL Server agent error log file location to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\SQLServerAgent on the second nodes. You can find that location on the node1.


Monday, May 07, 2012 - 12:29:24 PM - Rick Gao Back To Top

After failover to the second node, the SQL agent still keeps online, but the SQL agent log files fail to connect to the SQL agent. Do you have the reason and solution? Thanks.


Tuesday, March 13, 2012 - 9:25:43 AM - manu Back To Top

Nice article with complete troubleshooting steps. Thanks for sharing..


Sunday, February 26, 2012 - 3:20:37 PM - Mohan Back To Top

Nice article with all possible way of fixing the issue.


Monday, February 06, 2012 - 3:08:30 PM - bass_player Back To Top

There are a couple of scenarios where you need to run the repair process. One scenario, which was highlighted in the article, was creating a new SQL Server failover cluster. This means that you only have one cluster to begin with. If this is the case, you only need to run the repair process on the first node. Another scenario is when you are trying to install a service pack. Since the process of installing a service pack/cumulative update has changed in SQL Server 2008 Failover Cluster on Windows Server 2008, you will be starting off from the passive/inactive node. If this process fails, you only need to run the repair process on the affected node. The ultimate test of checking whether the repair process worked or not is to manually failover the SQL Server clustered resource group on all of the nodes in the cluster and review both Windows and SQL Server error logs.


Monday, February 06, 2012 - 10:30:17 AM - Dennixx Back To Top

Great article, thanks!

I had only one question: Should the repair process be done for each node in the cluster (failing over in between)? Or is it sufficient to do it only once?


Wednesday, January 25, 2012 - 3:37:39 PM - bass_player Back To Top

There are a few considerations for this tip. First, if this is a non-production server, I would recommend starting from scratch to keep things clean. My philosophy has been to make sure to get things right the first time so that you don't have to worry about support and administrative issues down the road. If this happened on a production machine - for example, one specific scenario is while installing a service pack - this means that the SQAGTRES.DLL file is already there because it was working prior to installing the service pack. And I certainly do not recommend implementing a workaround that does not fall under a supported scenario


Friday, January 20, 2012 - 1:59:09 PM - sreekanth Back To Top

Very Nice Info Edwin! Really appreciated:)

Just Curios - What if we coudn't locate SQAGTRES.DLL file in system32 Folder? Can we copy that DLL from some other Cluster(of course with the same SQL Build). What if we don't have any other Clusters with the same build? Also, are you aware of any DLL's which we should be looking for in "SysWOW64" folder if we are running on X64 machines?


Learn more about SQL Server tools