AlwaysOn Secondary readable - can't connect with applicationintent=readonly

Question

With an SQL AlwaysOn, I Set my readable secondary to read intent only. When I try to connect with parameter applicationintent=readonly my connection is established with the primary server.

Here is my parameters:

• Listener: Lst_Test
• Avaibility group: AG-TEST
• Primary: PROD-TEST
• Secondary: DRP-TEST

Here is my script for create route :

    ALTER AVAILABILITY GROUP [AG-Test]
MODIFY REPLICA ON 
'PROD-TEST' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://PROD-test.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG-Ocean]
MODIFY REPLICA ON 
'DRP-TEST' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://DRP-test.contoso.com:1433'));


ALTER AVAILABILITY GROUP [AG-Test]
MODIFY REPLICA ON 
'PROD-TEST' WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('DRP-TEST','PROD-TEST')))

ALTER AVAILABILITY GROUP [AG-Test]
MODIFY REPLICA ON 
'DRP-TEST' WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('PROD-TEST','DRP-TEST')))

Answer 1

Your connection string likely doesn't specify the database. It has to include the database name so that SQL Server can tell which routing list to use. (Your server could have multiple Availability Groups running on it.)

Answer 2

since this is bigger than comment ... If you have already done .. I will delete my answer ...

You need to alter your AG replica to allow read only connections e.g. (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))

Below is referenced from BOL :

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  

Answer 3

In order for read-only routing to work, there are some requirements.

Connection string requirements:

• Use TCP protocol
• Reference the listener of an AG that is configured for read-only routing
• Specify a database in the AG
• Set the application-intent property to ReadOnly
• Include the port if the listener is not using 1433

AG requirements:

• Read-only routing URLs and lists must be created
• Secondary replica must be setup to be a read-only replica

Database driver requirements

Answer 4

From my understanding, to achieve scalability through read-only routing in Always On AG, you have to specify the database name in the connection string, to route read connection request to readable secondary replicas. Something similar to this:

ApplicationIntent=ReadOnly;Initial Catalog = DBname;

When you don't specify the Initial Catalog, it will route the connection to your default database (I have seen mostly default database is master) on primary replica.

If you prefer not to specify the Initial Catalog for some reason, change the default database of the user to same database, which is part of Availability Group.

This is worked for me, you can check my page for better understanding.

Answer 5

While the other solutions are more likely, I figured I'd provide mine as one more thing to check. I was having the same issue, but it turns out the database I was testing against wasn't actually in the SQL Availability Group (AG). So if your app keeps connecting to the Primary SQL instance, make sure you are connecting to a database that actually exists in the AG and on the read-only SQL node. One way to tell if your database is in the AG is that in SQL Management Studio, in the SQL server's list of databases, the ones in the AG will typically have (Synchronized) appended to their name; so the database MyDatabase would show up as MyDatabase (Synchronized).