Microsoft SQL Server Documentation

Always On Availability Groups

Always On Availability Groups (AGs) are a high-availability and disaster-recovery solution that provides an enterprise-level of data protection for a set of user databases, known as availability databases. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases.

Key Components and Concepts

• Availability Replicas: Instances of SQL Server that host copies of the availability databases. These can be on the same server instance or different server instances.
• Availability Databases: A set of user databases that fail over together as a single unit.
• Listener: A virtual network name (VNN) and virtual IP address (VIP) that clients connect to. It automatically routes connections to the current primary replica.
• Failover: The process of switching from a primary replica to a secondary replica.
• Automatic Failover: When the primary replica becomes unavailable, a secondary replica can automatically take over as the new primary without any manual intervention.
• Manual Failover: Initiated by an administrator when planned maintenance or other scenarios require switching the primary role.
• Forced Failover: A last-resort failover where a secondary replica becomes the primary, even if data loss is possible.
• Availability Modes:
  • Synchronous Commit: Ensures that transactions are committed on both the primary and secondary replicas before the transaction is acknowledged to the client. Provides zero data loss.
  • Asynchronous Commit: Transactions are committed on the primary replica and then sent to the secondary replicas. This offers lower latency but carries a risk of data loss.
• Failover Modes:
  • Automatic: Supports synchronous-commit availability mode.
  • Manual: Supports both synchronous-commit and asynchronous-commit availability modes.
  • Forced: Supports asynchronous-commit availability mode only.
• Readable Secondaries: Secondary replicas can be configured to allow read-only access to their databases, offloading read-only workloads from the primary.

Benefits of Always On Availability Groups

• High Availability: Minimizes downtime by providing automatic or manual failover to a secondary replica.
• Disaster Recovery: Protects against site-level failures by replicating data to geographically separate locations.
• Improved Read Scalability: Offloads read-only workloads to readable secondary replicas.
• Faster Application Recovery: Reduces the time required to bring applications back online after a failure.
• Simplified Management: Offers a consolidated view and management interface for availability groups.

Prerequisites and Considerations

• SQL Server Enterprise Edition is required for Always On Availability Groups. (Standard Edition supports basic Availability Groups with fewer features).
• Windows Server Failover Clustering (WSFC) is a prerequisite.
• Network connectivity between all replica instances is essential.
• Shared storage is NOT required for Availability Groups, unlike traditional clustering.
• Each availability database must be in the FULL recovery model.

Configuring an Availability Group

Configuring an Availability Group typically involves the following steps:

1. Ensure prerequisites are met (WSFC, SQL Server editions, recovery model).
2. Enable the Always On Availability Groups feature on each participating SQL Server instance.
3. Create a new Availability Group using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).
4. Add databases to the Availability Group.
5. Configure availability replicas (endpoints, availability modes, failover modes).
6. Create an Availability Group Listener for client connectivity.
7. Configure backup preferences for secondary replicas.

T-SQL Example: Creating an Availability Group

CREATE AVAILABILITY GROUP MyAG
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    DB_FAILOVER = ON,
    DTC_SUPPORT = NONE
)
REPLICA ON
    'ServerA' WITH (
        ENDPOINT_URL = 'TCP://ServerA.contoso.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 60,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
    ),
    'ServerB' WITH (
        ENDPOINT_URL = 'TCP://ServerB.contoso.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE)
    );
GO

ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'ServerA' WITH (PRIMARY_ROLE = (READ_WRITE, PRIMARY_BETA = 10));
GO
            

Monitoring and Troubleshooting

Effective monitoring is crucial for maintaining the health of your Availability Groups. SQL Server Management Studio provides dynamic management views (DMVs) and built-in dashboards for this purpose. Key DMVs include:

• sys.dm_hadr_availability_group_states
• sys.dm_hadr_availability_replica_states
• sys.dm_hadr_database_replica_states
• sys.dm_hadr_cluster_members

Further Reading

• SQL Server Disaster Recovery Strategies
• SQL Server Management Studio (SSMS)
• Failover Cluster Instances (FCI)