In this article, we look into failover options present in AlwaysOn Availability groups in MS SQL Server
In layman’s terms, AlwaysOn Availability Groups can be considered as a form of disaster recovery solution which also helps in increasing the availability of database. Availability Groups support failovers in SQL Server. And it is important to note that failovers are not caused due to any issues with database, such as corruption or unavailability. The failover environment is supported by these groups. However the kind of failover occurs in what situation depends on what kind of Availability groups does the secondary replica belongs to. It can either be from Asynchronous commit mode or Synchronous commit mode.
The types of failovers existing in Availability groups are as follows:
Failovers Applicable to Secondary Replicas from Synchronous Commit Mode
Secondary replicas belonging to Synchronous Commit Mode of Availability groups are called Synchronous Commit Secondary Replicas. These replicas support two types of failovers in this mode. If the Failover Mode is set to ‘Manual’, secondary replica will support a manual failover. If you set it to ‘Automatic’, it will support an automatic failover. One type of failover is possible in both cases, thus making it two different types of failovers, which are:
- Planned Manual Failover – This assures no data loss. In this type of failover, the primary and secondary replicas under Synchronous Commit Mode, interchange their roles. Meaning that synchronized primary replica transitions to secondary role and synchronized primary replica transitions to primary role. This happens after the DBA has issued the failover command. The only condition required for this Manual failover is that both replicas are running in Synchronous Commit Mode and that secondary replica is already synchronized.
- Automatic Failover – This also assures no data loss. It usually takes place in-case of a failure leading to the synchronized secondary replica transitioning to primary role, once the primary replica becomes available, it transitions to secondary role. The conditions required for an Automatic Failover are similar to the ones required in a manual failover. This also requires both the Primary and Secondary replicas to be in Synchronous Commit Mode, the failover node should be set to ‘Automatic’ and the secondary replica be synchronized.
Failovers Applicable to Secondary Replicas from Asynchronous Commit Mode
When the Availability groups are running in Asynchronous Commit Mode, only one type of failover is possible with the target secondary replica. This single possible failover is listed below:
Forced Failover – This leads to loss of data and is rightly called a ‘Forced Failover’ because firstly, it has to be done manually, and it is a disaster recovery option. This failover is also called ‘Forced Manual Failover’ because doing it manually is the only way of doing it, with no scope of automatic.
Failover in AlwaysOn Availability Groups is a way in which the roles of primary and secondary availability replicas are interchangeable. Failover in Availability groups occur at the level of availability replica, with the secondary replica as the target.
Always Keep a SQL File Recovery Tool Handy
While a proper implementation of AlwaysOn Availability group can go a long way in ensuring business continuity, they are by no means completely foolproof. In some cases, specific instances may not failover properly or some records may end up missing. In such scenarios, you can use a
recover sql tool like DataNumen SQL Recovery to extract the missing records. This tool is designed to handle most extensive cases of SQL corruption with ease and can also be used bring back accidentally deleted records too.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com