1

I set my readable secondary to read intent only and I ran my application without application intent = readonly and start a trace on the secondary node to make sure we are not reading from the secondary. After that I executed my script to do the routing URL and the Routing List. I ran my application without application intent = readonly and started the trace. I noticed that the app is reading from the secondary and primary. I don't want any application to read from the secondary if its connection string doesn't contain application intent = readonly.

Here is my script to create the routing.

ALTER AVAILABILITY GROUP AG

MODIFY REPLICA ON 'A Node'

WITH( SECONDARY_ROLE

( READ_ONLY_ROUTING_URL='TCP://A Node.test.local:1433' ))

ALTER AVAILABILITY GROUP AG

MODIFY REPLICA ON 'B Node'

WITH( SECONDARY_ROLE

( READ_ONLY_ROUTING_URL='TCP://B Node.test.local:1433' ))

--Specify a read-only routing list

ALTER AVAILABILITY GROUP AG

MODIFY REPLICA ON= 'A Node'

WITH( PRIMARY_ROLE

(READ_ONLY_ROUTING_LIST =('B Node')))

ALTER AVAILABILITY GROUP AG

MODIFY REPLICA ON 'B Node'

WITH( PRIMARY_ROLE

( READ_ONLY_ROUTING_LIST =('A Node')))

closed as unclear what you're asking by LowlyDBA, RLF, Marco, Kin Shah, Erik Feb 8 '17 at 0:18

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    This does not sound like a possible scenario which leads me to believe that we need to challenge your assumptions to see where the issue is. A couple of these might seen overly obvious but I'll ask anyways. 1) Are you connecting to the listener name, not one of the nodes? 2) are there any connections still open from previous tests which might be skewing your analysis? 3) You said you traced it and it connected to two nodes, what was it doing? I can't imagine it ran your query twice on two nodes. 4) Double check your test. Have all applicationintent been removed? Are you on the correct build? – SQL Hammer Feb 5 '16 at 13:34
  • Have you tried to alter your AG to not allow connections when in the secondary role? I would try this, validate there are no connections with the trace, and then change it back and see what happens. – IanG Feb 5 '16 at 15:21
0

thanks to all of you! I found out that the Dev team have tried before to use the secondary readable and they embedded application intent= read secondary in the code logic without truly understand how readable secondary works. they experience error due to read/ write. The DBA at the time has changed readable secondary= No but they never remove it from the logic. Next they migrated to a new server. when I created the routing URL and LIST on the new server the app start using the secondary. So now I guess for my report to use readable secondary I have to remove the routing URL and list and change my report to use node name until the Dev team remove it or use it carefully whenever there is 100% read no right at all. I'm think to remove the routing URL and the LIST using the following script

  ALTER AVAILABILITY GROUP AG

MODIFY REPLICA ON 'A Node'

WITH( SECONDARY_ROLE

( READ_ONLY_ROUTING_URL='' ))

ALTER AVAILABILITY GROUP AG

MODIFY REPLICA ON 'B Node'

WITH( SECONDARY_ROLE

( READ_ONLY_ROUTING_URL='' ))

--Specify a read-only routing list

ALTER AVAILABILITY GROUP AG

MODIFY REPLICA ON= 'A Node'

WITH( PRIMARY_ROLE

(READ_ONLY_ROUTING_LIST =('')))

ALTER AVAILABILITY GROUP AG

MODIFY REPLICA ON 'B Node'

WITH( PRIMARY_ROLE

( READ_ONLY_ROUTING_LIST =('')))

Please let me know if there is the right way

Not the answer you're looking for? Browse other questions tagged or ask your own question.