I have been testing the Availability Groups feature in SQL Server 2012 and am finding that there is approximately 15 seconds of down time when the primary server fails over to the secondary server. All SQL queries executed during this time are failing until the failover transition is complete.

Is there any way to get this down to 0 seconds and prevent queries from failing during the failover transition?

In other words, is there a way to get any queries running during the failure to be redirected to the primary server instead of failing... and is there any way to get new db connections to immediately connect to the secondary server instead of failing to connect during the failover transition?

I currently have 2 servers set up in the Availability Group.


No, there's no way to do zero-downtime failover with AlwaysOn (or in SQL Server in general, as far as I'm aware). To do that, the SQL Server you're connected to would have to do state transfer to another node mid-query, and since many failovers are unexpected, that's not possible.

However, you can enable "read-only secondaries" in AlwaysOn, and then your readers would have zero-downtime when the primary server fails over - since they're connecting to a secondary copy anyways to do their SELECT queries, they wouldn't even notice the failover. There would still be an interruption for users with a "Write" connection open, but at least some part of your user base would be uninterrupted.

  • With "read-only secondaries" enabled, do any read requests occur on the primary or are they always done on the secondaries? And what happens if the secondary server has a fault in that situation?
    – John
    Oct 24 '12 at 14:56
  • Reading against a secondary only occurs if you configure the client to connect to the secondary.
    – Hannah Vernon
    Oct 24 '12 at 14:57
  • 1
    I see, so that requires a smarter client app.
    – John
    Oct 24 '12 at 15:04
  • @JohnHughes: Check out this Microsoft document (middle of page 5): tinyurl.com/9dtvndv and this blog post: tinyurl.com/8cyr9za. You don't necessarily need a smarter app - when you enable the feature, you can specify that any application can connect to the readable secondary, but any attempt to do a DDL/DML transaction will fail. If you're using the newer native client, you can specify "ReadIntent", which means the newer client will read from your secondary, while older clients will continue to do all transactions against your primary. An option but some reading is needed.
    – SqlRyan
    Oct 24 '12 at 15:19
  • SqlRyan is correct, there is no way to failover a SQL Server instance using AlwaysOn Availability Groups without the clients being disconnected.
    – mrdenny
    Oct 25 '12 at 18:01

Your Answer

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

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