3

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')))
4
  • Where are you trying to connect from? Are you using one of the supported database drivers? Can you show us the connection string?
    – Tara Kizer
    Jan 26, 2017 at 16:16
  • Hello, I tried with SQL Manager with the parameter applicationintent=readonly into Additional Connection Parameters tabs
    – Jérémy
    Jan 26, 2017 at 16:34
  • Did you also add the database name and specify tcp/ip in the second tab of the connection window?
    – Tara Kizer
    Jan 26, 2017 at 16:39
  • No I'don't. I just tried with the db name into "connection properties" tab and I can see my user connected to the secondary DB. Thanks for your help
    – Jérémy
    Jan 26, 2017 at 16:51

5 Answers 5

7

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.)

0
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  
1
  • Hello and thanks for your reply. Yes I added Read-intent only as parameter for readable secondary
    – Jérémy
    Jan 26, 2017 at 16:33
0

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

0

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.

1
  • @LowlyDBA-JohnM sure, I was updating URL as it was changed - will go through the link shared and follow going forward. May 3, 2021 at 17:58
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.