AlwaysOn High Availability Automatic Failover
Always on High Availability provides automatic failover up to 2 secondary server while you configure Availability Group and Server2 is selected as Automatic Failover Replica without any data loss.
Note: If you are frequently restart your server or SQL Server service then this option is not advisable.
See the current scenario.
–>Server1 is Primary Replica.
–>Server2 is Secondary Replica (With Synchronous and automatic failover)
–> Server3 is Secondary Replica (With Asynchronous and manual failover with data loss)
–>Now stop SQL Server service of Server1 to test automatic failover.
–> Now check
Automatic failover has been occured by AlwayOn Availability Group and Server2 initiated as Primary Server.
Server3 is now Secondary Replica of Server2 and Server1 seems to be down.
Now when you start SQL Server service of Server1, Server1 will be Secondary Replica of Server2.
Thanks & Regards,