SQL SERVER – How To Improve Performance by Offloading Backups to a Secondary Replica – Notes from the Field #108

December 24, 2015

[Notes from Pinal]: If we get one server, we want two servers, and if we get two servers, we want four servers. If we know we are going to get only two servers, we try our best to get maximum out of our available server. Maximum utilization of resources is always our primary goal. In this blog post we are going to talk about almost the same story where we try to get maximum out of our servers. Let us assume that we have two servers, how do we get maximum performance from them. Well, our generic answer would be that we will keep the most important task on our primary server and move all the not so important task on secondary server. This is common sense and essential too. This is when I reached out to Eduardo and asked him what can we do to make our primary server faster by offloading backups to secondary replica.

SQL SERVER - How To Improve Performance by Offloading Backups to a Secondary Replica - Notes from the Field #108 Eduardo In this 108th episode of the Notes from the Fields series database expert Eduardo Castro (partner at Linchpin People) shares very interesting conversation related to how to improve performance of SQL Server by offloading backups to a secondary replica in SQL Server standard edition.

Microsoft introduced AlwaysOn in SQL Server 2012 as a way to bring a high availability option to scenarios where the database administrator doesn’t have a SAN.

AlwaysOn is based on the concept of availability groups that support the grouping of several user databases that can fail over together to other server in case of an interruption of the main server. Each availability group defines partners known as availability replicas. Each replica is part of the availability group and is hosted in a separate instance of SQL Server.

The following picture shows a basic configuration with one primary and two replicas:

SQL SERVER - How To Improve Performance by Offloading Backups to a Secondary Replica - Notes from the Field #108 108-1

One of the main features of AlwaysOn, besides the high availability scenarios, is the option to have active secondary replicas.

Two common questions I get after my sessions on this topic are:

How to improve OLTP performance in scenarios where there is a lot of reporting being done during peak hours?
How to improve the speed of backups without affecting our main server throughput?

This is where AlwaysOn Active Secondary Replicas come to work. Basically in AlwaysOn, you have the option to use your replicas to distribute the load from the primary server and send the backups and read-only operations to one of the replicas.

If you are creating the AlwaysOn for the first time, you need to configure the backup priority during the Availability Group Wizard. The following picture shows how you can set it up so the backups are run in the secondary replicas. In this way, you can specify that the backups are run in the replica.

SQL SERVER - How To Improve Performance by Offloading Backups to a Secondary Replica - Notes from the Field #108 108-2

If you have already created your AlwaysOn Availability Group, and you haven’t configured where the backups are run, you must alter you group using T-SQL as shown below.

ALTER AVAILABILITY GROUP [@MyOLTPAvailablityGroup] MODIFY REPLICA ON <@MyOLTPInstanceA> WITH (BACKUP PRIORITY = 80)

If you need to automate this task you can create a PowerShell script as shown:

Set-SqlAvailabilityReplica -BackupPriority 80 -Path SQLSERVER: \Data\AvailabiltiyGroups\AvailabiltiyReplicas\&amp;<@ MyOLTPAvailablityGroup &amp;>

In case you want to configure a Read-only secondary after you have created the Availability Group, then you must alter the current configuration to include the read only routing, as show below:

ALTER AVAILABILITY GROUP MyOLTPAvailablityGroup
MODIFY REPLICA ON 'MySQLServerName'
WITH (
SECONDARY_ROLE (
READ_ONLY_ROUTING_URL = 'TCP://address:port' )
)

Once you have run the scripts, you can modify the connection string of the applications that are read-only to include the following parameter ApplicationIntent=Read-only, in this way the Availablity Group will redirect the read-only connections to the proper secondary replica.

Conclusion

If you want to leverage all the potential of AlwaysOn you should consider its high availability features, but the value of spending some time configuring the secondary read-only replicas and backups will also help you balance the request of your systems, optimize the resource usage and speed up your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)