Monthly Archives: April 2014

SQL Agent Error During Windows Failover Cluster Setup

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:

CompleteWithFailures

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

CMD

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

AddResource

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.

Using cluster.exe

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.

 

 

 


Cumulative Update #16 for SQL Server 2008 SP3

Here’s a link for the download for the CU #16 for SQL2008 SP3 http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/03/17/cumulative-update-16-for-sql-server-2008-sp3.aspx. 

The post also includes all the previous updates and with links to the KB articles.

 


She Goes Global

Travel | Self-Improvement

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with thee Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment