By connecting to MS SQL Server DB with applicationIntent=ReadOnly, the client requests a read-only workload when connecting to an AlwaysOn enabled database. The server will enforce the intent at connection time and during a USE database statement but only to an AlwaysOn enabled database. The applicationIntent keyword is used to enable read-only routing.
By default, Talend uses the open-source free JDTS driver to connect to MSSQL in all Talend MSSQL components. Unfortunately, the jtds driver does not [yet] support this method of connecting to MSSQL DBs.
Here’s what the generated code looks like – using the JDTS driver:
Here’s what the generated code looks like – using SQL Server native driver:
The easiest alternative to follow is to use the tJDBC* components in Talend and configure them to use the native MSSQL driver. Here’s what the job looks like in Talend:
Download the sqljdbc4.jar file and add to your component as shown. Here are the connection string and driver class to edit and use in your job:
- "jdbc:sqlserver://hostserver:1433;databaseName=mydb;applicationIntent=ReadOnly"
- "com.microsoft.sqlserver.jdbc.SQLServerDriver"