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

About Jayachandran PK
My passion is for Microsoft technologies and how if properly implemented, they can provide actual value for an organization especially in the field of infrastructure, virtualization and system monitoring. I work for the biggest Microsoft partner in Kuwait, specialized in project consultation and implementation services for enterprise clients. When I'm not at work, I try to contribute back through a charitable organization dedicated to prompting cultural values of Kerala. In my free time, I dabble in gardening and am also an avid solar power aficionado.

4 Responses to SQL Server Agent not listed as cluster resource

  1. trungdung says:

    Oh My God.. I spent 4 days to fix this error before. So many thanks for your tips

  2. Tai Nguyen says:

    Thank Jayachandran PK so much!!!! Great!!!!!!!!

  3. Dawood says:

    Thank You Mr.Jayachandran PK. You made my work simple.

  4. great post, thanks very much. we had to do this in windows 2012 and the steps worked fine with the equivalent powershell commands.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: