We recently deployed a setup of
SQL 2014 availability groups and we are having some difficulty with the failover.
- Two windows server 2012R2 VMs in a windows cluster with a CNAME pointed at the cluster IP address.
- The availability group is setup synchronously.
- The listener and the CNO are both in active directory.
- The application is using the cluster CNAME to connect to the database.
The problem we are running into is that in the event of a failover the Windows cluster is failing over, and the AG is not. So we get the primary of the windows cluster pointed at the secondary of the AG, so all the databases are read only.
My question is:
should we be pointing the CNAME at the listener instead of the Cluster IP? Or more generally what is the proper way to ensure correct failover behavior?