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.
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.
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:12345678910USE [master]GOALTER AVAILABILITY GROUP [AG-MyNewDB-Demo]MODIFY REPLICA ON N'SQLNODE1\INST1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))GOUSE [master]GOALTER 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 Yes12345678910USE [master]GOALTER AVAILABILITY GROUP [AG-MyNewDB-Demo]MODIFY REPLICA ON N'SQLNODE1\INST1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))GOUSE [master]GOALTER 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
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
- Lift and Shift SSIS packages using Azure Data Factory V2 - April 23, 2021
- Azure Automation – Automate Pause and Resume of Azure Analysis Services - April 20, 2021
- Direct Query Mode in Power BI for Azure Analysis Services - April 19, 2021