SQL 2016 cluster failed adding second node – adding SQL Agent as cluster resource

This happened to me first time. I was adding second node to SQL cluster, and SQL Server Agent account is grayed out.

image

If I go to first SQL cluster node, Under Failover Cluster Resources you can not see SQL server Agent, and it should be there (see another picture)

imageimage

I was looking on internet and found couple of solutions, but in short you should add SQL Agent as Resource type to Failover Cluster installation. I decide to do it all with PowerShell just for fun. Script/commands below are not the script. They are just steps in process, and you should consider to make right command from this especially if you have multiple SQL instances installed. Select right cluster instance and pipe output to command you need.

$ClusterName = "spssqlcluster"
$FciClusterGroupName = "SQL Server (MSSQLSERVER)" # may need to modify to reflect your environment

# check to see if you have the SQL Server Agent cluster resource type
Get-Cluster -Name $ClusterName  |
    Get-ClusterResourceType |
    Where-Object {$_.Name -eq "SQL Server Agent"}

#check cluster SQL resource group to check added resources

Get-ClusterGroup -Name $FciClusterGroupName | Get-ClusterResource

# if not, add it
Add-ClusterResourceType -Name "SQL Server Agent" -Dll "sqagtres.dll”

#Add cluster resource to SQL Server group

Add-ClusterResource -Name "SQL Server Agent" -Group $FciClusterGroupName -ResourceType "SQL Server Agent"`

 -Cluster $ClusterName

#Check Cluster REsource “SQL Server Agent”  parameters

Get-ClusterResource | where-object {$_.Name -eq "SQL Server Agent"} | Get-ClusterParameter

#Edit Cluster Resource properties 

Get-ClusterResource | where-object {$_.Name -eq "SQL Server Agent"}`

| Set-ClusterParameter  -Name "VirtualServerName" -Value 'SP2016SQL'

Get-ClusterResource | where-object {$_.Name -eq "SQL Server Agent"} `

| Set-ClusterParameter  -Name "InstanceName" -Value 'MSSQLSERVER'

#setting resource dependencies

Set-ClusterResourceDependency -Resource "SQL Server Agent" -Dependency "([SQL Server])"

 

 

If you run first paragraph of script without where-object part  you will see all available resource types on your failover cluster installation

image

You can se that SQL Agent is not available under resource types. If you run second paragraph you can check added resources to SQL Server cluster group

image

To fix things you first add SQL Server Agent as resource type with this command in script. You don’t need to give exact path for dll because it is already in Windows PATH variable.

image

If you run now full first paragraph in script you will see that SQL Server Agent is added like resource type

image

Next  thing  is to add SQL Agent as the resource to SQL Cluster Group

image

You can see that it’s state is offline because we need to give resource parameters to run. We check properties of resource and its values running this command

image

We see that values for VirtualServerName and Instance name is empty. We add it with this command, and you can check its value with command before.

image

You do the same thing for Instance name property

image

But we are not there yet.  SQL Server Agent should start after SQL Server is started in Cluster and that is why we need to put dependency on this cluster resource. After that your SQL Agent will go online in cluster

image

image

 

And after that, if you go back on your Service Account screen for adding another node for cluster you will get account name for SQL Server agent.

image

And that is all to it as I know for know. If I see some malfunction after this I will add it to post. If you know something I dont just comment.

Advertisements

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.