AlwaysOn Manual failback not working correctly

Question

I've set up a small fail over cluster using AlwaysOn in SQL Server 2014 and Windows 2012R2.

The cluster (SQL-CLUSTER-DEMO) just contains 2 SQL nodes (SQL-01 [Primary] & SQL-02 [Secondary]) and a file share quorum. Availability Group is set up in SQL Server no problems.

I am connecting to SQL-CLUSTER-DEMO and running transactions every second.

When I simulate failures in turn (pull the network cable from the machine, replace, pull from other machine, replace), they switch between primary and secondary as expected no problems.

The issue comes when I simulate failure on the primary and then use the Manual Fail-over wizard to revert back once it has recovered. The application I have running then starts to error with "Database is Read Only" and SQL-CLUSTER-DEMO seems to think it is pointing to the secondary.

I'm really stuck trying to figure out why automatic failure swaps perfectly, but manual fail-over doesn't seem to work... what am I missing?

Answer 1

I resolved this in the end. It was my own fault. I had connected using the name of the cluster "SQL-CLUSTER" rather than creating a listener. Originally I had tried to create a listener but was getting errors so stupidly I'd just ignored them. The errors were because I'd not set up the network properly (I'm a DBA not a network technician :) )

Anyway, after setting up the listener properly it works like a charm. Long story short... don't connect to the cluster, connect to the listener.