{"id":1167,"date":"2025-04-19T09:45:22","date_gmt":"2025-04-19T09:45:22","guid":{"rendered":"https:\/\/humtechno.com\/master\/?p=1167"},"modified":"2025-04-19T09:47:28","modified_gmt":"2025-04-19T09:47:28","slug":"sql-server-alwayson-secondary-replica-read-only-routing","status":"publish","type":"post","link":"https:\/\/humtechno.com\/master\/2025\/04\/19\/sql-server-alwayson-secondary-replica-read-only-routing\/","title":{"rendered":"SQL Server Alwayson Secondary Replica Read Only Routing"},"content":{"rendered":"\n\n\n\n

HST Techno<\/p>\n\n

Kamran Hussain<\/p>\n\n

Download Complete Steps<\/a><\/p>\n\n

SQL Server Read-Only Routing<\/strong> is a feature used with Always on Availability Groups<\/strong> that allows SQL Server to automatically route read-only queries<\/strong> (like SELECT statements) to a readable secondary replica<\/strong> instead of the primary.<\/p>\n\n

\n\n

In Simple Words:<\/strong><\/p>\n\n

When your application sends a read-only request<\/strong>, SQL Server can redirect it to a secondary server<\/strong>, reducing the load on the primary server (which handles both reads and writes). This helps improve performance and scalability.<\/p>\n\n

\n\n

How It Works:<\/strong><\/p>\n\n

    \n
  1. Your application connects using a read-only intent connection string<\/strong>.<\/li>\n\n\n\n
  2. SQL Server checks the routing rules in the Availability Group settings.<\/li>\n\n\n\n
  3. It routes the request to a secondary replica<\/strong> that\u2019s set up for read-only workloads.<\/li>\n<\/ol>\n\n

    Execute on Primary Replica Machine of Database<\/u><\/strong><\/p>\n\n

    — On Primary Replica<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL1′ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL2′ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL3′ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL1′<\/p>\n\n

    WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = ‘TCP:\/\/SQL1.hst.com:1433’));<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL2′<\/p>\n\n

    WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = ‘TCP:\/\/SQL2.hst.com:1433’));<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL3′<\/p>\n\n

    WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = ‘TCP:\/\/SQL3.hst.com:1433’));<\/p>\n\n

    — When SQL1 is Primary<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL1′<\/p>\n\n

    WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (‘SQL2’, ‘SQL3’)));<\/p>\n\n

    — When SQL2 is Primary<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL2′<\/p>\n\n

    WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (‘SQL1’, ‘SQL3’)));<\/p>\n\n

    — When SQL3 is Primary<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL3′<\/p>\n\n

    WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (‘SQL1’, ‘SQL2’)));<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL2′ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));<\/p>\n\n

    ALTER AVAILABILITY GROUP [SQLCL]<\/p>\n\n

    MODIFY REPLICA ON N’SQL3′ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));<\/p>\n\n

    How to Verify<\/u><\/strong><\/p>\n\n

    SELECT * FROM sys.availability_read_only_routing_lists;<\/p>\n\n

    SELECT * FROM sys.availability_replicas;<\/p>\n\n

    Connection String for Application<\/u><\/strong><\/p>\n\n

    Server=SQLLI;Database=YourDB;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_siteseo_robots_primary_cat":"","pagelayer_contact_templates":[],"_pagelayer_content":"","neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[17],"tags":[24],"class_list":["post-1167","post","type-post","status-publish","format-standard","hentry","category-mssql","tag-sql-server-read-only-routing"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1167","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/comments?post=1167"}],"version-history":[{"count":1,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1167\/revisions"}],"predecessor-version":[{"id":1169,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1167\/revisions\/1169"}],"wp:attachment":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/media?parent=1167"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/categories?post=1167"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/tags?post=1167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}