10

Playing with AG setup I have the WSFC up and configured with two nodes in one availability group called DevClusterOnline. Both nodes (DEV-AWEB5 primary, DEV-AWEB6 secondary) are running Windows Server 2008 R2.

If I check the health of my AG I get this:

Availability Group Health description

Running the query below will return this result set: Synchronous commit and automatic failover setup

select
    ar.replica_server_name,
    availability_group_name = ag.name,
    ar.availability_mode_desc,
    ar.failover_mode_desc
from sys.availability_replicas ar
inner join sys.availability_groups ag
on ar.group_id = ag.group_id
order by availability_group_name, replica_server_name;

If I disconnect DEV-AWEB5, I cannot connect to the Group Listener (DevListener), but I can ping it and it will respond to my ping. The replica - DEV-AWEB6 goes into a RESOLVING state and my DB is unaccessable. I can however, manually go into Management Studio and set Failover to DEV-AWEB6 and then I´m up and running again and DevListener will once again accept connections.

Considering that these facts confirm that the failover actually works, that I have synchronized commits and automatic failover configured I have no idea what if malfunctioning in my setup.

When I disconnect DEV-AWEB5 I expect that my replica will retain connection and thus, DevListener too. I expect that the automatic failover will allow me to connect to the AG Listener transparently. From an end-user perspective, using a Web system it should not be noticable that one of the DB servers go down.

I am stuck here, can anyone please enlighten me on what I am doing wrong?

2
  • 1
    What does your quorum model look like? Is it a simple node majority? If so, that could be your problem. From technet.microsoft.com/en-us/library/cc731739.aspx, that quorum model can only sustain a loss of (half of the nodes in the cluster) -1. So, if you have a two node cluster with node majority quorum, you can sustain 0 node failures.
    – Ben Thul
    Apr 30, 2014 at 15:55
  • 3
    @BenThul If the cluster lost quorum then the OP wouldn't be able to failover manually. Apr 30, 2014 at 17:24

2 Answers 2

6

If I disconnect DEV-AWEB5

Define "disconnect", if you will. My guess is you kept the box up but took SQL Server down.

I cannot connect to the Group Listener (DevListener), but I can ping it and it will respond to my ping

That's because the listener is just a virtual network name (VNN) within the WSFC cluster resource group for the represented availability group. Your DEV_AWEB5 node still owns the cluster resource group, but it's just the AG cluster resource most likely that is in a failed state. The VNN must still be online (expected behavior). It's simply pointing to whatever node is owning that resource group (in this case, DEV-AWEB5). In fact, if you had PowerShell remoting enabled, and you ran the following:

Invoke-Command -ComputerName "YourListenerName" -ScriptBlock { $env:computername }

Likewise, if you can RDP into DEV-AWEB5 (provided you have the capability and accessibility, etc.) then you'd be able to RDP using the listener name (mstsc /v:YourListenerName). It's just a VNN.

The return of that would be the computer name of your owning node.

By all of your symptoms, I'd be willing to bet that you've reached your failover threshold. The failover threshold determines how many times the cluster will attempt to failover your resource group in a specified time period. The default of these values max failovers n - 1 (where n is the count of nodes) in a period of 6 hours. You can see that through the following WSFC PowerShell command:

Get-ClusterGroup -Name "YourAgName" |
    Select-Object Name, FailoverThreshold, FailoverPeriod

That just gives you the settings (which you can modify if you so choose, of course).

The best way to prove that this is the case for you, you would need to generate the cluster log (the system event logs only go into detail as far as " has failed", or something like that).

Get-ClusterLog -Node "YourClusterNode" -TimeSpan <amount_of_minutes_since_failure>

That'll by default get put into the "C:\Windows\Cluster\Reports" folder, and the file is called "Cluster.log".

If you were to open up that cluster log, you should be able to find the following string in there, indicating exactly what happened and why it happened:

Not failing over group [YourClusterGroupName], failoverCount [# of failovers], failover threshold [failover threshold value], nodeAvailCount [node available count].

The above message is simply WSFC telling you that it will not failover your group because it's happened too much (you hit the threshold).

Why does this happen? Simply to prevent the Ping-Pong effect of cluster resources going back and forth too frequently between nodes.

Whereas this would be common to hit these thresholds in failover testing, in production it would typically point to a problem that should be investigated.

1
  • 3
    Thank you for your help, I followed your directions but I finally found that this was not the problem. The reason I could not get the AG to automatically failover was because I had not configured the WSFC dependencies correctly. As it turns out I needed to add MSSQL as a cluster resource (Generic Service) and add it as a dependency in the Failover Cluster Manager together with the AG listener. Also, it is necessary to tick the checkbox 'If restart is unsuccessful, fail over all resources in this service or application'. I am sure you were under the impression that I had done this already.
    – Marcus
    May 12, 2014 at 11:21
1

Adding MSSQL as a Generic Service Resource is not the Answer.

That will just put the Cluster Manager in charge of the SQL Server Service, OK, yes it will fail-over automatically, but you will notice in SQL Server Configuration Manager that your services are now set to "Manual" indicating that the Cluster Manager is now in control of your SQL server Service.

You are putting Cluster Manager in charge of a NON Clustered Application.

It will end in tears.

The correct approach it to correctly configure the SQL Server availability Groups as per the MS documentation.

And also ensure you are not exceeding the Fail-over Parameters as Defined in the Cluster Manager > Roles > Fail-over Tab.

If you are exceeding these limits, the cluster will not fail your resources over and an error will be posted to the Application Event Log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.