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?
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 only takes a minute to sign up.Sign up to join this community
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.
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