SQL Server "Application Intent=readonly" not working JDBC

Question

Im am trying to hit the secondary node on a sql server 2012 DB using "ApplicationIntent=readonly" in the connection string.

It is still hitting the primary node, not sure why.

I'm using sqljdbc4-4.0 jar

java JDBC call:

ResultSet results = null;
PreparedStatement preparedStatement = null;
Connection conn = null;

String SQL_SERVER_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
Class.forName(SQL_SERVER_DRIVER);

String dbString = "jdbc:sqlserver://*****;applicationIntent=ReadOnly;databaseName=****;user=*****;password=****;";
    
conn = DriverManager.getConnection(dbString);
preparedStatement = conn.prepareStatement("some_sql");

results = preparedStatement.executeQuery(); 

Shem

Do you have a answer for this? We are also experiencing this problem – Helen

Did you ever figure out how to get it to work properly? – Mannuela

Answer 1

You should connect to availability group listener (AGL) address(es). you can use below query to find availability group listener addresses:

select * from sys.availability_group_listener_ip_addresses 

after you find the AGL address your jdbc connection string should be :

jdbc:sqlserver://[AGL-ADDRESS]:1433;databaseName=[DB_NAME];user=[username];password=[password];applicationIntent=ReadOnly

Smile

Answer 2

For DBeaver Users:

For Copy Paste: ApplicationIntent ReadOnly

Skate