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.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Does Synchronous mode has more latency than Asynchronous mode for automatic failover? –  Pooya Yazdani Jan 8 at 5:45
    
There is no automatic failover for asynchronous commit. –  Thomas Stringer Jan 8 at 13:45

Did setup below ?

cluster clusterName group "Your Availability Group" /properties FailoverThreshold=100

This parameter is making your cluster ready to automatic failover 100 times in some period of time. This period of time you can setup via FailoverPeriod property. More about cluster groups common properties setup you can read here msdn.microsoft.com/en-us/library/aa369680%28v=vs.85%29.aspx

share|improve this answer

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.