June 22, 2015 at 11:20 am
Evening Folks,
Having an annoying AG/AO problem with the read only routing side of it.
Let me give you some specifics first:
2 SQL Server Instances, Not Clustered.
Availability Group is named 'Ireland'
There is a primary Replica and a Secondary Replica, named:
'Ireland\Primary' and 'Ireland\Secondary'
There is a listener configured with the name 'ListenIreland' on Port 14330 (the two 3's are correct)
Read Only Routing URLS are configured as follows:
Ireland\Primary tcp://Ireland.dom.local:49891ALL
Ireland\Secondary tcp://Ireland.dom.local:49841ALL
So now my problem:
When I try to connect using the ApplicationIntent=Readonly; or even using -K ReadONLY in sqlcmd I get the error telling me that my connection was actively refused.
This is connecting to the Listener, not the instance itself - that works fine. I'm at a bit of a loss now.
To explain what I am trying to achieve is a for a connection to be redirected to the secondary replica when its set for read-intent.
Does anyone see where my problem is? Or can I provide any more information to help debug?
Cheers Guys,
Alex
EDIT: I've just noticed that it only fails when I specify ApplicationIntent=ReadOnly; If I omit the Intent It connects to the read-write database instead.
June 23, 2015 at 12:03 am
Did you configure the read_only_routing_list on the primary replica?
Can you confirm you can connect directly to the secondary's read only routing URL?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 23, 2015 at 2:24 am
Yes, I have - but now I'm not so sure I understand how the ports are working in this, firstly here is my routing list: (I've sanitised the servers names somewhat)
source_replica_server_name;readonly_replica_server_name;read_only_routing_url;routing_priority
UKSQTD002\NIRELAND;UKSQTD003\NIRELAND;tcp://UKSQTD003.domain.local:49891;1
UKSQTD002\NIRELAND;UKSQTD002\NIRELAND;tcp://UKSQTD003.domain.local:49891;2
UKSQTD003\NIRELAND;UKSQTD002\NIRELAND;tcp://UKSQTD002.domain.local:49841;1
UKSQTD003\NIRELAND;UKSQTD003\NIRELAND;tcp://UKSQTD002.domain.local:49841;2
As to where the port numbers came from, I'd run a so-called calculator to determine them for me - but can I just use 1433 (this is a test environment anyway)
Also, if I look at the Endpoint URL in properties of the 2 availability replicas they use the following:
TCP://UKSQTD003.ingeus.local:5023
and
TCP://UKSQTD002.ingeus.local:5023
So, thoroughly confused now as to where ports 49891 and 5023 are used?
Cheers
Alex
June 23, 2015 at 5:09 am
alex.sqldba (6/22/2015)
Read Only Routing URLS are configured as follows:Ireland\Primary tcp://Ireland.dom.local:49891ALL
Ireland\Secondary tcp://Ireland.dom.local:49841ALL
This is only a small part of the config required, please supply the exact script you used to apply RO routing
alex.sqldba (6/23/2015)
UKSQTD002\NIRELAND;UKSQTD003\NIRELAND;tcp://UKSQTD003.domain.local:49891;1UKSQTD002\NIRELAND;UKSQTD002\NIRELAND;tcp://UKSQTD003.domain.local:49891;2
UKSQTD003\NIRELAND;UKSQTD002\NIRELAND;tcp://UKSQTD002.domain.local:49841;1
UKSQTD003\NIRELAND;UKSQTD003\NIRELAND;tcp://UKSQTD002.domain.local:49841;2
As to where the port numbers came from, I'd run a so-called calculator to determine them for me - but can I just use 1433 (this is a test environment anyway)
Also, if I look at the Endpoint URL in properties of the 2 availability replicas they use the following:
TCP://UKSQTD003.ingeus.local:5023
and
TCP://UKSQTD002.ingeus.local:5023
So, thoroughly confused now as to where ports 49891 and 5023 are used?
Cheers
Alex
The port used in the URL configuration is the database engine port number, not the database mirroring port.
Please take a look through my stairway to AlwaysOn starting at this link
http://www.sqlservercentral.com/articles/Failover+Clustered+Instance+(FCI)/107536/[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply