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.