ODBC/JDBC connection to Microsoft sql server database with Always on availability group listener

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Does Automic Workload Automation support Microsoft sql server database clustering with Always on availability group listener configuration across the multiple subnets? 

If so, Are there any changes to the database connection string required? 

Environment

Release : 21.0.x and 24.x

Component : AUTOMATION ENGINE

Resolution

Automic does support Microsoft sql server database clustering with Always on availability group listener configuration across the multiple subnets.

It is a configuration in ODBC/JDBC connection settings. 

ODBC Connection -While creating the new DSN in ODBC GUI select 'Multi-subnet failover' check-box.

If using 'DSN-less' then set the 'MultiSubnetFailover=Yes' on the 'sqlDriverConnect' string -

sqlDriverConnect=ODBCVAR=NNNNNNRN,Driver={ODBC Driver 11 for SQL Server};Server=tcp:<database_server>,<port>;Database=<database_name>;Uid=<userid>;Pwd=<password>;Mars_Connection=Yes;MultiSubnetFailover=Yes

And for the JDBC connection the parameter for the sqlDriverConnect string would be 'MultiSubnetFailover=true' -

sqlDriverConnect=jdbc:sqlserver://<database_server>:<port>;databaseName=<database_name>;MultiSubnetFailover=true

The parameter "multiSubnetFailover=true" enables TCP connection attempts faster than the operating system's default TCP retransmit intervals. Microsoft recommends using this setting for both single and multi-subnet connections to availability groups listeners and to SQL Server Failover Cluster Instance names. Enabling this option adds additional optimizations, even for single-subnet scenarios.

Additional Information

Microsoft documentation on Connect to an Always On availability group listener -

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover?view=sql-server-ver15#SupportAgMultiSubnetFailover