As you may know by now if you’ve been following SQL 2012 HA capabilities, AlwaysOn availability groups allows readable secondary replicas to allow offloading of backups, database consistency checks, index fragmentation checks, reporting and so on. One of the question I see being asked a lot is how do I tell my connection that I want you to go to read-only replica, do I have to always go and change the connection to point to the actual server name? The answers lie here.

AlwaysOn availability groups comes with a feature referred to as Application Intent filtering which is a process of telling you application that I want you to only perform read-only operations. When the intent is defined as read-only, Availability Groups Listener will ensure that the application connects to the first available read-only secondary replica through a mechanism referred to as Application Intent Read-Only Routing.

For the routing to take place some conditions have to in-place

1.       The intent of the application has to be defined as read-only (very obvious), if not defined the connection will be assumed to be read/write intent.

2.       The primary replica has to be on-line.

3.       There has to be readable secondaries.

4.       Read-Intent only has to be configured in the readable secondary dropdown list on all the replicas

5.       Configure the AG to be aware of read-intent only connections

TSQL Sample

ALTER AVAILABILITY GROUP [AG1]

 MODIFY REPLICA ON

N'labmac1' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [AG1]

 MODIFY REPLICA ON

N'labmac1' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://labmac1.mynetwork.local:1433'));

 

ALTER AVAILABILITY GROUP [AG1]

 MODIFY REPLICA ON

N'labmac2' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [AG1]

 MODIFY REPLICA ON

N'labmac2' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://labmac2.mynetwork.local:1433'));

 

PowerShell Sample

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"
 
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://labmac1.mynetwork.local:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://labmac2.mynetwork.local:1433" -InputObject $secondaryReplica

 

6.       The database has to be part of the AG group.

7.       Configure your application to use TCP protocol defined in conditions above the connection string should appear as follows as you can see

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

a.       The Server is actually my TCP protocol

b.      Application intent is defined

c.       A database that is part of AG

Here is how it all goes down.

An application with read-only intent filtering makes a connection to the Listener (yes the application will be connecting to the primary replica listener)

The listener determines that the application has read-only intent and performs the routing redirection to the available readable secondary.

And wallaah you are connected to readable replica. In case of failover the connections will be reset and you will still be on a readable unless the above conditions are not met which would result to connection refused.

In conclusion AlwaysOn has some very good features that are convincing enough to trigger the upgrade of your SQL Server environment to SQL 2012.