September 23, 2015 at 11:00 am
Hello Community,
I have a concern in regards to AG and read-only intent.
So have I have an environment with 2 nodes, setup under AlwaysOn.
Everything is establish, but I'm concern in regards to the read-only intent application controls.
Example, if user connects via SSMS through the Group Listeners, does the AGL determine if the query being passed by the user is a read query (example select statment) and then redirect this to the 2nd replica?
Also same goes for SSRS, I can assume under the datasources for SSRS users need to specify for the data source applicationIntent connection string.
Lastly, I've tried for a simple test on the 2nd replica (sp_who2) while running an SSRS (using SQL data tools on another server) connecting to the Availability Group Listeners, but I don't see the SSRS connection coming into the secondary node, I do however see it on the primary replica.
Is there something else that I'm missing in the settings?
September 24, 2015 at 9:11 am
nemesis101 (9/23/2015)
Hello Community,I have a concern in regards to AG and read-only intent.
So have I have an environment with 2 nodes, setup under AlwaysOn.
Everything is establish, but I'm concern in regards to the read-only intent application controls.
Example, if user connects via SSMS through the Group Listeners, does the AGL determine if the query being passed by the user is a read query (example select statment) and then redirect this to the 2nd replica?
To be able to direct a readonly query via the listener the incoming connection must have the read_intent option specified.
nemesis101 (9/23/2015)
Also same goes for SSRS, I can assume under the datasources for SSRS users need to specify for the data source applicationIntent connection string.Lastly, I've tried for a simple test on the 2nd replica (sp_who2) while running an SSRS (using SQL data tools on another server) connecting to the Availability Group Listeners, but I don't see the SSRS connection coming into the secondary node, I do however see it on the primary replica.
Is there something else that I'm missing in the settings?
Check my stairway series to AlwaysOn on this site starting at this link, read only routing is detailed there
http://www.sqlservercentral.com/articles/Failover+Clustered+Instance+(FCI)/107536/[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 24, 2015 at 10:01 am
Thanks for the reply,
I've notice a few things.
I needed to setup Read_only_Routing_URL and Read_Only_routing_List.
example:
ALTER AVAILABILITY GROUP MyGroup
MODIFY REPLICA ON N'Myreplica' WITH
(SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'tcp://Myreplica.domain.com:1433'))
ALTER AVAILABILITY GROUP MyGroup
MODIFY REPLICA ON N'Myreplica2' WITH
(SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'tcp://Myreplica2.domain.com:1433'))
ALTER AVAILABILITY GROUP MyGroup
MODIFY REPLICA ON N'Myreplica'
WITH (Primary_role (READ_ONLY_ROUTING_LIST =('N'Myreplica','N'Myreplica2')))
ALTER AVAILABILITY GROUP MyGroup
MODIFY REPLICA ON N'Myreplica2'
WITH (Primary_role (READ_ONLY_ROUTING_LIST =('N'Myreplica2','N'Myreplica')))
After doing this, however I have not had success in redirecting SSRS reports to the secondary replica.
Within SSRS I have setup a Data Source=ListenerName SQL;Initial Catalog=MyDB;ApplicationIntent=ReadOnly;
Within this this SSRS test I have dataset to select @@SERVERNAME to see if at somepoint the data is coming from the second replica.
Everything else within my environment is working correctly example (failover/backups/etc), except the redirection of reads to the 2nd replica.
All reads are hitting the primary.
I've also check the settings within the AG and verified that my replicas accept all connections and allow readable secondary = Yes
I know im getting close, but I feel like I'm still missing something.
September 24, 2015 at 10:45 am
Also to add,
I've notice the same has happen with SSMS, where I apply additional connection parameters (ApplicationIntent=Readonly) I still see the reads on the primary replica not the secondary.
September 25, 2015 at 5:08 am
what TCP port number did you use for the Listener
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2015 at 7:30 am
The Listener is setup to run on port 1433
September 28, 2015 at 7:53 am
Nemesis, did you get this resolved?
September 28, 2015 at 11:14 am
Unfortunately no,
I'm still working to find the answer to this.
Once we find the answer, it will be very good to distribute this information forward, since there maybe many DBA out there that may face this issue.
So to recap this is what has happen.
I have two SQL Server 2012 Enterprise with version build 11.0.5582
Both nodes are participating in AlwaysOn.
I have tested failover successfully.
The availability Group Listeners is set on port 1433
Users can connect via ssms using the AGL name with no issues.
Both replicas allow secondary reads = YES
But the biggest issue I notice is the reads are still being executed on the primary node and not the 2nd node.
September 28, 2015 at 11:43 am
Have you tried using different ports in your routing table. I used the dynamic port assigned in Configuration Manager instead of 1433. Below is a link that auto generates this url:)
http://blogs.msdn.com/b/ggaurav/archive/2014/09/03/testing-alwayson-routing-url-using-sqlcmd.aspx
September 28, 2015 at 12:47 pm
Have you tried connecting with some other tool such as sqlcmd to see if the readonly intent is working?
sqlcmd -e -d MyDB -l 30 -M -K READONLY -Q "SELECT CAST(@@SERVERNAME AS VARCHAR(30)) AS SERVER_NAME; SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DATABASE_NAME" -S ListenerName SELECT CAST(@@SERVERNAME AS VARCHAR(30)) AS SERVER_NAME; SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DATABASE_NAME
I also experienced the issue of setting up the Read_only_Routing_URL and Read_Only_routing_List but I see you have already configured that.
I do not know if this applies to your situation but there is a kb for readonly routing for Report Services, but, according to the article, you should see an error for the keyword applicationintent if this is the issue.
https://msdn.microsoft.com/en-us/library/hh882437(v=sql.120).aspx
September 28, 2015 at 12:56 pm
Thanks for the link,
so this is what I got from the article.
Once I ran this query:
SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority
------------------
Results:
Availability GroupWhen Primary Replica IsRouting PriorityRO Routed Tosecondary_role_allow_connections_descread_only_routing_url
MyGroup Myreplica11Myreplica1ALLtcp://Myreplica1.mydomain.org:1433
MyGroup Myreplica12Myreplica2ALLtcp://Myreplica2.mydomain.org:1433
MyGroup Myreplica21Myreplica2ALLtcp://Myreplica2.mydomain.org:1433
MyGroup Myreplica22Myreplica1ALLtcp://Myreplica1.mydomain.org:1433
I was able to test sqlcmd example and I receive no error so far.
I was wondering if the way this is handle is similar to load balancing. (Example Round-Robin) in which a query is distributed on a first come first serve basis.
My assumption was that if any replica is set to have secondary copies to be used, the AGL would be able to direct the traffic based on applicationintent connection.
Is this assumption correct?.
September 28, 2015 at 1:35 pm
I think I see your problem, when primary is myreplica then the route needs to have myreplica2 first, you originally posted the opposite so your primary settings should look like below: I simply changed the order of the replica's on both alter statements.
ALTER AVAILABILITY GROUP MyGroup
MODIFY REPLICA ON N'Myreplica'
WITH (Primary_role (READ_ONLY_ROUTING_LIST =('N'Myreplica2','N'Myreplica')))
ALTER AVAILABILITY GROUP MyGroup
MODIFY REPLICA ON N'Myreplica2'
WITH (Primary_role (READ_ONLY_ROUTING_LIST =('N'Myreplica','N'Myreplica2')))
September 28, 2015 at 1:52 pm
WilburBud,
I tried this portion of the code:
sqlcmd -e -d MyDB -l 30 -M -K READONLY -Q
"SELECT CAST(@@SERVERNAME AS VARCHAR(30)) AS SERVER_NAME; SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DATABASE_NAME"
-S ListenerName
I get in response only my main replica in this case (Replica1).
September 28, 2015 at 2:24 pm
JeepHound (9/28/2015)
I think I see your problem, when primary is myreplica then the route needs to have myreplica2 first, you originally posted the opposite so your primary settings should look like below: I simply changed the order of the replica's on both alter statements.ALTER AVAILABILITY GROUP MyGroup
MODIFY REPLICA ON N'Myreplica'
WITH (Primary_role (READ_ONLY_ROUTING_LIST =('N'Myreplica2','N'Myreplica')))
ALTER AVAILABILITY GROUP MyGroup
MODIFY REPLICA ON N'Myreplica2'
WITH (Primary_role (READ_ONLY_ROUTING_LIST =('N'Myreplica','N'Myreplica2')))
This solution helped alot,
thanks for pointing out the routing list, after I took another look I did notice that the routing list was pointing to itself first and then pointing to the second. I can see my error as to how the replica1 has to prioritize to route to the 2nd replica.
So now the results are this after I queried it again:
Availability GroupWhen Primary Replica IsRouting PriorityRO Routed Tosecondary_role_allow_connections_descread_only_routing_url
MyGroup Myreplica11Myreplica2ALLtcp://Myreplica2.mydomain.org:1433
MyGroup Myreplica12Myreplica1ALLtcp://Myreplica1.mydomain.org:1433
MyGroup Myreplica21Myreplica1ALLtcp://Myreplica1.mydomain.org:1433
MyGroup Myreplica22Myreplica2ALLtcp://Myreplica2.mydomain.org:1433
now when I execute on sqlcmd
sqlcmd -e -d MyDB -l 30 -M -K READONLY -Q
"SELECT CAST(@@SERVERNAME AS VARCHAR(30)) AS SERVER_NAME; SELECT CAST(DB_NAME() AS VARCHAR(30)) AS DATABASE_NAME"
-S ListenerName
This reports the secondary replica (instead of the first)
or on ssrs (ApplicationIntent=ReadOnly -- within my datasource) I can do an sp_who2 and I can see the reads hitting my second replica.
After going through several cycles I looked past this and I thought to myself, I need another pair of eyes to see what I didn't see.
Thanks, and hopefully other DBA out there finds this helpful.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply