1

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.

2

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.

0

Tony's answer is correct and addresses item #1 perfectly.

To your application question:

...do i need to specify application intent in my Connection String?

You absolutely do need to specify intent when making read-only queries from your application in order for the listener to know to route queries to the readable secondary. From the docs page you linked to:

...and the client's connection strings must specify the application intent as "read-only."

One way to accomplish this is to maintain two connection strings in your app. A default connection:

Data Source=YourInstanceName;Database=YourDatabaseName;Integrated Security=SSPI

And a read connection:

Data Source=YourInstanceName;Database=YourDatabaseName;Integrated Security=SSPI;ApplicationIntent=ReadOnly

Any query that you know is completely read-only would then use the read-only connection string.

A caution about ORMs

You should be especially careful about leveraging read-only connections if your application uses an ORM for data access. They make it very easy to change data in application code (even accidentally!) and "auto-flush" updates back to the server.

If you used the read-only connection for that, AlwaysOn will route the query to the secondary and it will fail with this message:

Failed to update database "YourDatabaseName" because the database is read-only

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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