In this 31^st 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]

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

  

  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

  

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

  1 2 3 4 5 6 7 8 9 10

  USE [master] GO ALTER AVAILABILITY GROUP [AG-MyNewDB-Demo] MODIFY REPLICA ON N'SQLNODE1\INST1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)) GO USE [master] GO ALTER AVAILABILITY GROUP [AG-MyNewDB-Demo] MODIFY REPLICA ON N'SQLNODE2\INST1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)) GO

  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

  

  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.

  

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

  

• 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

  

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

  1 2 3 4 5 6 7 8 9 10

  USE [master] GO ALTER AVAILABILITY GROUP [AG-MyNewDB-Demo] MODIFY REPLICA ON N'SQLNODE1\INST1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)) GO USE [master] GO ALTER AVAILABILITY GROUP [AG-MyNewDB-Demo] MODIFY REPLICA ON N'SQLNODE2\INST1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)) GO

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

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.

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

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

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023