Contributors – David Pless - dpless@microsoft.com

Overview

In this blog I will go through step by step how to configure Read-Only routing for scale-out report workloads using SQL Server 2012 Always On Database Availability Groups.

In SQL Server 2012, AlwaysOn Availability Groups provides group level high availability for any number of databases on up to four secondaries known as ‘replicas’. The secondary replicas allow direct read-only querying or can enforce connections that specify ‘ReadOnly’ as their Application Intent. The secondaries primary use is to provide high availability or disaster recovery for the groups of databases being replicated. The secondaries can also be used to offset backup operations, DBCC checks, and to offload reporting workloads. The secondary copies of these databases known as secondary replicas have the ability to be read when they are in the secondary role. This is unlike Database Mirroring which requires a Database Snapshot in order to read a static view of the database at the Database Snapshot was created.

AlwaysOn brings in the strengths of Clustering, Log Shipping and Database Mirroring together where the method to transmit data is very similar to Database Mirroring with much more functionality and flexibility. AlwaysOn requires Windows Server 2008 clustering features, but does not require that SQL Server itself be clustered.

In AlwaysOn, Read-Only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available AlwaysOn readable secondary replica (http://msdn.microsoft.com/en-us/library/ff878253.aspx) that is, a replica that is configured to allow read-only workloads when running under the secondary role. To support read-only routing, the availability group must possess an availability group listener (http://msdn.microsoft.com/en-us/library/hh213417.aspx). Read-only clients must direct their connection requests to this listener, and the client’s connection strings must specify the application intent as “read-only.” That is, they must be read-intent connection requests.

This can be seen in the connection string, an example is shown below:

Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID=”";Initial Catalog=”";Data Source=AGListner;Initial File Name=”";Server SPN=”";ApplicationIntent=READONLY

Read-Only routing is a great new feature that can be leveraged to scale out reporting workloads such as SQL Server Reporting Services reports.  All of your reports hosted in SharePoint or on a Native Mode installation of Report Server can specify read-only intent and can be serviced by your secondary replicas.  This takes the heavy read workload that typically causes SQL Server blocking and consumes memory and CPU from your primary read/write database and frees up those resources.

Note: This document assumes that you are familiar with SQL Server 2012 Always On Availability Groups and how to setup AlwaysOn Database Availability Groups.

Overview of SQL Server Always On Infrastructure

To facilitate Read-Only routing I setup three SQL Server 2012 instances and then setup a database availability group using two Adventure Works databases.

Always On Database Availability Group Diagram:

 Diagram of Server Manager:

My Windows 2008 R2 server names are listed below (3 node cluster):

• SP-DENALI1
• SP-DENALI3
• SP-DENALI4

I have loaded a SQL 2012 named instance on each server with the following names:

• SP-DENALI1\SQL1 (PRIMARY REPLICA)
• SP-DENALI3\SQL1 (SECONDARY REPLICA)
• SP-DENALI4\SQL1 (SECONDARY REPLICA)

Note: With AlwaysOn Availability Groups it is a good practice to make all of the named instances the same name for data synchronization.

Availability Group Listener (This is required to be setup for read-only routing):

• SQL1

Note: The Availability Group Listener is much like a virtual network name in traditional SQL clustering as it is a pointer to the SQL Instance that currently hosts the database availability group.

View from SQL Server Management Studio:

 Prerequisites

• The availability group must possess an availability group listener. For more information, see Create or Configure an Availability Group Listener (SQL Server). (http://msdn.microsoft.com/en-us/library/hh213080.aspx)
• One or more availability replicas must be configured to accept read-only in the secondary role (that is, to be readable secondary replicas). For more information, see Configure Read-Only Access on an Availability Replica (SQL Server). (http://msdn.microsoft.com/en-us/library/hh213002.aspx)
• The ability for a client such as a Reporting Services report or application to take advantage of read-only routing depends on the version of the client drive.  At this time the ApplicationIntent option in the connection string is only available in .NET 4.0.2 http://support.microsoft.com/kb/2544514 (http://support.microsoft.com/kb/2544514)

In this post we are using the new SQL Server Data Tools which replaces Business Intelligence Development Studio. SQL Server 2012 RTM has the correct client driver for .NET 4.0.2 that will expose the connection string property ApplicationIntent.

The syntax for a connection string depends on the client provider an application is using. The following example connection string for the .NET Framework Data Provider 4.0.2 for SQL Server illustrates the parts of a connection string that are required and recommended to work for read-only routing:

Server=tcp:SQL1,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

Read-Only Routing Lists

Read-Only routing requires that the availability replica be enabled for read access.  This can be done at the time the Always On Availability Group is setup.  You can also change and view this property by looking at the properties of the Availability Group:

Availability Group property dialogue:

Note: In the above screen shot you can see that the two replicas are marked to allow read connections.

To setup the Read-Only routing list you must be using an Availability Group Listener. In essence, you are essentially creating a SQL Server routing table for AlwaysOn that redirects connections to the secondary when the Application Intent is set to Read-Only. Below is the script that I used to setup the list for each replica based on whether it is in the Primary Role or the Secondary Role:

Note: For a deeper explanation and a VERY handy script that will generate the ROR URL for you please see Matt Neerincx’s blog post http://blogs.msdn.com/b/mattn/archive/2012/04/25/calculating-read-only-routing-url-for-alwayson.aspx

Running Reports using the Read-Only Routing List and ApplicationIntent=ReadOnly

I have created a report in SQL Server Data Tools which is the new Business Intelligence Development Studio. Within SQL Server Data Tools I created a simple Report Project and a simple report to show how a reporting type of workload can be routed by SQL Server to a secondary read-only replica.

The above screenshot shows that I have two data sets. DataSet1 represents the result set that I am displaying in the report. DataSet2 is a simple “SELECT @@SERVERNAME” which returns the current instance name of the SQL Server for which the data source connection is currently connected to.  I am using this to populate a text box with the SQL Server instance name so that I can show you which instance in my AlwaysOn environment executes the query for my report.

Now let’s take a look at how the SSRS report data source is configured for Read-Only Routing:

The above screen shot shows the properties of the shared data source in my report.  Notice the use of the Availability Group Listener name SQL1 as the Data Source property and the ApplicationIntent=ReadOnly option.  These are both required for SQL Server to use the Read-Only routing list I configured earlier.

Another view of the data source properties in SQL Server Data Tools:

Now that the data source is configured properly let’s run the report.  Before I run the report I want to verify which instance is in the primary role.

Running the report:

As you can see in the above screenshot SQL Server received the connection and routed the report query to the secondary replica instance named SP-DENALI4\SQL1.  This instance is the instance that we specified first in the Read-Only Routing List when the SP-DENALI1\SQL1 instance is in the Primary Role.

Summary

With the Read-Only Routing capabilities SQL Server is now more scalable than ever.  Customers can utilize secondary replicas for scaling reporting type workloads, increasing the value of their SQL Server infrastructure while increasing performance by offloading heavy read workloads that commonly cause contention.  Applications like SQL Server Reporting Services in SharePoint mode or Native mode can now easily take advantage of using secondary replicas for reporting.

Read-Only routing is yet another great feature of SQL Server 2012!