SQL Server 2012 AlwaysOn: Rethinking Database Disaster Recovery

The High Availability Dilemma

Relational database scaling and disaster recovery configurations have traditionally been complex. Database administrators had to choose between:

• ◆Database Mirroring: Failover support at the individual database level, but restricted to a single target and hard to scale.
• ◆Failover Cluster Instances (FCI): High availability at the server level, but requiring expensive Shared Storage Area Networks (SANs) that represent single points of failure.

SQL Server 2012 addresses this with AlwaysOn Availability Groups.

What is AlwaysOn?

AlwaysOn Availability Groups allow database administrators to define a set of user databases that failover together as a single unit. It combines clustering capabilities with database-level replication, without requiring shared SAN storage.

Key architectural features:

• ◆Multiple Target Replicas: Supporting up to four secondary replicas (one primary, up to four secondaries).
• ◆Asynchronous Commit: Used for off-site disaster recovery destinations, preventing network latency from slowing down primary database writes.
• ◆Synchronous Commit: For local automatic failovers without data loss.

Active Secondaries: Offloading Reads

In traditional failover setups, secondary servers sit idle, wasting compute power. AlwaysOn allows secondaries to be accessed for:

1. 1.Read-Only Routing: Directing reporting dashboards and read-only APIs to secondary replicas, saving primary CPU resources.
2. 2.Backups: Offloading database backups to secondary replicas, eliminating production performance overhead.

-- Configuring a read-only routing connection string parameter
Server=ListenerName;Database=SchoolDB;ApplicationIntent=ReadOnly;

AlwaysOn establishes a robust database architecture, ensuring system availability even during datacenter outages.