January 18, 2016 at 9:36 pm
While i test this out, it might take some time so decided to ask here. If i have AAG with secondary replica's in read only mode,how does listener send queries to replica's? Lets say i have stored procedure which does select and then does insert/updates, how does it work in this case? Do ALL reads ALWAYS go to secondary replica's?
January 19, 2016 at 1:31 am
The only queries which go to read only replicas are ones that are run on connections which were made with the ReadOnlyIntent option in the connection string. If the connection string doesn't have that option, the query is run on the principal.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 8:38 am
do i always have to have read_only intent option even if i chose replica's to be always READ only? Let's say i do have that option connection string, how does it handle a single transaction which has select and updates?
January 19, 2016 at 8:44 am
The only queries which go to read only replicas are ones that are run on connections which were made with the ReadOnlyIntent option in the connection string. If you do not specify the option in a connection string, that connection goes to the primary replica (the read-write one).
If you specify read only intent in a connection string and then try to write, you'll get an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 11:29 am
you also need to specify the database name in the connection string otherwise it doesn't know where to route the query to
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 19, 2016 at 11:58 am
I am using listener to connect through SSMS, i do have ApplicationIntent = ReadOnly, still it connects to primary? Am i missing something here?
January 19, 2016 at 12:14 pm
curious_sqldba (1/19/2016)
I am using listener to connect through SSMS, i do have ApplicationIntent = ReadOnly, still it connects to primary? Am i missing something here?
You have to actually configure the AG for read-only routing; it doesn't just automatically route to read-only replicas. See here for more info: https://msdn.microsoft.com/en-us/library/hh710054.aspx.
You can run this query (lifted from http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/22/modifying-alwayson-read-only-routing-lists.aspx) to check the current read-only routing configuration, if there is any configured yet:
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
Cheers!
January 19, 2016 at 12:39 pm
I am using listener to connect through SSMS, i do have ApplicationIntent = ReadOnly, still it connects to primary? Am i missing something here?
If you are using SSMS you can connect to the listener just like you would any other DB. You can execute queries directly to the listener DB.
For example we have three nodes NODEA, NODEB, NODEC. NODEB & NODEC are both listeners. I can connect directly to NODEB in SSMS and run queries against NODEB, even tho it is a listener. You can set different permissions as well to the listeners DB as well.
Sorry if that isn't what you are asking.
January 19, 2016 at 12:41 pm
I think i do have routing configured, attached is the image.
January 19, 2016 at 12:42 pm
krypto69 (1/19/2016)
I am using listener to connect through SSMS, i do have ApplicationIntent = ReadOnly, still it connects to primary? Am i missing something here?
If you are using SSMS you can connect to the listener just like you would any other DB. You can execute queries directly to the listener DB.
For example we have three nodes NODEA, NODEB, NODEC. NODEB & NODEC are both listeners. I can connect directly to NODEB in SSMS and run queries against NODEB, even tho it is a listener. You can set different permissions as well to the listeners DB as well.
Sorry if that isn't what you are asking.
When i connect to listener even using the added option i still connect to primary, i was hoping to connect to secondary.
January 19, 2016 at 12:45 pm
krypto69 (1/19/2016)
You can execute queries directly to the listener DB.For example we have three nodes NODEA, NODEB, NODEC. NODEB & NODEC are both listeners.
The listener is not a database. It's a virtual network name.
https://msdn.microsoft.com/en-us/library/hh213417.aspx
In your example, NodeA would be the principal replica, nodes B and C secondary replicas.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 12:45 pm
curious_sqldba (1/19/2016)
When i connect to listener even using the added option i still connect to primary, i was hoping to connect to secondary.
Are you specifying the database name in the connection string?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 12:53 pm
GilaMonster (1/19/2016)
curious_sqldba (1/19/2016)
When i connect to listener even using the added option i still connect to primary, i was hoping to connect to secondary.Are you specifying the database name in the connection string?
I am connecting through SSMS, again i am able to connect, when i execute select @@servername it gives me primary node name.
January 19, 2016 at 12:57 pm
curious_sqldba (1/19/2016)
GilaMonster (1/19/2016)
curious_sqldba (1/19/2016)
When i connect to listener even using the added option i still connect to primary, i was hoping to connect to secondary.Are you specifying the database name in the connection string?
I am connecting through SSMS, again i am able to connect, when i execute select @@servername it gives me primary node name.
Are you specifying the database name in the connection string? Are you specifying ReadOnlyIntent in the connection string?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2016 at 1:19 pm
Your jpg shows
When Primary is DB4 then it can use DB5 (priority 1) or DB4 (priority 2) as read-only
When Primary is DB5 then it can use DB4 (priority 1) or DB5 (priority 2) as read-only
If you want to have all read-only connections use the "other" server then you need to delete the entries for the "primary"
Note you can use sqlcmd to test this e.g. with windows authentication:
sqlcmd -S MyAAGListener -K ReadOnly -l 30 -d MyDatabase -Q "SELECT @@SERVERNAME, DB_NAME()"
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply