Identify and resolve SQL Server problems BEFORE they happen with SQL diagnostic manager

Fix SQL Server Agent on Windows Failover Cluster

By: Edwin Sarmiento | Read Comments (4) | Print Edwin works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP. Related Tips: More

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,

Using Windows PowerShell,

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.

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

Using cluster.exe,

Using Windows PowerShell,

Next Steps

• Review the series of tips on installing SQL Server 2008 Failover Cluster on Windows Server 2008
• Get familiar with Failover Cluster cmdlets in Windows PowerShell
• Check this Microsoft KB article for a configuration running SQL Server 2008 Failover Cluster on Windows Server 2003

Related Tips: More | Become a paid author

Last Update: 1/19/2012

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Friday, January 20, 2012 - 1:59:09 PM - sreekanth
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?

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

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

Monday, February 06, 2012 - 10:30:17 AM - Dennixx
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?

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

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.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the free performance monitoring tool from Idera! Optimize your SQL Server storage: compress live databases by up to 90%. Download a free trial. SQL Server Issues? Not sure where to turn for answers? Innovative SQL DBA consultants Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Free Web Cast - What Are You Waiting For? Delivered by Jason Strate on Wednesday, March 14 @ 3:00 PM EST