Effective Use of Secondary Replicas of AlwaysON Availability Groups
SQL Server AlwaysON Availability groups were introduced in SQL Server 2012 onwards which provides High Availability and Disaster Recovery solutions for most of the organizations. In AlwaysON Availability Groups at any given point of time there is one server/replica which acts as primary replica which serves both Read/Write operations and there can be one or more secondary replicas which can allow Read_Only operations if required. Along with read_only options there are few maintenance tasks like backups and Integrity checks which can be offloaded to the secondary replicas thus reducing some load of the primary replica.
Below are some of the benefits and effective way of using secondary replica.
– Secondary replicas support read_only workload which will make effective use of Hardware resources on the secondary replica which improves the return on investment on secondary server, unlike Windows failover cluster where the secondary nodes are just passive.
– ReadOnly work load like Reporting can be offloaded to one or more secondary replicas thus reducing the load and locking on in primary server which enhances the performance.
– Read-only workloads use row versioning to remove blocking contention on the secondary databases which will enhance the performance of readonly workload compared to running both read/write and readonly workloads on same SQL Server instance.
– Backups can be performed on secondary replica thus offloading some load from primary replica. Copy_Only Full, File or Filegroup can be performed on secondary replica, however regular transaction log backups can be performed on any of the secondary replica and SQL Server will maintain consistent backup chain across all replicas.
– Integrity checks like DBCC CheckDB can now be run on Secondary replicas. So, you can perform daily checkdbs on secondary replica and can perform weekly checkdb on primary replica over weekends.
– Secondary replica creates and maintains temporary statistics for secondary databases in tempdb, but these temporary statistics can only be created by SQL Server based on the queries being run on the secondary server. SQL Server detects when permanent statistics on a secondary database are stale. But changes cannot be made to the permanent statistics except through changes on the primary database. For query optimization, SQL Server creates temporary statistics for disk-based tables on the secondary database and uses these statistics instead of the stale permanent statistics. When the permanent statistics are updated on the primary database, they are automatically persisted to the secondary database. Then SQL Server uses the updated permanent statistics, which are more current than the temporary statistics.
– Read-only workloads for memory-optimized durable tables access the data in exactly the same way it is accessed on the primary database, by using native stored procedures or SQL Interoperability with the same transaction isolation level limitations. Reporting workload or read-only queries which generally run on the primary replica can now be run on the secondary replica without requiring any changes. Similarly, a reporting workload or read-only queries running on a secondary replica can be run on the primary replica without requiring any changes.
This is applicable on below versions of SQL Server
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.