SQL Database Engine High Availability

High Availability for SQL Server Database Engine

Ensuring your SQL Server instances and databases are accessible is critical for business continuity. High availability (HA) solutions minimize downtime and data loss, providing robust mechanisms to keep your applications running.

Key High Availability Technologies

SQL Server offers several integrated technologies to achieve high availability. The choice of technology often depends on your specific RPO (Recovery Point Objective) and RTO (Recovery Time Objective) requirements, as well as your existing infrastructure.

Failover Cluster Instances (FCI): FCI provides instance-level HA by installing SQL Server on a Windows Server Failover Cluster. If a node fails, the SQL Server instance fails over to another node.
- Supports shared storage solutions.
- Provides instance-level protection.
- Requires Windows Server Failover Clustering.
Log Shipping: A simpler disaster recovery solution that periodically sends transaction log backups from a primary server to one or more secondary servers.
- Cost-effective for DR.
- Can be used for read-only reporting on secondaries.
- Higher potential for data loss compared to other HA solutions.
Database Mirroring (Deprecated): A feature that maintains a redundant copy of a database on a separate server instance. While effective, it has been largely superseded by Always On Availability Groups.
- Database-level protection.
- Supports automatic or manual failover.
- Recommended to migrate to Always On Availability Groups.
Always On Availability Groups (AG): The most comprehensive HA and disaster recovery solution for SQL Server. It provides instance-level or database-level availability with automatic failover and readable secondaries.
- Supports multiple databases per Availability Group.
- Offers synchronous and asynchronous replication modes.
- Can span across multiple data centers.
- Available in Enterprise Edition.

Diagram illustrating different SQL Server High Availability options

Overview of SQL Server High Availability Technologies

Choosing the Right Solution

Consider these factors when selecting an HA strategy:

Downtime Tolerance (RTO): How quickly must your database be back online after an outage?
Data Loss Tolerance (RPO): How much data can you afford to lose?
Scope of Protection: Do you need to protect the entire SQL Server instance or just specific databases?
Complexity and Cost: Some solutions are simpler and less expensive to implement than others.
Licensing: Features like Always On Availability Groups are often tied to specific SQL Server editions.

Implementing Always On Availability Groups

Always On Availability Groups are a cornerstone of modern SQL Server HA strategies. The basic steps involve:

Configure Windows Server Failover Clustering (WSFC).
Enable the Always On Availability Groups feature on all participating SQL Server instances.
Create an Availability Group, adding the desired databases.
Configure replicas and endpoints for communication.
Set up listeners for client connectivity.
Configure failover settings (automatic or manual).

Example: Basic Availability Group Configuration Snippet


-- On the primary replica instance
ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON
N'PrimaryServer' WITH (
    ENDPOINT_URL = N'TCP://PrimaryServer.domain.com:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SEEDING_MODE = AUTOMATIC
);

-- On the secondary replica instance
ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON
N'SecondaryServer' WITH (
    ENDPOINT_URL = N'TCP://SecondaryServer.domain.com:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SEEDING_MODE = AUTOMATIC
);

Monitoring and Maintenance