Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (5)   |   Related Tips: More > AlwaysOn Availability

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:

Specify Replicas

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.

Availability Group Property

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
  • Learn more about AlwaysOn in this webcast


Last Update:






About the author
MSSQLTips author Carla Abanes Carla Abanes works for a private bank in Singapore as a SQL Server DBA.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, September 26, 2014 - 7:52:39 PM - Carla Abanes Back To Top

Hi Kevin,

Yes, these settings are configure on the MS SQL Server 2012 level only. Nothing to set up in your application except if your AG is setup across subnets. You might want to check with your SAN admin for the backup activity and monitor the spikes.

I hope this helps.

 

Carla

 


Thursday, September 25, 2014 - 1:11:22 PM - Kevin Lobo Back To Top

 

Hi Carla,

Great article - great tip. For .NET applications, is this something that is purely a SQL Server setting as you mentioned above or is there some configuration required in the applications as well?

We've installed a few AO setups but during backups we still see massive spikes on the SAN on the primary node?

Thanks in advance,

 

Kevin Lobo


Thursday, September 25, 2014 - 12:14:57 PM - Jim Curry Back To Top

I think it is important to note there are licensing implications when using AG secondaries for read-only access or backups.


Tuesday, September 09, 2014 - 4:39:15 AM - Bart O Back To Top

"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"

Can you explain? Because Microsofts says something different:

"Databases enabled for change data capture (CDC) are able to leverage AlwaysOn Availability Groups in order to insure not only that the database remains available in the event of failure, but that changes to the database tables continue to be monitored and deposited in the CDC change tables."

 

Kinds regards,

Bart

 

 


Thursday, June 26, 2014 - 12:31:56 PM - bass_player Back To Top

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.


Learn more about SQL Server tools