SQL Server Availability Group in AlwaysOn
In earlier article we learn about the “requirements/ prerequisite for SQL Server AlwaysOn Configuration”. Now lets see about the SQL Server Availability Group.
SQL Server Availability Group is an alternative to database mirroring.
In SQL Server, Availability Group is a collection or group of user databases which failover together. Databases participated in this are called as Availability Databases. In mirroring, replicas are limited to principle and mirror database. However, in availability group, it supports read-write primary databases. It also supports multiple secondary databases which are accessible for read-only operations.
Database failover is not related to individual database issue such as database file or transaction log file corruption. It is related to the issues at a SQL server instance level. Failover occurs on per replica basis and all the databases in the SQL server Availability group replica fail over.
Availability Modes
Availability Modes of SQL Server Availability Group depends on data loss and transaction latency requirements. There are two types of availability modes.
Asynchronous-commit mode
In your SQL Server Availability Group, if replicas are placed at geographically dispersed locations, you must use Asynchronous-commit mode. When you configure a secondary replica with Asynchronous-commit mode, the primary does not wait for the secondaries to write log records to disk. It will run with minimum transaction latency.
If you configure the primary replica with Asynchronous-commit mode, then the transactions for all replicas will be committed asynchronously. This happens irrespective of the mode of each secondary replica.
Synchronous-commit Mode
In Synchronous-commit mode transaction latency is more, but it minimizes the chance of data loss when automatic failover happens. Each transaction is applied to secondary before being written to the local log file. The primary database always verifies that the transaction has been applied to the secondary. After that, it enters into the Synchronized state.
Failover Modes
As we already discussed, in SQL Server Availability groups failover at availability replica level. When failover happens one of the secondary replica becomes a primary and original primary replica becomes secondary replica. SQL Server Availability groups supports three types of failover modes.
Automatic Failover
Automatic failover occurs without manual intervention. There will be no data loss occurs during this type of failover. It is supported only if the current primary and at least one secondary replica are configured with automatic failover.
The most important point is automatic failover can occur only if the primary and secondary replica are in synchronous-commit mode.
Planned manual Failover
In planned manual failover, failover is triggered by the administrator. This type of failover is used in case of maintenance activities. There will be no data loss in this type of failover. To perform planned manual failover at least one of the secondary need to be in Synchronized state.
You can perform planned manual failover only if the primary and secondary replicas are in synchronous-commit mode.
Forced Manual failover
Forced Manual failover involves the possibility of data loss. Use this type of failover when none of the secondary replicas are in a synchronized state. You should also use it when the primary replica is unavailable.
If asynchronous-commit mode is used on the primary, then forced manual failover is used. The same applies if the only available replica uses asynchronous-commit mode.