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.
The scenario is called out and supported on the link you've provided.
Availability Group with One Remote Secondary Replica
If you have deployed an availability group only for disaster recovery, you may need to fail over the availability group to an asynchronous-commit secondary replica. Such configuration is illustrated by the following figure:
Availability Group Upgrade in DR Scenario
In this situation, you must fail over the availability group to the asynchronous-commit secondary replica during the rolling upgrade/update. To prevent data loss, change the commit mode to synchronous commit and wait for the secondary replica to be synchronized before you fail over the availability group. Therefore, the rolling upgrade/update process may look as follows:
1.Upgrade/update the remote server
2.Change the commit mode to synchronous commit
3.Wait until synchronization state is SYNCHRONIZED
4.Fail over the availability group to the remote site
5.Upgrade/update the local (primary site) server
6.Fail over the availability group to the primary site
7.Change the commit mode to asynchronous commit
Best Answer
Ok I found what I was missed and it was the cluster because there is only two nodes in the cluster so I add third node and it is working now.