Making the most out of your secondary replica for SQL Server AlwaysOn Availability Groups

By: Carla Abanes | Updated: 2014-06-26 | Comments (5) | Related: > Availability Groups

Problem

From this previous tip, we have learned how to setup and configure AlwaysOn for SQL Server 2012. You have an active primary replica and a passive secondary replica, which is the most common configuration in a clustered setup. How do you offload certain tasks and conserve resources on your primary replica and make the most out of your secondary replica?

Solution

In the past, most high availability solutions would involve an active-passive configuration wherein the secondary node is idle when the primary replica is active. SQL Server 2012 AlwaysOn changes that because in an AlwaysOn configuration, your secondary replica can be more than an idle server. It can be in a read only mode, which can handle queries thereby offloading some work from your primary replica.

One big advantage of SQL Server 2012 AlwaysOn setting is that your secondary replica can be read only and can also handle SQL Server database backup jobs. With AlwaysOn active secondary replicas, you can use secondary hardware to perform backups and other resource intensive read only queries. Idle hardware is no longer a factor when you choose a SQL Server high availability solution.

Active secondary replicas overcome the shortcomings of database mirroring, introduced in SQL Server 2005, because you can use a secondary copy of the database to perform backups, freeing your primary server of these workloads.

SQL Server Database Backups on Active Secondary Replica

SQL Server database backups can be performed on an active secondary replica with the copy_only option for full database, file, filegroup and transaction log backups. You can configure an availability group to specify where backups can be performed. To do this, set the WITH AUTOMATED_BACKUP_PREFERENCE option of the CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUP T-SQL statements. You can script these backup jobs for this setting when you choose where your preferred replica backups are executed. The valid values for WITH AUTOMATED_BACKUP_PREFERENCE options are:

PRIMARY
SECONDARY_ONLY
SECONDARY
NONE

Alternatively, you can configure this option when running the New Availability Group wizard for setting up your availability group. This screen is on the Specify Replicas dialog window and on the Backup Preferences tab. See the image below:

You can also use the BACKUP_PRIORITY option of the CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUP T-SQL statements to specify the backup location. If this option is set to 0, the replica is not chosen to perform backups. Maintenance plans and log shipping automatically use specified backup preferences.

Restrictions of readable secondary replicas in SQL Server 2012 AlwaysOn Availability Groups

Change tracking and change data capture are not supported on a secondary database that belongs to a readable secondary replica in SQL Server 2012 AlwaysOn Availability Groups. If any active transactions exist on the primary database when the readable secondary replica joins the availability group, row versions are not fully available immediately on the secondary database. Queries are temporarily blocked until the active transactions on the primary replica that existed when the secondary replica was configured are committed or rolled back.

Clean up of ghost records on the primary replica can be blocked by transactions on one or more secondary replicas. Ghost records are cleaned up only if no secondary replicas need them any longer. DBCC SHRINKFILE may fail on the primary replica if the file contains ghost records that a secondary replica still needs.

Configuring Secondary Replicas in SQL Server 2012 AlwaysOn Availability Groups

You can configure a secondary replica in SQL Server 2012 AlwaysOn Availability Groups by using CREATE or ALTER AVAILABILITY GROUP T-SQL statements. You can set ALLOW_CONNECTIONS property to one of the following;

NONE: No direct connections are allowed to the databases in the secondary replica.
READ_ONLY: Connections are allowed provided that the application intent property of the connection specifies ReadOnly.
ALL: All connections are allowed to the database in the secondary replica for read-only access, even of the ApplicationIntent property is not specified.

An alternative way to configure this property and verify your current settings are from reviewing the properties for your Availability Group. You can view this screen from your Availability Group in SQL Server Management Studio.

Application Connectivity in SQL Server 2012 AlwaysOn Availability Groups

Applications can specify the purpose of the connection through a new property of the connection string called Application Intent. This property specifies whether the connection is directed to a read write or read only version of the Availability Group databases.

When connecting to an Availability Group replica, clients connect either directly to the SQL Server instance name hosting the read-only database or by using the Availability Group listener name, which allows for read-only routing to an available readable secondary replica.

Read-only routing refers to the ability of the SQL Server to route incoming connections to an Availability Group listener to a secondary replica that allows read-only workloads. An Availability Group listener must be specified in order to use read-only routing and the following conditions must be true:

Application Intent of the incoming connection is READONLY.
ALLOW_CONNECTIONS property of the read-only replica is READ_ONLY.
READ_ONLY_ROUTING_URL for each replica is set by the CREATE or ALTER AVAILABILITY GROUP T-SQL statements.
READ_ONLY_ROUTING_LIST option must be set for each replica in the CREATE or ALTER AVAILAILITY GROUP T-SQL statement, as part of the PRIMARY_ROLE replica options. The READ_ONLY_ROUTING_LIST can contain one or more routing targets.

Multiple routing targets can be configured and the routing occurs in the order that targets are specified in routing list. You may use the following ALTER AVAILABILITY GROUP T-SQL statements.

alter availability group <yourAGnamehere>
modify replica on <FirstInstance> with (secondary_role(read_only_routing_url='tcp://ServerName:1450'))

alter availability group <yourAGnamehere>
modify replica on <SecondInstance> with (secondary_role(read_only_routing_url='tcp://ServerName:1450'))

alter availability group <yourAGnamehere>
modify replica on <ThirdInstance> with (secondary_role(read_only_routing_url='tcp://ServerName:1450'))

alter availability group <yourAGnamehere>
modify replica on <FirstInstance> 
with (primary_role(read_only_routing_list=(<SecondInstance>,<ThirdInstance>)))

alter availability group <SecondInstance>
modify replica on <FirstInstance> 
with (primary_role(read_only_routing_list=(<FirstInstance>,<ThirdInstance>)))

select * from sys.availability_read_only_routing_lists

Monitoring Active Secondary Replicas in SQL Server 2012 AlwaysOn Availability Groups

There are quite a few system views and functions available to query to determine the health of your replicas. These queries require a VIEW SERVER STATE permission.

sys.availability_replicas - Returns all Availability Group replicas in each Availability Group in your current instance.
sys.availability_read_only_routing_lists - Returns the read only routing list of each Availability Group replica in an AlwaysOn Availability Group.
sys.dm_hadr_availability_replica_cluster_nodes - Returns all the Availability Group replicas of the AlwaysOn Availability Groups participating in the cluster.
sys.dm_hadr_availability_replica_cluster_states - Returns all replicas participating in your Availability Group and its current join state.
sys.dm_hadr_availability_replica_states - Returns the state and role of each local and remote availability replica participating in the same Availability Group.
sys.fn_hadr_backup_is_preferred_replica - Returns 1 if the passed parameter database name is the current preferred backup location.
sys.dm_hadr_cluster - Returns the cluster name and information about the quorum.
sys.dm_hadr_cluster_members - Returns how many more failures your WSFC cluster can tolerate before losing quorum in a majority node case.

To summarize, your secondary replica can be more than an idle passive server. It can be used to serve read only requests for reporting or dashboard applications as well as it can perform your backup jobs thereby freeing up some resources on your primary replica.

At your next architectural meeting, you can share this information and let your organization know that your secondary replica can be more than just an expensive hardware copy of the primary replica.

Next Steps

Learn more about SQL Server 2012 AlwaysOn Availability Group here

About the author

Carla Abanes is a certified Microsoft SQL Server Database Administrator with extensive experience in data architecture and supporting mission critical databases in production environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2014-06-26

Thursday, September 25, 2014 - 12:14:57 PM - Jim Curry	Back To Top (34717)
I think it is important to note there are licensing implications when using AG secondaries for read-only access or backups.

Thursday, June 26, 2014 - 12:31:56 PM - bass_player	Back To Top (32417)
Great suggestions. One thing to be very aware of is that when you start using secondary replicas for anything other than a standby, you need to pay for licenses. And as far as SQL Server 2012 and higher is concerned, it's more expensive than the previous versions due to the changes in licensing. Great feautres, great suggestions but they all come at a cost.