2

I know I can just change the connection string to remove the applicationintent property, but how do I remove the configuration from within SQL Server?

For example, if we query sys.availability_read_only_routing_lists, how do I get an empty result?

2

Ref:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql

Use ALTER AVAILABILITY GROUP command with MODIFY REPLICA ON option.

<modify_replica_spec>::=  
  <server_instance> WITH  
    (    
       ENDPOINT_URL = 'TCP://system-address:port'   
     | AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }   
     | FAILOVER_MODE = { AUTOMATIC | MANUAL }   
     | SEEDING_MODE = { AUTOMATIC | MANUAL }   
     | BACKUP_PRIORITY = n  
     | SECONDARY_ROLE ( {   
          ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }   
        | READ_ONLY_ROUTING_URL = 'TCP://system-address:port'   
          } )  
     | PRIMARY_ROLE ( {   
          ALLOW_CONNECTIONS = { READ_WRITE | ALL }   
        | READ_ONLY_ROUTING_LIST = { ( ‘<server_instance>’ [ ,...n ] ) | NONE }   
          } )  
     | SESSION_TIMEOUT = seconds  
    )   

NONE

Specifies that when this availability replica is the primary replica, read-only routing will not be supported. This is the default behavior. When used with MODIFY REPLICA ON, this value disables an existing list, if any.

2
  • Great solution, thanks for information. Want to ask though, are there any consequences to disabling readability on the secondaries? – Xingzhou Liu Aug 2 '17 at 1:16
  • 1
    Unless you have any good reason I would not do that. You are sending all traffic to one node. – SqlWorldWide Aug 2 '17 at 1:17
0

You can set the "readable secondary" property of each replica under Availability Group properties. You need to connect to the primary in ssms, then right click availability group and select properties. See the third screen in this blog post: https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/. Your choices are "Yes", "Read Intent", and "No".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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