OpenLink SQL Server ODBC Driver Multi Subnet Failover Support
Support has been added to the OpenLink SQL Server ODBC driver, version 09.00.230621 and above, for Microsoft SQL Server Multi-Subnet Clustering Failover. Multi Subnet Failover works with TDSver >= 7.1 (-V connection option) and can be configured with the following new connection options in the Options param of an ODBC DSN:
-
–msf - This flag switch ON MultiSubnetFailover mode
-
–conn-tm xxx - Connection timeout, where “xxx” is timeout in msec.
Thus the Options param in the odbc.ini file would be something like:
Driver = /usr/home/sql_mt_lt.so
Options = -H hostname -P 1433 -V 7.1 --msf --conn-tm 5000
Database = pubs
...
A DNS record name with two or more IP addresses associated with it referencing the IP addresses of the SQL Server instances that are part for the SQL Server Multi-Subnet Cluster on the client machine. This DNS record name will be used as the hostname (-H connection option) passed in the Options param of the DSN.
For example is you have two SQL Server instances on different IP addresses a DNS record for them can be added to the local /etc/hosts file, as follows:
44.203.105.238 wsql
44.200.231.146 wsql
each IP address must have Microsoft SQL server instances on the same PORT number as would be the case for a cluster if it is to automatically fail over.
Linux
A Linux ODBC DSN can then be configured of the form:
[sqllu_uda9]
Driver = /home/ubuntu/uda90/lib/sql_mt_lu.so
ServerType = SQLServer
Username =
Password =
Database = Northwind
Options = -H wsql -P 1433 -V 7.1 --msf --conn-tm 5000
FetchBufferSize = 99
ReadOnly = No
JetFix = No
Description = Sample SQLServer Lite Connection
WideAsUTF16 = Y
and test connection made using iodbctest:
$ /opt/uda8/bin/iodbctestw sqllu_uda9
iODBC Unicode Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.1420.0305
Driver: 09.00.230609 OpenLink SQL Server Lite Driver (sql_mt_lu.so)
SQL>select @@version
EXPR_1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64)
Apr 1 2023 12:10:46
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>
result set 1 returned 1 rows.
SQL>
macOS
For the macOS OpenLink SQL Server ODBC Driver the SQL Server connection advanced attributes dialog of the ODBC Setup dialog contains Multi Subnet Failover check box to enable the feature and Conn timeout text window for setting the connection timeout for failover:
Windows
For the Windows OpenLink SQL Server ODBC Driver the TDS connection advanced attributes dialog of the ODBC Setup dialog contains Multi Subnet Failover check box to enable the feature and Conn timeout text window for setting the connection timeout for failover:
Testing
Setup a SQL Server Multi-Subnet Failover Cluster with at least two separate instances.
Alternative 2 or more separate SQL Server instances can be setup running on the same SQL Server port number. Using Docker 2 separate SQL Server instances can be created using the available Microsoft SQL Server Docker Containers, with a command like:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" -e "MSSQL_PID=Express" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
Notes
The connection is always tried to the first SQL Server instance name in the DNS record name list and if that first instance is shutdown or inaccessible for any reason, the connection will then be made to the next IP address in the list.
Creating a Fee TDS log with export TDSDUMP=filename command the connection process using the IP addresses in the DNS record name list can be seen:
log.c:190:Starting log file for FreeTDS 0.96.dev.20210809
on 2023-06-13 15:34:56 with debug flags 0x4fff.
iconv.c:197:names for ISO-8859-1: ISO-8859-1
iconv.c:197:names for UTF-8: UTF-8
iconv.c:197:names for UCS-2LE: UCS-2LE
iconv.c:197:names for UCS-2BE: UCS-2BE
iconv.c:363:iconv to convert client-side data to the "utf8" character set
iconv.c:516:tds_iconv_info_init: converting "UTF-8"->"UCS-2LE"
iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
login.c:524:getaddrinfo for host wsql
net.c:406:[thr=0] Connecting to 44.203.105.238 port 1433 [IPv4]
login.c:349:Wait connection...
net.c:406:[thr=1] Connecting to 44.200.231.146 port 1433 [IPv4]
net.c:463:[thr=0] tds_open_socket: connect(2) returned "Operation now in progress"
net.c:463:[thr=1] tds_open_socket: connect(2) returned "Operation now in progress"
net.c:504:[thr=0] tds_open_socket() succeeded
login.c:356:Recv signal done_count=1 done_id=0
login.c:359:Connection received 0 rc=1
login.c:370:Done connection waiting[1]... id=0
net.c:1256:Sending packet
...