SQL Server 2012 – Automatic Failover in Availability Group

availability-groupsfailoversql serversql-server-2012

I have 2 nodes in my SQL server fail over cluster. I use SQL Server 2012 Availability Groups for HA purpose.

The problem is that when one my server goes down the automatic fail over does not happen!

How can I enable automatic fail over?

Best Answer

If you want to enable automatic failover, you will need to ensure that you have an "automatic failover set", to include two replicas (in your case, that'd be your two nodes) both set with synchronous commit, as well as automatic failover.

When you setup the Availability Group, this can be accomplished through the GUI, but if you had this scripted out, it would appear in the CREATE AVAILABILITY GROUP command. If you take a look at the syntax and possible parameters of CREATE AVAILABILITY GROUP, you would simply see the opportunity to set AVAILABILITY_MODE = SYNCHRONOUS_COMMIT as well as set FAILOVER_MODE = AUTOMATIC. This is essentially the steps to setup automatic failover.

Likewise, if this is already setup you can modify these replica settings with ALTER AVAILABILITY GROUP ... MODIFY REPLICA ON ... and specify the same above parameters.

Before continuing on, though, with the above modifications it is definitely going to be worth verifying what you already have in place. A quick query that will tell you what your commit mode and failover mode is for your current configuration is below:

select
    ar.replica_server_name,
    availability_group_name = ag.name,
    ar.availability_mode_desc,
    ar.failover_mode_desc
from sys.availability_replicas ar
inner join sys.availability_groups ag
on ar.group_id = ag.group_id
order by availability_group_name, replica_server_name;

Feel free to edit your question and put the output of the above query for review.

EDIT: In order to address your comment, it is worth pointing out that to have automatic failover you must be utilizing synchronous commit.

Related Question