Database Reference
In-Depth Information
SQL Server AlwaysOn Availability Groups
SQL Server AlwaysOn Availability Groups (AGs) were introduced with SQL Server
2012 and provide a new solution for customers seeking a highly available SQL Server
implementation. SQL Server AlwaysOn AGs differ from SQL Server AlwaysOn FCI in
that they do not rely on a shared disk. A shared disk represent a single point of failure.
SQL Server AlwaysOn AGs utilize replication built in to SQL Server to replicate
between SQL Servers. Although SQL Server AlwaysOn AGs eliminate the single point
of failure at the disk level, they do, at a minimum, double the storage requirements for
your SQL Server implementation. We did say an AG doubles the requirements, but we
did not say it doubles the cost. Storage costs associated with a SQL Server AlwaysOn
AG will change depending on the design implemented.
SQL Server AlwaysOn AG also differs from SQL Server AlwaysOn FCI in that SQL
Server AlwaysOn AG groups databases together and fails these databases over as a
group, whereas SQL Server AlwaysOn FCI works at the SQL Server instance level.
SQL Server AlwaysOn AG is similar to SQL Server AlwaysOn FCI in that it too relies
on Windows Server Failover Clustering (WSFC). Both these solutions require the
Windows Server administrator to create a WSFC instance prior to their creation.
Although they differ in their WSFC requirements and configuration, they are both based
on and require WSFC to be installed and configured.
SQL Server AlwaysOn AG supports up to five availability replicas. A replica is a copy
database, or a group of databases. There is one primary replica and up to four
secondary replicas. The replicas can be configured to support two commit modes.
These modes are synchronous and asynchronous.
Consider using synchronous commit mode when the SQL Servers are well connected
because enabling this option increases transaction latency. Synchronous commit solves
the high availability solution in that every transaction is replicated to all synchronous
members, which at the time this chapter was written was limited to three (the primary
and two secondary replicas). Synchronous commit mode allows for an RPO of zero,
meaning zero data loss.
The asynchronous commit replica should be used when the replicas are separated by
great distance. Asynchronous mode does not have the transactional overhead associated
with the synchronous commit model; however, this means there is a potential for data
loss.
The availability modes—synchronous and asynchronous—can be mixed per SQL
Server AlwaysOn AG. In addition, AlwaysOn AGs provide the ability to create a read-
only connection into the replicas as well as the ability to run backup operations off these
secondary replicas. Be sure to read, understand, and implement the correct licensing
options for the secondary replicas.
