May 9, 2018 at 4:40 am
Hello,
I and application that is about 60% WRITE and 40% READ. have configured ReadOnly Routing List for SQL 2016 Always On Availability Group based on the Microsoft's guide. here : https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-2017
(1.) When I tried testing it using SQLCMD, I get the error below:
C:\Users\administrator.IMGT>sqlcmd -S IMGT-AVG,5022 -E -d IMGT_DB_Prod -
K ReadOnly
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login.
Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (
memory or maximum allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: An existing connection was forcibly closed by the remote host.
.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection due to prelogin failure.
How else can I test if ReadOnly Routing List is working?
(2.) Since my application is about 60% WRITE and 40% READ, do i need to specify application intent in my Connection String?
Any suggestion on how to address the issues will be appreciated.
May 9, 2018 at 4:45 am
The first paragraph of the link you provided said that you have to provide intent in the connection string, so guessing that's your issue.
May 9, 2018 at 5:52 am
First things first, have you created an Availability Group listener, and does it work for read/write connections?
For read-only connections you do need to specify the application intent in the connection string. The example in the link is below.
Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
Have you tested your read-intent connections in SSMS first? It can be useful to do that before trying to troubleshoot application connections. Before you connect to your AG listener in SSMS, click the Options button, select the Additional Connection Parameters tab and enter ApplicationIntent = ReadOnly. If it's set up correctly you should be routed to a readable replica.
May 9, 2018 at 5:58 am
Beatrix Kiddo - Wednesday, May 9, 2018 5:52 AMFirst things first, have you created an Availability Group listener, and does it work for read/write connections?For read-only connections you do need to specify the application intent in the connection string. The example in the link is below.
Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=TrueHave you tested your read-intent connections in SSMS first? It can be useful to do that before trying to troubleshoot application connections. Before you connect to your AG listener in SSMS, click the Options button, select the Additional Connection Parameters tab and enter ApplicationIntent = ReadOnly. If it's set up correctly you should be routed to a readable replica.
Hi Beatrix,
Yes, using "ApplicationIntent=ReadOnly" in SSMS routed me to the ReadOnly Replica.
I can confirm that it worked with SSMS.
May 9, 2018 at 6:00 am
Rick-153145 - Wednesday, May 9, 2018 4:45 AMThe first paragraph of the link you provided said that you have to provide intent in the connection string, so guessing that's your issue.
Yes, I want to know how to test and ensure that READ request goes to the ReadOnly Replica while WRITE goes to the Primary Node.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply