SQL Server Agent not listed as cluster resource

While installing SQL Database server 2008 R2 on a Windows 2008 R2 two node cluster, I encountered following problem. Thought of posting this so that this might be helpful for anyone faces the same problem.

The SQL cluster installation completed with failures.

Event Log:

Windows Event Log showed Error “SQLServerAgent could not be started (reason: Unable to connect to server (local); SQLServerAgent cannot start”.  With event ID 103

SQL Server Management Studio:

The SQL Server Management Studio was able to connect to database but the agent service is failed to start.

SQL Server Agent was missing in cluster resource listing.

To add missing agent resource, follow the below steps:

Step-1:  Add the SQL agent resource type:  Run command prompt with Administrator privilege and run

cluster restype “SQL Server Agent” /create /DLL:sqagtres.dll

Step-2:  Add the SQL agent resource: In the Failover Cluster Management, Click Add a resource from Action panel and then click More resources, select A-Add SQL Server Agent.

Note: Name the resource as “SQL Server Agent”, as this is case sensitive unless you are on CU3 or above for SQL Server 2008 RTM.

Right-click on the SQL Agent Resource and go to Properties and fill in values for the following two parameters.

VirtualServerName  (the network name specified while installing SQL cluster)

InstanceName(MSSQLSERVER for default instance  otherwise specify the named instance name).

Add all the SQL Server Resource as a dependency for the newly created SQL Server Agent resource.

Step-3 : Modify the registry entry: Open Registry Editor and browse to the following location.

If you are running Default Instance:

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\ConfigurationState

If you are running Named Instance:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft \Microsoft SQL Server\MSSQL10.INSTNAME\ConfigurationState

Make sure that the values for all the keys present are 1. In case it is not, change it to 1.

Note:This will set the configuration state for all SQL features to 1, meaning the feature is installed and configured. A value greater than 1 indicates either an install failure or configuration failure.

Step-4 : bring the  SQL Server Agent online: go to the Failover Cluster Management and thenright clickon SQL Server Agent, bring online.

 

SQL Server Management Studio shows agent service as started.

Step-5: In case the SQL agent resource failed to come online, do a repair:

Launch the SQL Server Setup program (setup.exe) from SQL Server installation media. After prerequisites and system verification, the Setup program will display the SQL Server Installation Center page. Click Maintenance in the left-hand navigation area, and then click Repair to start the repair operation

 

Advertisements
%d bloggers like this: