Print this page
Monday, 30 November 2015 20:23

Connecting Talend to an MS SQL Server DB with 'applicationIntent=ReadOnly'

Written by
Rate this item
(1 Vote)

Out of the box, Talend uses the open source jTDS driver to connect to MS SQL Server databases. This driver however does not support connecting to an AlwaysOn enabled database. A generic jdbc driver would have to be used as a work-around.

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:

f1

Here’s what the generated code looks like – using SQL Server native driver:

f2

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:

f3

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"

 

 

Read 19274 times Last modified on Tuesday, 09 February 2016 21:18
Published in Talend DI
Tagged under
Will Munji

Will Munji is a seasoned data integration, data warehousing and business intelligence (BI) architect & developer who has been working in the DW/BI space for a while. He got his start in BI working on Brio SQR (later Hyperion SQR) and the Crystal Decisions stack (Reports, Analysis & Enterprise) and SAP BusinessObjects / Microsoft BI stacks. He currently focuses on Talend Data Management Suite, Hadoop, SAP BusinessObjects BI stack as well as Jaspersoft and Tableau. He has consulted for many organizations across a variety of industries including healthcare, manufacturing, retail, insurance and banking. At Kindle Consulting, Will delivers DW/BI/Data Integration solutions that range from front-end BI development (dashboards, reports, cube development, T-SQL/ PL/SQL ...) to data services (ETL/ DI development), data warehouse architecture and development, data integration to BI Architecture design and deployment.

Latest from Will Munji

Related items