SQL Server – AlwaysOn Availability Groups

Varinder Sandhu SQL, SQL-Advanced, SQL-Server 2012, Varinder Sandhu

Introduction

Microsoft introduced the AlwaysOn Availability Groups which is a high-availability and disaster-recovery solution that provides an enterprise-level solution in SQL Server 2012.

Using Database Mirroring for local high availability and combining it with Log Shipping for a disaster recovery solution is popular deployment architecture prior to SQL Server 2012. With SQL Server 2012, the Database Mirroring and Log Shipping solution can be replaced with an Availability Group solution with multiple secondaries.

AlwaysOn Availability Groups – Deployment Architecture

SQL Server – AlwaysOn Availability Groups

The whitepaper http://msdn.microsoft.com/en-us/library/jj191711.aspx provides architecture details and best practices for this solution.

Enhancement to SQL Server 2014 AlwaysOn Availability Groups

Enhanced Availability for Read-Only Replicas: With SQL Server 2014, read workloads not impacted during network failures (or primary down, or cluster quorum loss). Also secondary replicas can be used for reporting and backup.
Increased Number of Replicas: Maximum number of secondaries increased from 4 to 8 (but Max 2 sync secondaries for high availability)
Add Azure Replica Wizard: On-premises SQL Server instances can use the new Windows Azure configuration options in the AlwaysOn Availability Group wizard to create one or more asynchronous secondary replicas on Windows Azure Infrastructure as a Service (IaaS) services.
Enhanced Diagnostics: SQL Server 2014 AlwaysOn Availability Groups diagnostic and troubleshooting message display has been improved.

Comparison of Failover Cluster Instances and Availability Groups

Description	Nodes within an FCI	Replicas within an availability group
Uses WSFC cluster	Yes	Yes
Protection level	Instance	Database
Storage type	Shared	Non-shared
Storage solutions	Direct attached, SAN, mount points, SMB	Depends on node type
Readable secondaries	No	Yes
Applicable failover policy settings	WSFC quorum FCI-specific Availability group settings	WSFC quorum Availability group settings
Failed-over resources	Server, instance, and database	Database only