SQL AlwaysOn Avail Group Failover and Client Disconnects

We had an issue recently where an application was not properly getting disconnected from SQL during a failover of an AlwaysOn Availability Group (AOAG).  Some background:  The application was accessing the primary node, and after the failover the application continued to access the same node.  Unfortunately, as it was now read-only, the app was not very happy.

Turns out it was due to the Read-Only configuration of the secondary.  We had it set to “Yes” which allows any connections to continue to access the secondary with the assumption the application is smart enough to know it can only read.  It appears while using this setting, connections aren’t forcefully closed, causing all sorts of issue.

Setting it to either “No” or “Read-Intent Only” properly severed the connections for us.  Yay!

For more info.

Leave a Reply

Your email address will not be published. Required fields are marked *