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?
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:
availability_group_name = ag.name,
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.
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