SQLTeam.com | Weblogs | Forums

How to return From Read only Routing - sql server 2014


#1

I run set up for enable Read only Routing on my Availability groups.
After that second server was unaccessible.
I want to return to read only. Then in Availability group Properties, I Changed Readable secondary property from
Read-intent only to Yes.
But yes my second server is unaccessible.
How to return from read only Routing, Completely.

I set up read only Routing with this command :
ALTER AVAILABILITY GROUP [AV1]
MODIFY REPLICA ON
N'server1' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AV1]
MODIFY REPLICA ON
N'DK-BI-1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://server-1.domain.COM:1433'));

ALTER AVAILABILITY GROUP [AV1]
MODIFY REPLICA ON
N'server-2' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AV1]
MODIFY REPLICA ON
N'server-2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://server-2.domain.COM:1433'));

ALTER AVAILABILITY GROUP [AV1]
MODIFY REPLICA ON
N'server-1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('server-2','server-1')));

ALTER AVAILABILITY GROUP [AV1]
MODIFY REPLICA ON
N'server-2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('server-1','server-2')));
GO


#2

What do you mean by "return"? I'm not understanding your question.


#3

I confige to use Read only routing.
But It caused for me some Problems as well as I can connect to secondary from sql server console.
I want to remove routing configuration from Availability group.
I change the secondary roll to Read-only But It dosnt work . (I dont have my secondary from console yet)
Thank you