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" 😉