1

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?

  • 2
    Sounds like there are no dependencies on the services which is making them startup before the AG comes online (AR becomes PRIMARY state). Can you show a dependency report of the services? I would set them to depend on the AG resource. – Sean Gallardy Aug 26 '16 at 15:40
  • I forgot to add that as a bullet point. The services are both dependent on the SQL AG. For completeness, the following report includes the Network Name resource that our proprietary services use at the bottom, but it should be irrelevant. Dependency Report – Blaine Perry Aug 26 '16 at 18:48
  • Awesome - Thank you. Given the new information, it sounds like the databases aren't coming online fast enough before your services attempt to connect. The error is happening most likely because a default database in the connection is specified, which is one of the databases in the AG. The issue is that the service attempts to connect before the databases are online as the primary. Generally setting the dependency is enough as the failover should be pretty fast. If you look in the errorlog, how long did it take for the database(s) to fully online? – Sean Gallardy Aug 26 '16 at 19:29
  • Thanks, Sean. (And sorry for the delayed response.) I believe the databases are coming online in the AG before our services try to connect. Here's a snippet of the errorlog. – Blaine Perry Aug 30 '16 at 19:30
  • Blaine, going by the except of the log this is what happened. The server was a secondary, it was signaled by WSFC to change state to primary. Something else happens in the WSFC and the server reverts back to a secondary because of the WSFC. From the except I had, anyway, it never became the primary... so the service could never connect because those DBs were in transitional. It did, once again connect after they transitioned back to secondary. You'll want to see why it thought it should failover and then revert itself back. Check the cluster log. – Sean Gallardy Aug 31 '16 at 3:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.