When Microsoft SQL Server 2016 AlwaysOn Availability Groups are employed, it is possible that a secondary replica can be made readable. The read-only access to a secondary replica is useful if your read-only workloads can tolerate some data latency. In situations where no data latency is acceptable, the most appropriate option is to run read-only workloads against the primary replica.

When a secondary replica is configured for read access, the read-only workloads on the secondary replica consume system resources, such as CPU, memory, and I/O, from the read queries as well as the redo threads. Take this into account when planning the overall design from an ESXi host workload perspective.

The workload on the secondary node is often completely different from the workload on the primary node. Typically, read-only queries are more analytical in nature. They usually generate a high number of sequential large block I/Os and do more aggregation operations, which can consume a large amount of CPU and memory. Database analytic type queries can also benefit from parallel processing threads, and therefore, additional vCPUs.

Due to the workload on a read-only secondary replica often being completely different than the workload on the primary, it might be necessary to configure additional indexes to get to optimal query plans. If additional indexes are required, they must be created on the primary replica to be transferred subsequently to the secondary replica. Plan for additional disk space, if needed, to accommodate the new indexes.

Finally, consider the impact of enabling read access on a secondary replica, as well as the characteristics of a read-only workload when capacity planning. Depending on your database characteristics, a readable secondary replica could demand more resources than the primary replica.