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

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: SQLNode2INST1
  • Secondary Replica: SQLNode1INST1
  • 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

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Author: admin

Leave a Reply

Your email address will not be published.