ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

Question

We have a SQL 2012 AlwaysOn Availibility Group with 2 nodes. We have synchronous commit between the 2 replicas. They are both set to accept ReadOnly connections when acting as secondaries and to accept All Connections when acting as Primary.

We have ReadOnly routing setup for readonly traffic via the listener and with ApplicationIntent=ReadOnly in the connection string. It all works fine.

A couple of days ago we rebooted the readable secondary and during the time it was down, the readonly traffic failed to connect to a readable secondary (as expected), but wasn't redirected to the Primary as I thought it would. Instead the following error was received

Unable to access the SalesDb database because no online secondary replicas are enabled for read=only access.

My question is, if no readable secondary replicas are available for some reason, does the readonly traffic not get routed to the Allow All Connections Primary replica by the listener, rather than getting a connection error?

Any answers gratefully received.

AcombLil

Answer 1

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.

Answer 2

You should include the primary replica as the last entry in the Read_Only_Routing_List so that it will be used when the secondaries are unavailable. See Steve's answer for how to enable ApplicationIntent=ReadOnly on the primary and include it in the routing list.