What is Always On Availability Group
Certifications:
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has on an Oracle database - 1 November 2024
- How we enable Unified Auditing in Oracle Database - 7 October 2024
The Always On Availability Group is the feature that Microsoft provides us in SQL Server To make Replicate our bases on up to eight Secondary Nodes (servers) as a solution for High Availability, Disaster Recovery and for reading without occupying the Primary Node.
Secondary copies of the databases can be accessed for reading, maintenance procedures such as Backup, integrity checks of the databases can be carried out there and finally in case of a catastrophic event (Disaster) we can failover it.
To create an Always On Availability Group, require each Node (server) that we want to have a copy of the databases as a Secondary to belong to a Windows Server Failover Cluster (WSFC) along with Primary.
In case we want to have the possibility for automatic failover in Secondary we should have installed to Quorum of Cluster and one Witness. A Witness can be a shared folder, shared disk, or Azure Storage.
The job of the Witness is to communicate with all the Nodes and in the event that one of them fails or the communication between them is lost, to vote for the health of the Cluster so that it continues to be up and running.
What options do we have for database replication?
When modifying the Availability Group we choose it availability mode. In the availability mode, it is defined whether the replication is done synchronously or asynchronously. The differences between them are as follows:
Asynchronous-commit
When we have chosen asynchronous commit, our Primary commits the transaction without first waiting for the transaction to be written to the Secondary database. So with asynchronous commit we don't have delays in the transactions done in Primary. But this way we do not have the guarantee that any transaction that has been done in the Primary has had time to be written in the Secondary, which means that we may lose data.
Synchronous-commit
When we choose synchronous commit, our Primary waits before committing a transaction to be first written in the log of the Secondary database. So the synchronous commit guarantees me that no transaction made in the Primary has been lost from the Secondary. This option, however, brings delays in the time the transactions are completed and should be avoided if we have network problems.
What Failover options do we have?
If for some reason e.g. Disaster we want to failover to a Secondary, depending on the type of synchronization we have chosen, we have different failover options that we can choose.
Manual failover
In the case of manual failover, the process of changing the roles from Secondary to Primary and vice versa is done manually by the administrators. If we have chosen Secondary to be updated simultaneously then there is no possibility of losing data. On the other hand, if the Secondary is updated asynchronously, then the manual failover becomes a force and there is the possibility of losing data from transactions that had not had time to be written to it.
Automatic failover
In order to choose automatic failover, the Secondary must be updated synchronously and we must have defined a Witness in the Windows Cluster Quorum. In this case, in case of failure of the Primary, the Secondary automatically becomes the Primary without the possibility of data loss and when the former Primary comes back, it is defined as the Secondary and the synchronization continues automatically.
What are Basic Availability Groups and how do they differ from Always On Availability Groups
In case the version of SQL Server is Standard and not Enterprise then we cannot make use of Always On Availability Groups, but we have them instead Basic Availability Groups.
In Basic Availability Groups we still have the option to choose for synchronous or asynchronous commit in replication and the option for automatic failover if something happens to our Primary Node. But we have the following limitations:
- In each Basic Availability Group we can have only one individual basis per Group (but we can have many Groups)
- We can only have up to 2 Nodes (Primary and Secondary) with a single copy of the base
- The standby base cannot have read access
- We can't get the backups on standby
- We cannot run standby database integrity checks
