MSSQL Server (Always On availability groups) configuration

Scenario

A listener sits in front of multiple SQL nodes.

Best practice

Point HCL Workload Automation to the Availability Group Listener and enable the multiSubnetFailover property if the nodes are located in different data centers. The following example shows the XML configuration:

<dataSource id="twsdb" jndiName="jdbc/twsdb">
                <connectionManager maxPoolSize="100"/>
                <properties.microsoft.sqlserver 
                serverName="ag-listener.example.com" 
                portNumber="1433" 
                databaseName="TWS" 
                multiSubnetFailover="true" 
                loginTimeout="30"/>
                </dataSource>
Note: The multiSubnetFailover="true" property is vital if your SQL nodes are located in different IP subnets (disaster recovery scenarios), because it allows the driver to try all IP addresses associated with the listener simultaneously.