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 Answers 2

2

Ref:

https://learn.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? Aug 2, 2017 at 1:16
  • 1
    Unless you have any good reason I would not do that. You are sending all traffic to one node. Aug 2, 2017 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 and acknowledge you have read our privacy policy.

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