0

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')))
  • 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 '17 at 16:16
  • Hello, I tried with SQL Manager with the parameter applicationintent=readonly into Additional Connection Parameters tabs – Jérémy Jan 26 '17 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 '17 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 '17 at 16:51
3

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

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  
  • Hello and thanks for your reply. Yes I added Read-intent only as parameter for readable secondary – Jérémy Jan 26 '17 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 this page for better understanding.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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