Sql-server – Always On Availability Group ApplicationIntent=ReadOnly Not routing to Secondary

availability-groupssql-server-2012

Version: SQL 2012 SP3
Two Node. 1 Primary 1 Secondary

This is how nodes are set up.

Using SSMS connection options when I set connection property ApplicationIntent=ReadOnly I am not getting routed to secondary node. Yes I am using listener name as Server name.

I did not create any read-only routing list because I only have 2 nodes.

Question 1

Am I wrong that even with only 2 nodes I need read-only routing list?

Question 2

What is the difference between these 2 settings about Readable Secondary? Secondaries can only serve read-only traffic, so why route other connections with selecting Yes?

As per books online:

Read-intent only

Only read-only connections are allowed to secondary databases of this
replica. The secondary database(s) are all available for read access.

Yes

All connections are allowed to secondary databases of this replica,
but only for read access. The secondary database(s) are all available
for read access.

Best Answer

You have to have a read-routing list, even with only two instances, if you want to offload reads to a secondary replica.

The following script is an example of setting this up (you would change the AG name to whatever your AG is, and the replicas to your servernames (using the fully qualified domain name, and adjusting the port if required).

ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA1.FQDN:1433'));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA2.FQDN:1433'));

ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA2', 'REPLICA1')));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA1', 'REPLICA2')));

As regards the flags:

ReadIntent allows for connections only passed along with the ApplicationIntent=ReadOnly flag
Yes allows for any connection to the database (meaning you could connect directly with SSMS and run queries)

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.

Sql-server – Always On Availability Group, Always redirect user to read only instance

I don't have an answer to your entire question (though I gave an answer to a similar question today https://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=ReadOnly doesn't work properly.

Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag won't work. I think if you got that working then you could start to re-evaluate your requirements.

Instructions on MSDN https://msdn.microsoft.com/en-us/library/hh710054.aspx and most easily done in PowerShell.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

Best Answer

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

Sql-server – Always On Availability Group, Always redirect user to read only instance

Related Question