What is AlwaysOn
in SQL Server?
AlwaysOn is a High Availability (HA) and Disaster
Recovery (DR) feature in SQL Server, introduced in SQL Server 2012
(Enterprise Edition).
Always On Availability Groups (AG) do not require shared disk storage, making them a flexible and storage-independent high availability (HA) solution for SQL Server. This feature has traditionally been available only in the Enterprise Edition.
For versions prior to SQL Server 2016, Always On AG is not supported on the Standard Edition. However, starting with SQL Server 2016 Standard Edition, Microsoft introduced the Basic Availability Group (BAG), offering a limited version of the Always On AG feature.
Installation Note:
When installing SQL Server for an Always On configuration, you must choose:
"New SQL Server stand-alone installation"
This sets up the necessary SQL Server instance required for participation in an Availability Group.
High Availability and Disaster Recovery (HA/DR):
An Always On AG setup for HA and DR commonly uses "Node Majority" quorum mode in a Windows Server Failover Cluster (WSFC). This setup ensures that a majority of cluster nodes must be online for the cluster (and AG) to function, improving resilience and failover reliability.
It offers:
·
Automatic failover between servers
·
High uptime (99.99% or more)
·
Support for readable secondary replicas
·
Better performance than older methods (like
Database Mirroring)
Two main features:
1. AlwaysON Availability
Groups (AGs)
·
Allows multiple databases to fail over as
a group
·
Supports up to 9 replicas (1 primary + 8
secondary)
·
Secondary replicas can be read-only (offloading
reporting queries)
·
Requires Windows Server Failover Clustering
(WSFC)
2. AlwaysON Failover Cluster
Instances (FCI)
·
Whole SQL Server instance runs on a
Windows Cluster
·
Uses shared storage
·
Only one instance runs at a time
·
Similar to older failover cluster setups
Feaures: Always
On Availability Groups
|
Feature |
Description |
|
Primary
Replica |
Active,
read-write database (client-facing) |
|
Secondary
Replica(s) |
Can be
used for read-only queries, backups, DR |
|
Synchronous
Mode |
Real-time
commit to secondary (supports automatic failover) |
|
Asynchronous
Mode |
Fast
write on primary, secondary lags (DR sites) |
|
Automatic
Failover |
Yes (if
synchronous + health monitoring) |
|
No Shared
Storage |
Each node
has its own storage (unlike FCI) |
Example
Scenario
·
You have a SalesDB with critical data
·
It’s hosted on a Primary Node in Mumbai
·
You create a Secondary Replica in Delhi
·
Both are in an Availability Group
·
If Mumbai fails, Delhi takes over automatically
Key
Benefits
|
Benefit |
Explanation |
|
High
Availability |
Instant
failover if primary crashes |
|
Disaster
Recovery |
Async
replicas can be across regions |
|
Read
Scale-Out |
Route
reporting queries to secondaries |
|
Backup
Offloading |
Take
backups from secondary to reduce primary load |
|
No Shared
Storage Needed |
Uses
local disks → works on cloud + on-prem |
Requirements
& Limitations
|
Requirement |
Description |
|
Enterprise Edition required |
For full Always On AG support |
|
WSFC Cluster setup |
Needed for monitoring and failover control |
|
Same DBs on all nodes |
Only selected databases are replicated |
|
Not available in Express/Std (fully) |
Std supports Basic AG (1 DB, 1 secondary) |
Real-World
Use Case
A bank runs SQL Server Enterprise with:
·
CustomerDB on a Primary node
·
2 Secondary replicas: 1 for DR, 1 for read
reporting
· Writes go to primary, reports go to secondary
· Auto-failover between primary and DR
AlwaysOn Basic Availability Group (AlwaysOn BAG)
Starting with SQL Server 2016 Standard Edition, the Always On Basic Availability Groups (BAG) feature is available. While it shares a similar setup and management approach to Always On Availability Groups (AG) found in the Enterprise Edition, it offers only a limited subset of features.
Key differences:
-
BAG supports only two replicas: one primary and one secondary.
-
Supports a single database per Basic Availability Group.
-
Lacks advanced features like readable secondary replicas, automatic page repair, or backup from secondary.
This feature was introduced to replace Database Mirroring, which is now deprecated. Always On BAG provides high availability for lightweight scenarios in smaller environments or where Enterprise Edition is not licensed.
AlwaysOn
Distributed Availability Group (AlwaysOn DAG)
Always On Distributed Availability Groups (DAGs) are loosely coupled groups that span two independent Availability Groups (AGs). Each AG is hosted on a separate Windows Server Failover Cluster (WSFC), with its own quorum and voting configuration.
This architecture allows the secondary replicas to be located in a different geographic region from the primary. A typical use case includes enabling read-only workloads in remote sites while minimizing the risk of network-related disruptions at the secondary site impacting the primary system.
In short, DAGs provide high availability across geographically distributed data centers with better isolation and fault tolerance.
0 Comments