Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Check this question (and the answers): dba.stackexchange.com/questions/25124/… –  Max Vernon Oct 24 '12 at 15:01
    
Perfect, thanks! –  John Hughes Oct 24 '12 at 15:05
add comment

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
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 Hughes Oct 24 '12 at 14:56
    
Reading against a secondary only occurs if you configure the client to connect to the secondary. –  Max Vernon Oct 24 '12 at 14:57
1  
I see, so that requires a smarter client app. –  John Hughes 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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