Always On Availability Group (AG) is a High Availability, Enterprise-Level disaster recovery solution introduced by Microsoft SQL Server 2012 (11. x ) as an alternative solution to database mirroring; it supports a replicated database environment for a discrete set of user databases, known as availability databases, in the enterprise.
You can create an availability group of high availability (HA) group databases or read-scale group databases:
- A high availability (HA) group is a database group that would fail over together.
- A read-scale availability group is a group of databases copied to other instances of SQL Server for a “read-only” workload.
An availability group support one set of primary databases (read/write) and one – eight sets of corresponding secondary databases (read-only). You can set availability replicas for each set availability group for the single primary replica or one-eight secondary replicas. The primary replica makes the primary databases available for users to perform “read and write” operations and updates the transaction records to secondary databases for data synchronization. With an availability group, a secondary database can be suspended without affecting other secondary databases. Similarly, a primary database can be suspended or failed without affecting other primary databases.
The key benefits of Always using On AG for SQL Database failover include
•It supports nine available replicas, one primary database (Read/Write) and eight secondary databases (Read-only).
•It supports automatic failover and manual failover, in which manual failover mode would be preferred.
•During disaster recovery, it provides read access and backup operations to the secondary replicas, ensuring business continuity and preventing data loss.
•It supports data encryption and compression, ensuring data integrity during data transfer.
•It provides integrated management tools for deploying and managing availability groups.
NB: Deployment of Always On Availability Group for high availability on Windows requires a Windows Server Failover Cluster (WSFC). Each availability replica of an availability group must reside on a different node of the same WSFC.
Connection Recommendation for MS SQL 2012
•Always On-aware the client library. Use a client library that supports tabular data stream (TDS) protocol version 7.4 or newer to provide the client site functionality for Always On features. Client library includes Data Provider for SQL server in .NET Framework 4.5 and the SQL Native Client 11.0.
NB: From Etere version 28.1 onwards, .NET Framework 4.5 is required.
•In the connection provider property, set the “MultiSubnetFailover” to “True” in the connection string to enable client libraries to attempt to connect to all IP addresses in parallel that are registered for AlwaysOn Availability Group listener that has Ian P address in multiple subnets.
•In the connection provider property, set the “ApplicationIntent” to “ReadOnly” to offload read-only workloads from primary to secondary replicas.
•Avoid legacy client connections. Legacy client database libraries do not support parallel connection attempts; in a multi-network environment with multiple IP addresses, it would establish a link to each IP address sequentially till a connection is established or TCP timeout. The connection timeout on legacy clients should be adjusted to at least 15 – 21 seconds for each replica to accommodate the potential sequential timeout and retries to cater for environments with multiple IP addresses.