Sql-server – SQL Server 2016 Always On ReadOnly Routing List

availability-groupssql serversql-server-2016

I have an application that is about 60% write and 40% read. I have configured a Read Only Routing List for SQL 2016 Always On Availability Group based on the Microsoft's guide. here: Configure Read-Only Routing for an Availability Group

  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?

  1. Since my application is about 60% WRITE and 40% READ, do i need to specify application intent in my Connection String?

Any guide on how to test this will be appreciated.

Best Answer

Port 5022 is the endpoint that is used for mirroring, not for client connections. You just need to connect to the listener on same port as usual (1433 if using the default), and the availability group will route the read-only connection to the secondary.

See Connecting to ReadOnly Secondary Replica of SQL Server via the Listener by Default for further explanation.

To test a read-only connection with SQL Server Management Studio, select Options, and on the Additional Connection Parameters tab enter "ApplicationIntent=ReadOnly"). Then use a SELECT @@SERVERNAME query to confirm the connection is routed to the secondary.

Related Question