Readonly Routing List is not working in AlwaysOn feature

Question

Hi All,

We are exlploring off loading the Readonly operations on AlwaysOn setup.

We currently have a AlwaysOn set up on 1 Primary(AlwaysOn1Serv1) & 2 active read only secondaries (AlwaysOn2Serv2 & AlwaysOn3Serv3) servers.

On accessing our AlwaysOn listener ( ALywasOnAVG_LIstener) with Application intent as Read only,  the connection is not routed to none of the secondary instances. Instead, all the read Only opearations are served by Primary server.

Please find the details of the configurations below.

We defined the Readonly Routing list as below.

ALter Availability Group AlwaysOnAVG  MODIFY REPLICA ON 'ALwaysOn3Serv3' WITH ( Secondary_ROLE ( READ_ONLY_ROUTING_URL='TCP://ALwaysOn3Serv3.sqlsolutions.com:5022'))

Same command executed on AlwaysOn2Serv2 as well with respective values.

Now we ran the below command to define the readonly preference on seocndary instances

Alter Availability Group ALwaysOnAVG MODIFY REPLICA ON 'ÁlwaysOn1Serv1'

WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST=('AlwaysOn3Serv3','AlwaysOn2Serv2') ))

But when we tried accesing our AlwaysOn listener ( ALywasOnAVG_LIstener) with Application intent as Read only,  the connection is not routed to none of the secondary instances. Instead, all the read Only opearations are served by Primary server.

Connection string we used :-

SQLPROVIDER=SQLNCLI11.0;Data Source=ALwaysOnAVG_Listener;initial Catalog=AlwaysOnDB1;Integrated Security=SSPI; Application Intent=ReadOnly;

Set up deatils:-
Availability Group Name: AlwaysOnAVG
Databases: AlwaysOnDB1 & ALwaysOnDB2
Availability Group Listener Name: AlwaysOnAVG_Listener

Servers                current Role    behaviour in Secondary
AlwaysOn1Serv1 PRIMARY          Allow ReadOnly Intent COnnections
AlwaysOn2Serv2 Secondary       Allow ReadOnly Intent COnnections (Synchronous, automatic failover mode)
AlwaysOn3Serv3 Secondary       Allow ReadOnly Intent COnnections (Synchronous, ASynchronous, High Safety Mode)

Please help us in fixing this issue.

Answer 1

( READ_ONLY_ROUTING_URL='TCP://ALwaysOn3Serv3.sqlsolutions.com:5022'))

I think problem might be the port you put here, it should be the client connection port (by default it's 1433 unless you change it during/after installation)

5022 is the internal endpoint port between primary and secondary replica instances.

Answer 2

Hi,

SQL instances are running on defualt port 1433. We dropped the entire Availability Group and recreated it with the same name as earlier. This time, we used the default port 1433 while defining the routing list and ran the test again with Readonly Intent in the connection string. But we still see that all the requests are served by Primary(In this case AlwaysOn1Serv1) itself. 

I think the way i am testing (as explained below) the Readonly Intent connections are actually served by secondaries, is correct. if not, please let us know how to test /show the customers that the ReadOnly intent connections are served by Secondaries.

As shown in the link: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI404, i have a small GUI application, which allows us to enter the COnntection string and Two queries ( one is normal select command & other one is Select @@servername). As shown in the above link by Eric,  i am displaying the output of first query (Ex: select * from EMP command) in first Text box and the output of @@servername on the COnnected To Text box as shown in the Attachment.

Thanks

 

Answer 3

You can do a quick check to see if you can connect to secondary via instance name + "Application Intent = ReadOnly" directly.

You can also try SQL CMD to see if you can connect

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqlcmd -S ALwaysOnAVG_Listener -E -d AlwaysOnDB1 -K ReadOnly
1> SELECT @@SERVERNAME
2> GO

 

Here’s the full list of things to check: ( I marked PASSED based on your info above)
PASSED   Routing list is configured correctly – e.g. primary replica is pointing to the secondary, secondary is pointing to the primary.
PASSED       Routing URLs are assigned to each replica
3.       Secondary replica is actively synchronizing data with the primary. It shouldn’t be suspended.
4.       Secondary replica must be configured to accept connections (connect to it with readonly intent directly and see...)
PASSED       Connection must be established to VNN (listener) of the availability group
 
If everything seems right, please create XEvent session on the primary replica and add following events:
•         read_only_route_complete
•         read_only_route_fail
•         hadr_evaluate_readonly_routing_info
•         connectivity_ring_buffer_recorded

Answer 4

Can you also check sys.availability_group_listeners to see if the listener is associated with your Availability Group?

Answer 5

Hi Yao,

Thanks for your replies.

I tried with the below command. But its still poiting to the primray instance instead of secondary instance.

sqlcmd -S ALwaysOnAVG_Listener -E -d AlwaysOnDB1 -K ReadOnly
1> SELECT @@SERVERNAME
2> GO

--- output: AlwaysOnServ1        ( When Instance is running on ALwaysOn1Serv1 instance)

Also, i am able to connect directly to the secodary Instances(databases) & able to fetch the data from a table with ReadOnly intent as shown in the below script.

SQLCMD -S ALwaysOn2Serv2 -E -d AlwaysOnDB1 -K ReadOnly

ALso, My Listener is associated with my Availability Group (verified with Sys.availability_group_listeners)

Reg the list of things to check:-

PASSED   Routing list is configured correctly – e.g. primary replica is pointing to the secondary, secondary is pointing to the primary.
PASSED       Routing URLs are assigned to each replica
3.       Secondary replica is actively synchronizing data with the primary. It shouldn’t be suspended. (YES, i can failover the server to secondary and could find the last latest updated being reflected on the secondary Dbs too.)
4.       Secondary replica must be configured to accept connections (connect to it with readonly intent directly and see...) (YES, able to connect directly to the secondaries with SQLCMD with Readonly intent and able to fecth the Data)
PASSED       Connection must be established to VNN (listener) of the availability group

Answer 6

can you post your results of  sys.availability_group_listeners ?

Answer 7

Here is the output of Sys.Availability_Group_Listeners..

 group_id:-F7C1C159-CB4B-4FD0-8479-30EB7A1AD0D7

Listener_ID : cb50c307-12c3-462e-b412-0757281a4cf1

dns_Name : AlwaysOnAVG_Listener

Port :  NULL

is_conformant   : 1

ip_configuration_string_from_cluster : ('IP Address: 155.168.130.110')

Thanks

Answer 8

OK...so it looks like you created the listener in cluster manager directly and associated listener with AG resource (That's why we can query this listener from catalog view)

 

However, if you create that in cluster manager, you miss one step to assign the port to this listener. (that's why we see Port: NULL here), please execute:

Alter Availability Group [your ag name]
Modify Listener AlwaysOnAVG_Listener
{
   Port = xxxx
}

xxxx usually you can use 1433 - the default port for SQL instance or another port you want to specify.

After that you can retry this feature (with port number - if not default 1433). The Primary can only start to listen the listener after you assign the port and only connections through that listener can leverage ReadOnly Routing feature.

 

To avoid this confusion, you can always create listener through SQL (SSMS or T-SQL)

• SSMS: find "Availability Group", go to the AG you've created, right click -> "Create Listener...", then you can create DHCP or Static IP listener. you can see the input box for the port.
• T-SQL:
    - Create AG with listener in one T-SQL : http://msdn.microsoft.com/en-us/library/ff878399(v=SQL.110).aspx (See listener_option)
    - After you create AG, you want to add listener: http://msdn.microsoft.com/en-us/library/ff878601(v=SQL.110).aspx  (See add_listener_option)

In create availability group wizard, listener creation is optional , but you can find it in the <Listener> tab - so you can also create listener in the wizard as one operation.

 

Answer 9

That's true. I have skipped the creation of Listener window while i was creating Availability Group due to unavailability of DHCP server. Latter i manually added Accesspoint for the Listener (for ALwaysONAVG_LIstener with Static IP) in the Cluadmin.msc

NOw, i dropped the listener and recreated it in the SSMS with port no: 1433 ( remaining setting are same as earlier)

But this time i am not able to connect to the listener with ReadIntent. Below is what i observed when Listener is actively running on AlwaysOn1Serv1 instance.

A) SQLCMD -S AlwaysOnAVG_listener -E -d AlwaysOnDB1 -K ReadOnly

>> Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : TCP Provider: No such host is known.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

B) SQLCMD -S AlwaysOnAVG_listener -E -K ReadOnly  ----- with Out-d option ,Connection successfully establised to Primary ( not to secondary)

>> select @@servername --- gives output as AlwaysOn1Serv1  (but AlwyasOn3Serv3 is expected)

C)   SQLCMD -S AlwaysOn3Serv3 -E -dAlwaysOnDB1 -K ReadOnly  --- ReadIntent Connection successfully establised to secondary)

Please check the below configuration details of my setup. Just want to make sure that the Routing list & build no is correct. Also please check the way Readonly Routing List has been defined( script pasted in the post).

SQL Server Version: Select @@version : DENALI CTP3:11.0.1440.19

Select Replica_Server_name,Endpoint_url,Secondary_Role_allow_Connections_Desc, Backup_Priority,  Read_only_Routing_URL from Sys.AVailability_replicas

Out put:

Replica_Server_name   Endpoint_url             Secondary_Role_allow_Connections_Desc     Backup_Priority Read_only_Routing_URL 

AlwaysOn1Serv1  TCP://AlwaysOn1Serv1.sqlsolutions.come:5022,   Read_ONLY,   4,                                                             NULL

AlwaysOn2Serv2 TCP://AlwaysOn2Serv2.sqlsolutions.come:5022, Read_ONLY,  1,  TCP://AlwaysOn2Serv2.sqlsolutions.com:1433

AlwaysOn3Serv3  TCP://AlwaysOn3Serv3.sqlsolutions.come:5022,  Read_ONLY, 2, TCP://AlwaysOn3Serv3.sqlsolutions.com:1433

 

 

Answer 10

all configurations look fine. Can you try enabling -T 9532 when you start SQL Instance?

I barely remember Readonly Routing feature might not be enabled in CTP3 (11.0.1440.19) , you need trace flag to try that.

Answer 11

-T9532 has already been enabled on all the instances. With out this trace enabled, i could not have added 2nd secondary replica to the availability Group. Not sure which version Did Eric use in the demo (Link pasted in the post).

Thank you

Answer 12

that's odd. If you want , please contact me <goden.yao at microsoft.com> and we can set up online meeting to take a look at your environment/configuration.