We have a two-node SQL Server 2014 AlwaysOn Availability Group that spans multiple subnets. We also have two proprietary services on the same cluster role as our AG. We decided to put them on the same role because we prefer that the services always be on the same node as the SQL Server in the event of a failover.
During a planned or unplanned failover, the two services cannot connect to the AG when they attempt to come online and fail with the following message.
The target database, 'X', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.
I do not expect this because of the following conditions:
- The SQL client in the services are using the MultiSubnetFailover=true keywords.
- I confirmed on each node that both IPs of the AG Listener are being returned by DNS.
- I can connect to the listener using sqlcmd (with the -M switch) on the node that is attempting to come online during the time the services fail to connect.
What usually happens is that the cluster will attempt to bring up the services on the secondary node, which fails as per the message above. It then fails back to the original primary node, as per my the policy settings on the cluster services. I then immediately invoke the failover a second time and it succeeds.
The obvious culprit would be the service, but our developer has confirmed that the MultiSubnetFailover=true is being used in the connection string. Also, the service connects eventually, just not during the failover process. Has anyone seen anything that can explain this behavior or have any troubleshooting tips?