How to: Configure SQL Server 2012 AlwaysOn – Part 7 of 7

How to: Configure SQL Server 2012 AlwaysOn

Using the Availability Group Listener

In this, final, section of the AlwaysOn blog series I will be explaining the Availability Group (AG) listener and how it can be used to off-load some server processing to secondary replicas.

Cluster Level Operations

From a network perspective the AG listener is quite simple. It is a DNS name, also know as a Virtual Network Name (VNN), with one or more Virtual IP addresses associated to it.

One of the reasons that you must have Windows Server 2008 or higher and SQL Server 2012 to use AlwaysOn AGs is because of the multiple IP configuration and the ability to cross subnets. In Windows Server 2003 all IP addresses had to be functional in order for the cluster resource to stay online due to an AND operator in the logic sequence. In Windows Server 2008 that operator was modified to an OR. Unfortunately, SQL Server 2008 and 2008 R2 still had that same AND operator in place which is why this new functionality of Windows Server 2008 was not usable to SQL Server until 2012 when SQL Server followed suit by changing the operator to an OR as well.

Connection String Attributes

There are two new connection string attributes available in .NET 4.0 and higher (or .NET 3.5 SP1 with a hotfix KB2654347 which is required for SSRS) which can be used when connecting to a AG listener. The first that we will go over is the MultiSubnetFailover which is used for connection performance enhancements and the other is ApplicationIntent which can be used to off-load certain types of processing to secondary replicas.

References:

• MSDN: Availability Group Listeners, Client Connectivity, and Application Failover (SQLServer)
• MSDN: Configure: Read-Only Routing for an Availability Group (SQL Server)

MultiSubnetFailover

The AG listener supports the use of the “MultiSubnetFailover=True” connection string option. In a nutshell, this option allows for parallel connection attempts on all IP addresses associated to the listener. Let’s run through a quick example.

You have a listener with two IP addresses, one in sub-net A, and one in sub-net B. You are in your normal configuration so the IP address for sub-net A is cached and is used continuously throughout the day. Then, suddenly, data center A drops out and a failover occurs to data center B. Now your IP address for sub-net A goes offline and the one for sub-net B comes online. At this time your applications might still be using the cached value for sub-net A when they hit your listener’s DNS. This causes the application to wait for a timeout on that IP address before it can acquire the secondary IP and attempt a connection.

As you can see here, this can be a performance hindrance that you would like to avoid. By setting the MultiSubnetFailover option to true in all of your connection strings you allow for both IP addresses to be used when attempting a connection. Which ever IP address creates a successful connection first, wins. This will never cause a problem where you happen to get your secondary site when your primary site simply responded a bit slower because the listener will not have two IP addresses from two different sub-nets online at the same time.

In addition, to the benefit of this setting in a multiple sub-net environment Microsoft also notes:

We recommend this setting for both single and multi-subnet connections to availability groups listeners and to SQL Server Failover Cluster Instance names. Enabling this option adds additional optimizations, even for single-subnet scenarios.

An ADO.NET example of a connection string utilizing this attribute:

[shell]Server=tcp:AGListener,1433;Database=AdventureWorks;IntegratedSecurity=SSPI; MultiSubnetFailover=True[/shell]

ApplicationIntent

The ApplicationIntent connection string attribute is used to tell an AG listener what to expect from it’s processing. The possible pass-in values are ReadOnly and ReadWrite where ReadWrite is the default.

The reason for using this attribute in the ReadOnly mode is to utilize your AlwaysOn architecture in a scale-out manner. You will have one or more readable secondary replicas and if configured to accept Read-Intent connections then you can spread your load out to potentially under-utilized servers. Whether using synchronous or asynchronous commit this option will off-load the read load to your secondaries, so, be mindful of your SLAs when deciding to use this because only synchronous commit setups will allow for 100% up-to-date data. Asynchronous configurations will have some form of delay which can be disastrous to some applications but just fine for other types of reporting.

In order to use this method of connection routing you need to setup at least one secondary replica to accept read-intent connections. Steps on how to conduct this were covered in part 4 of this blog series under Group Creation.

How it works

The process is kicked off by an ODBC or OLEDB connection string being passed to the AG listener with the ApplicationIntent set to ReadOnly.

An ADO.NET example of a connection string utilizing this attribute:

[shell]Server=tcp:AGListener,1433;Database=AdventureWorks;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly[/shell]

Once the string is read the AG listener will begin Read-Intent Routing. When the AG is created or a new read-intent replica is added; a read-intent routing list is created/updated. The AG listener will use this list to pick a secondary replica and route the connection to that replica for read processing.

This list is not a form of load balancing. The first available server from the list will be used for any ReadOnly connection that is being created. There is no more sophistication to this list other than, “first to connect wins.”

Back-up Off-loading

REFERENCES:

• MSDN: Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)
• SQLGardner: SQL 2012 AlwaysOn and Backups – Part 1 – Offloading the work to a replica
• SQLGardner: SQL 2012 AlwaysOn and Backups – Part 2 – Configuring Backup Preferences and Automating Backups
• SQLGardner: SQL 2012 AlwaysOn and Backups – Part 3 – Restore

Another one of AlwaysOn’s great features is the ability to off-load your backups to a secondary replica, thus keeping your primary server unhindered by the work load. This can also result in faster backups because you can play with the BACKUP  command options more liberally and take over more memory and CPU for compression, multiple thread processing, and more/larger buffer units.

Basically, what needs to be done is you first set a relative weight for your replica back-ups and then check to see if your server is the current preferred node for backups.

I don’t know about you but when I first started reading about backup off-loading I thought, “wow the AG Listener handles this all for me once I configure the weight of the replicas?” Sadly, the answer to that question was, “no,” we do need to check for the preferred replica ourselves. The reality is that you have to script out your backup jobs on every server with a replica. When you are creating the jobs you have to code in your own IF statement to check the return value of sys.fn_hadr_backup_is_preferred_replica. This function will return a 1 if the server is the current preferred replica and a 0 if not. So you can structure your IF like this:

[sql]If sys.fn_hadr_backup_is_preferred_replica( @dbname ) <> 1
BEGIN
— If this is not the preferred replica, exit (probably without error).
END
— If this is the preferred replica, continue to do the backup.[/sql]

When all jobs run on the all servers only one will begin to process while the others will simply exit without error. This is a bit of work on us DBAs for initial configuration but once it’s setup things are rather nice because during a fail-over you won’t be worrying about setting up or even enabling backup jobs on your secondary sites. Those jobs will just suddenly start backing up databases once their function check returns a 1.

NOTE: If you use the built-in Maintenance Plans for your backups then you only need to make sure that there is a plan on every server. The Backup Database task for those plans implicitly checks the sys.fn_hadr_backup_is_preferred_replica function before continuing. The only flaw here is that the Maintenance Plan will not produce any warnings, errors, or indications that it didn’t backup the database due to not being on a preferred node. See example below, you would leave this unchecked under normal circumstances.

SUPPORTED BACK-UP TYPES

• BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups are supported on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.
• Differential backups are not supported on secondary replicas.
• BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).
• A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).

These limitations present a less than ideal scenario but even with them, backup off-loading is still a very powerful and beneficial feature. The consistent log chain irrespective of the sync type is what I am personally happy about.

Weighing your replicas

Each replica in an AG can be weighted based on a 0 to 100 scale. These settings are configured when you create an Availability Group or when you add a new replica to an AG. We briefly illustrated these options in part 4 of this blog series and BOL addresses the full scope of options here so I’m going to just touch on them a little bit.

Image Credit: SQLGardner

In the above image you will notice three types of settings to configure. First is the overriding preferences seen with radio buttons at the top. This is the general method of selection of replicas.

Next, you’ll notice the Backup Priority column in the grid at the bottom. This where you set a relative weight for each replica to conduct the backups.

Finally, you’ll see the Exclude Replica column. Here you can check off which replicas to exclude from the backup preferences.

NOTE: These are preferences not rules. You can still conduct a backup on any replica that you wish regardless of state or exclusion settings. The purpose behind all this is to make sys.fn_hadr_backup_is_preferred_replica return the value that you want it to under different conditions. Nothing stops you from backing up a database on a server by ignoring that function completely.

Wrap-up:

I hope that this blog series has been helpful to you. We’ve covered a complete build out of a dual data center architecture with instance level (Windows) failover and database level (AlwaysOn) failover capabilities.

If you are stumbling upon this part of the series after searching for information on the Availability Group listener please take the time to click through some of the links below to the previous parts as we defined and configured an AlwaysOn environment and discussed all components’ usages and merits.

Follow the links below to move on through this tutorial.

• Scope and Architecture – Part 1 of 7
• Building the Environment – Part 2 of 7
• Configuring AlwaysOn – Part 3 of 7
• Building Availability Groups – Part 4 of 7
• Explanation of Implicitly Created Components – Part 5 of 7
• How to: Fail-Over – Part 6 of 7
• The Availability Group Listener – Part 7 of 7