SQL high availability (HA)

SQL databases are essential components of software applications that should have a level of redundancy. Without adequate redundancy, SQL database can be a single point of failure. To assist, Microsoft SQL servers (MSSQL) has native supports disaster-recovery capabilities such as Replication, Mirroring (AlwayON) and Clustering.

Replication– One primary with many secondary read-only servers.
– Instance level failover
Clustering
(AlwaysON)
– Needs at least two servers in cluster to be effective.
– Good solution for zero downtime
Mirroring Deprecated (should avoid using)

SQL Clustering

Clustering is the logical grouping of two or more SQL instances that enables a group or instances to create high availability and sometimes increased performance (when using read replicas). Instances in a cluster are known as nodes, each cluster has a dedicated primary node and secondary replicas. At any one time, there is only one primary node that applications interact with. Changes to the primary node are replicated to the replica nodes.

MSSQL has many high availability and disaster recovery solutions based off of clustering. For example, AlwaysOn is a solution introduced in SQL server 2012 and consist of two similar technologies build on top of Windows Server Failover Clustering (WSFC) (source):

AlwaysOn Failover Clustering Instances (AlwaysOn FCI)
  • This solution requires shared disk storage such as a storage area network (SAN)
  • Provides Server Instance level HA
Fail over cluster instances showing primary SQL instances and secondary instances. Both instances have shared storage (same underlying data store).
AlwaysOn Availability Groups (AlwaysOn AG)
  • Applications connect via AG listener
  • Secondary replicas instances on AG must be on a separate SQL server instance and separate physical node.
  • Only a single SQL Server instance may be the primary replica of an AG
  • Database level HA
AlwaysOn AG showing two AG groups and AG Listeners. Each blue block is a separate SQL server instance. Primary database nodes can exists in any of the nodes of an AG.

Note: SQL Server 2016 introduced two variants on AlwaysON AG: “AlwaysOn Basic AG” and “Always on Distributed AG”

To check if a SQL instance has AlwaysON, user the query below:

-- Check if AlwaysOn is enabled on server
SELECT CASE WHEN SERVERPROPERTY ('IsHadrEnabled') = 1 THEN 'ON' ELSE 'OFF'END AS AlwaysON;

Availability Group

Always On Basic Availability Groups replaces the deprecated Database Mirroring feature. Nodes that are placed into AG are kept synchronized with the primary node and are potential candidates for primary node promotion during a cluster instance fail-over. At any given time there is always only one primary node which the AG Listener is directing traffic. Secondary nodes can be used as read-only replicas to improve read-only SQL operations. Below can be used to query AG details (source).

select * from sys.availability_groups
select * from sys.availability_databases_cluster
select * from sys.availability_group_listener_ip_addresses
AlwaysOn AG setup, example of database level HA. Database in primary AG group on SQL02 and the same database shown as secondary AG on SQL01. Assuming there are additional SQL instances all will show database in secondary AG

source – what is AlwaysOn SQL server
source – Failover clusters
source – SQL disaster recovery

Leave a Reply

Your email address will not be published. Required fields are marked *