February 8, 2016 at 12:15 pm
Evening All,
Here is my ongoing AlwaysOn/Availability/Read Only Routing problem:
Please note I have sanitised the hostnames and IP's consistently.
I have a 3 node shared nothing cluster.
It has a single AlwaysON Availability Group called Ingenius
This contains one database called test.
To make things are bit more complicated perhaps, the nodes span two subnets:
172.16.nnn.nnn
172.18.nnn.nnn
With a single node in one and 2 in the other. Same domain, but different datacenters.
The Listerner DNS name is davedavedave:
Ingeniusdavedavedave('IP Address: 172.16.6.111' or 'IP Address: 172.18.6.111')
Access configuration is as follows (ive tried with many variations of IP or Host and FDQN)
GLSQCOL1010TCP://172.16.6.170:1433READ_ONLY
GLSQCOL3010TCP://172.18.6.170:1433READ_ONLY
GLSQCOL3011TCP://GLSQCOL3010.ingeus.local:1433NO
Routing table looks like this:
GLSQCOL1010GLSQCOL3010TCP://172.16.6.170:14331
GLSQCOL1010GLSQCOL1010TCP://172.16.6.170:14332
GLSQCOL3010GLSQCOL1010TCP://172.18.6.170:14331
GLSQCOL3010GLSQCOL3010TCP://172.18.6.170:14332
When I connect to the Listener without any options it connects me to GLSQCOL1010 as expected: @@servername confirms this.
When I specify readintent and the database name @@servername still returns GLSQCOL1010, whilst I am expecting it to return GLSQCOL3010.
So I cant quite make out where I am going wrong, but equally dont quite fully understand how the routing table works. It's a little confusing.
I have 3 nodes in the 'cluster' but only want readonly routing between two of them.
Any help, as always very much appreciated.
Cheers
Alex
February 9, 2016 at 5:29 am
so each node has a standalone instance of sql server?
can you supply the scripts you used to apply the read only routing configuration?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2016 at 10:22 am
Hi Perry,
Right, well i did the following:
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL1010'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://GLSQCOL1010.blah.local:1433'));
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3010'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://GLSQCOL3010.blah.local:1433'));
Followed by:
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL1010'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('GLSQCOL3010', 'GLSQCOL3011')));
Now, I should point out at this point - this works... Connecting to the Listner with readintent and specifying the db-name sends me to GLSQCOL3010.
However, I am not so certain I understand why, or what I need to do if I want to add a 3rd read only server in the event that GLSQCOL3010 isnt available.
Would I add:
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3011'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://GLSQCOL3011.blah.local:1433'));
To complete the config? I have alluded to its use in the ROUTING_LIST not sure I have configured it right.
Also my second question is, is it possible to add a clause that says if the old primary (GLSQCOL1010) comes back online and is healthy then that should become a readable secondary and apart of the routing list.
This is where my mind cannot cope anymore!
Cheers
Alex
February 9, 2016 at 10:54 am
alex.sqldba (2/9/2016)
Hi Perry,Right, well i did the following:
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL1010'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://GLSQCOL1010.blah.local:1433'));
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3010'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://GLSQCOL3010.blah.local:1433'));
Followed by:
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL1010'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('GLSQCOL3010', 'GLSQCOL3011')));
Now, I should point out at this point - this works... Connecting to the Listner with readintent and specifying the db-name sends me to GLSQCOL3010.
However, I am not so certain I understand why, or what I need to do if I want to add a 3rd read only server in the event that GLSQCOL3010 isnt available.
Would I add:
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3011'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://GLSQCOL3011.blah.local:1433'));
To complete the config? I have alluded to its use in the ROUTING_LIST not sure I have configured it right.
Ok, you haven't specified the secondary connections you wish to allow. I would expect something along the lines of this
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL1010'
WITH (SECONDARY_ROLE (AALOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL1010'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://GLSQCOL1010.blah.local:1433'));
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3010'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3010'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://GLSQCOL3010.blah.local:1433'));
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3011'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3011'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://GLSQCOL3011.blah.local:1433'));
Your Primary role routing below references GLSQCOL3010, but you have no Secondary role configuration listed above.
Also, for each replica listed you should be specifying a Primary role configuration such as
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL1010'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('GLSQCOL3010', 'GLSQCOL3011', 'GLSQCOL1010')));
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3010'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('GLSQCOL1010', 'GLSQCOL3011', 'GLSQCOL3010')));
ALTER AVAILABILITY GROUP [ingeus2]
MODIFY REPLICA ON N'GLSQCOL3011'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('GLSQCOL1010', 'GLSQCOL3010', 'GLSQCOL3011')));
alex.sqldba (2/9/2016)
Also my second question is, is it possible to add a clause that says if the old primary (GLSQCOL1010) comes back online and is healthy then that should become a readable secondary and apart of the routing list.This is where my mind cannot cope anymore!
Cheers
Alex
No clause, you just need to specify the preferred order in Primary role config for each replica
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 10, 2016 at 3:29 am
Hi Perry,
This is what my configuration looks like now after some tweaking based on your reply:
GLSQCOL3010|TCP://GLSQCOL3010.blah.local:5022|SYNCHRONOUS_COMMIT|AUTOMATIC|ALL|READ_ONLY|TCP://GLSQCOL1010.blah.local:1433
GLSQCOL1010|TCP://GLSQCOL1010.blah.local:5022|SYNCHRONOUS_COMMIT|AUTOMATIC|ALL|READ_ONLY|TCP://GLSQCOL3010.blah.local:1433
GLSQCOL3010|TCP://GLSQCOL3010.blah.local:5022|SYNCHRONOUS_COMMIT|AUTOMATIC|ALL|READ_ONLY|TCP://GLSQCOL3010.blah.local:1433
GLSQCOL1010|TCP://GLSQCOL1010.blah.local:5022|SYNCHRONOUS_COMMIT|AUTOMATIC|ALL|READ_ONLY|TCP://GLSQCOL3010.blah.local:1433
So,
As a primary then any replica will accept all connections.
As a secondary they take read-only connections
Am I missing anything? I'm still not 100% certain I know whats going on - but that's my problem. I plan to get the 3rd node involved today and then modify the routings, lets see how I fare!
Cheers,
Alex
February 10, 2016 at 3:54 am
alex.sqldba (2/10/2016)
Hi Perry,This is what my configuration looks like now after some tweaking based on your reply:
GLSQCOL3010|TCP://GLSQCOL3010.blah.local:5022|SYNCHRONOUS_COMMIT|AUTOMATIC|ALL|READ_ONLY|TCP://GLSQCOL1010.blah.local:1433
GLSQCOL1010|TCP://GLSQCOL1010.blah.local:5022|SYNCHRONOUS_COMMIT|AUTOMATIC|ALL|READ_ONLY|TCP://GLSQCOL3010.blah.local:1433
GLSQCOL3010|TCP://GLSQCOL3010.blah.local:5022|SYNCHRONOUS_COMMIT|AUTOMATIC|ALL|READ_ONLY|TCP://GLSQCOL3010.blah.local:1433
GLSQCOL1010|TCP://GLSQCOL1010.blah.local:5022|SYNCHRONOUS_COMMIT|AUTOMATIC|ALL|READ_ONLY|TCP://GLSQCOL3010.blah.local:1433
My script was just an example, you may not want to specify every replica as a readonly partner in a primary role configuration, it all depends on your design for your AG
alex.sqldba (2/10/2016)
So,As a primary then any replica will accept all connections.
Microsoft states
For the primary role, select a new value from the Connections in primary role drop list, as follows:
- Allow all connections
All connections are allowed to the databases in the primary replica. This is the default setting.
- Allow read/write connections
When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. Connections where the Application Intent connection property is set to ReadOnly are not allowed. This can help prevent customers from connecting a read-intent work load to the primary replica by mistake. For more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.
alex.sqldba (2/10/2016)
As a secondary they take read-only connectionsAm I missing anything? I'm still not 100% certain I know whats going on - but that's my problem. I plan to get the 3rd node involved today and then modify the routings, lets see how I fare!
Cheers,
Alex
Again Microsoft states
For the secondary role, select a new value from the Readable secondary drop list, as follows:
- No
No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.
- Read-intent only
Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
- Yes
All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply