With AlwaysOn availability group, we can create the readable secondary to offload the query load on the primary. This post is the walk through how to set up the readable seconday.
There were three options in the readable secondary configuration.
- NO: default, no readable secondary. The secondary database is not accessible.
- Read-intent Only: The secondary database is accessible when use keyword , Applicationintent=ReadOnly in the connection string.
- Yes: User can connect to the database without Applicationintent=ReadOnly in the connection string but only allow read only actions.
The SGC8\SGC8SQL2014 instance is set to YES, therefore, we can directly connect to the database without issue.
However, SGC5\SGC5SQL2014 is still not accusable through the normal connection. Even we specify the READONLY in the application intent , we still end up with primary ( SGC4). In order to utilize the application intent, we have to configure the READ ONLY routing first.
Configure read only routing
The key point to configure the READ ONLY routing is READ_ONLY_ROUING_URL. If the port number is not configure properly, you will get all kind of strange error such as below:
This post has great script to identify the correct port number and below is the script to create the read only routing list.
Test read only routing
From SSMS, we can add the connection parameter.
From sqlcmd, we can use –K readonly