Rajendra Gupta

Make the most of secondary replicas in SQL Server Always On Availability Groups

October 13, 2020 by

In this 31st article of the SQL Server Always On Availability Group series, we will explore how you can use the secondary replica SQL database for your workloads.

Introduction

In a SQL Server Always On Availability Group, we configure two or more instances for high availability and disaster recovery. We configure the SQL listener to connect to the primary replica. We can use automatic or manual failover in case of any issue on the primary replica. It promotes the secondary replica as a primary replica and provides application connectivity.

What is the role of the secondary replica apart from providing HADR benefits? In this article, we will cover how we can make the most of the secondary replica SQL database.

Environment details

In this article, we have two node availability group replicas in the synchronous commit mode. You can refer to earlier articles in the series to create a similar environment.

  • Primary Replica: SQLNode2\INST1
  • Secondary Replica: SQLNode1\INST1
  • Database: [MyNewDB]

SQL Server Always On Availability Group setup

Use Secondary replica SQL database for read-only connections

By default, the secondary replica SQL database does not allow both read-only and read-write connections. You can verify the secondary replica connection status using the AG group properties. On the property page, check the value for the Readable Secondary.

The secondary replica property is applicable for both Synchronous and Asynchronous commit availability mode.

  • Readable secondary: No – It does not allow any user connections for the secondary replica. It is the default configuration

    Readable secondary

    We can change the Readable secondary to the following values

  • Read-intent only: In this mode, the secondary database allows the read-only connections. Users with the appropriate access can read data

    Read-intent only

    Alternatively, you can use the ALTER AVAILABILITY GROUP statement as specified below:

    In the read-intent secondary mode, if we directly connect to the secondary database, you get the following error because, by default, all connections are read-write in SQL Server

    read-write connections

    To connect with the secondary database for read-only connections, specify the argument ApplicationIntent=ReadOnly in the additional connection parameters. If you do not specify the ReadOnly argument in the connection string, you cannot use the secondary database for data read.

    ApplicationIntent property

    Now, you can explore the secondary database for the read-only connections and execute the select statements as shown below:

    secondary database for the read-only connections

  • Readable Secondary- Yes

    In this mode, the secondary replica allows all types of connections, but you can only read data from the secondary SQL database

    Readable Secondary- Yes

    Alternatively, use the following SQL statement to change the Readable Secondary to Yes

Configure Read-only routing for secondary replica SQL database

We can configure the Read-Only routing from SQL Server 2016 onwards to route the read-only connections to the secondary replica configured for the readable connections. We must use a SQL Listener before configuring the read-only routing. The application connects to the availability group using the listener, and the connection string must use the ApplicationIntent=ReadOnly for the connection. In this method, we use the read-only routing URLs so that the SQL listener redirects the read intent connections to the secondary replica. It does not serve those requests from the primary replica even you make connections to the primary replica using the listener.

In a high-level, you can view the read-only routing in the below image:

  • Peter connects to the listener using the default connection string (read-write); therefore, it connects to the primary replica
  • Sundar also connects to the listener but specified the ApplicationIntent=ReadOnly in the connection string. The listener redirects the connection to the secondary readable replica and uses the secondary SQL database for the read-only purpose

Read-only routing

You can use the article, How to Configure Read-Only Routing for an Availability Group in SQL Server 2016 for defining read-only routing for the secondary replica.

Configure the secondary replica in SQL Server Always On Availability Groups for the database backups

We can use the secondary replica for specific SQL database backups as well. Many times, SQL database backups cause significant pressure on the IO and CPU due to backup compression. In this case, the secondary replica can help to minimize the load on the primary replica, and it can serve the application requirements without any issues.

Let’s see what kinds of database backups are supported on the secondary replica.

  • Full Backup: We can take copy-only full database backups from the secondary replica. Sometimes, developers ask database professionals to restore the database copy in the lower environments. We can use the secondary replica for a copy-only backup. It does not impact the LSN or the differential bitmap

    You can read more about the copy-only backup in this article, Understanding SQL Server Backup Types

  • Differential backup: You cannot take differential backup on the secondary replica
  • Transaction log backup: The secondary replica supports the regular transaction log backup. The Copy_Only option is unsupported on the secondary replica. SQL Server Always On Availability Group ensures a consistent log chain (log sequence number) whether we take the log backup from the primary or the secondary replica

You can understand the log backup process from the secondary replica in the following image.

secondary replica in SQL Server Always On Availability Groups for the database backups

As we run frequent log backup for the critical databases, it is useful to schedule those backup from the secondary replica SQL databases. We can take log backup in both Synchronous and Asynchronous commit mode.

  • Note: Your replicas should be in Synchronized or Synchronizing state for taking backups from the secondary replica

In the article, SQL Server Always ON Availability Group Log Backup on Secondary Replicas, I demonstrated that the SQL Server maintains the log sequence number chain irrespective of the backup from the primary and secondary replica.

In the Availability Group properties, navigate to the backup preference and configure the preference for your database backups.

  • Prefer Secondary: It is the default configuration and allows the automatic backups to occur on the connected secondary replica. However, if the secondary replica is not working, it takes backup on the primary replica
  • Secondary Only: In this option, the automated backup occurs on the secondary replica. It does not take the backup on the primary replica
  • Primary: If we require all automated backups from the primary replica, we should use this option
  • Any Replica: In this option, we can define the backup policy according to the replica backup priorities. By default, SQL Server allocates the same priority for all replicas. We can set the backup priority for a replica between 1(lowest) and 100 (highest). Once we execute the backup, SQL Server checks the replicas’ priorities and takes the backup on the highest priority replica. In case that replica is unavailable, it takes backup on the next priority replica

Backup options

Suppose we have multiple secondary replicas situated in both primary and secondary data centers. We can combine the synchronous commit and asynchronous commit for multiple replicas. The secondary data center is located at a considerable distance and has limited network bandwidth. Due to this reason, we used the asynchronous commit, and there might be some data latency between primary and secondary replica.

In this case, we might not want our backups to run on the specific secondary replica’s. You can exclude the replica, and SQL Server does not take automated backups on an excluded replica.

You can refer these SQL Server Always On Availability Group backup options using the Understanding backups on Always On Availability Groups – Part 1 and Understanding backups on Always On Availability Groups – Part 2 articles

Conclusion

In this article, we explored the ways to make the most of the secondary replica SQL databases in the SQL Server Always On Availability Group. In this way, we can offload the primary replica, and it can serve the application effectively.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Group
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views