Always On Availability Group - Offloading Reporting Approach

An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and corresponding secondary databases which continuously back up databases and their transaction logs on a regular basis.

 The Source database sends transaction log records to secondary database and then applies them to secondary database. Data synchronization occurs between the primary database and its corresponding secondary database so that changes are applied in real-time (or near-real-time) while users are still in the database. The secondary databases server must have the same specs as the primary databases server. AlwaysOn is implemented on a per-database basis. We don't need to add all databases on source database server. Only those databases which need for real time reporting need to configure for AlwaysOn.

This secondary database can be used for real-time reporting purpose. Also, if we would like to prevent read-only workloads from running on the primary database, we can use secondary database to allow only read-write access for running complex queries.

 Benefits

1. Real time Reporting

2. Enhanced Performance

3. Complex queries can run without compromising database performance.

4. High Availability (HA) with manual failover 

Infrastructure and Software needed for AlwaysOn

1. Windows Server 2012 or later versions.

2. SQL Server 2014 Enterprise edition or later versions

3. Since we can run a query on a secondary replica, all SQL servers need to have license.

4. Both SQL & OS Editions, Versions should be at same level on all participating nodes.

5. All replicas in your AlwaysOn group must be in the same windows domain. They must be able to communicate with each other.

6. SQL Server port must be opened at firewall level for communication between replicas.

7. Ensure that each computer is a Node in a Windows Server Failover Clustering (WSFC)

Cost and Budget

For setting AlwaysOn, a minimum of 2 SQL servers with Enterprise edition (Source database and Secondary database)  are required.

 Analytics workload or read-only queries running on the primary replica can be run on the secondary replica without requiring any changes.