Why wouldn't I want to use Automatic Failover as the Failover Mode for an AlwaysOn Availabiltiy Group instance?
Are there pros for Manual Failover Mode that don't exist for Automatic Failover Mode?
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 have synchronous replicas you typically have one with automatic failover. The most common reason not to is when the primary is hosted on a Failover Cluster Instance, and it's a rule that only the FCI can failover automatically in that configuration.
But you could also have a scenario where you would prefer an outage over a failover, for instance if the secondary replica is not accessible from the application.
There are no pros as such that motivates you to use this mode of failover. its completely dependent on the requirement of the business and requirement of availability or activation of secondary server.
so may be you want to initiate a failover yourself just to satisfy your believe that failover works best this way or you can make yourself confident that SQL OS will do it for you.
Many times you want to do it yourself to know whats happening in the environment and when its happening. you may have some ependency to take care at the time of failover.
In case its asyncronus you may have to get the failover approved since it comes with a risk of data loss.
Hope I have given a satisfactory answer. please let us know if you are comfortable with this answer.