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)|
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
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
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;
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 in 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