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.

In a traditional SQL Server clustering when failover occurs, all client that are connected to SQL Server failover instance looses the connection and each client must reestablish a new connection to failover cluster instance.

Does AlwaysON Availability Group mitigate this problem? Does failover in case of SQL Server 2012 AlwaysON availability group is transparent to client connecting to SQL Server.

share|improve this question
    
Aaron B. will no doubt give you a great answer, but briefly: Clustering is still clustering, and AG is like a beefed-up version of DB mirroring. You can do both. –  Eric Higgins Sep 28 '12 at 16:50
    
base of my question, it has a listener seating in a front that client should connect to and listner should redirect it to the replica that is primary? else what's point. –  SQL Learner Sep 28 '12 at 16:58
1  
The point is that the name of the SQL server remains the same, regardless of the hardware that's powering it, so no configuration settings need to change when things fail over. I'm pretty sure the connection would still drop, though. –  Jon Seigel Sep 28 '12 at 17:17
    
adding a "listener" in front of the server is pointless since it introduces a single point of failure and more complexity. And yes, the connection will fail. The client simply needs to retry the command to give the sql driver a chance to connect to the new principal. –  Max Vernon Sep 28 '12 at 17:41
add comment

2 Answers 2

up vote 5 down vote accepted

No. During a failover, clients will notice. Your connection to the SQL Server will be broken, and you'll have to retry your transaction.

share|improve this answer
    
D'oh - I guess I should have had the cajones to make my comment an answer! Good to see you Brent! –  Max Vernon Sep 28 '12 at 18:47
    
Unless the app is coded to retry (mirror-aware)... msdn.microsoft.com/en-us/library/ms366199(v=sql.105).aspx –  Eric Higgins Sep 28 '12 at 18:54
    
"Coded to retry" means that your application has to have code that retries the transaction. The question was about whether the client must reestablish a new connection to the instance, and that's exactly what happens in this situation (as well as in the mirroring situation). –  Brent Ozar Oct 7 '12 at 21:05
add comment

For those reading this post later as a result of a web search. It is indeeded described in SQL Server BOL here. Doh... I could not find it earlier....

Behavior of Client Connections on Failover

When an availability group failover occurs, existing persistent connections to the availability group are terminated and the client must establish a new connection in order to continue working with the same primary database or read-only secondary database. While a failover is occurring on the server side, connectivity to the availability group may fail, forcing the client application to retry connecting until the primary is brought fully back online.

If the availability group comes back online during a client application’s connection attempt but before the connect timeout period, the client driver may successfully connect during one of its internal retry attempts and no error will be surfaced to the application in this case.

share|improve this answer
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.