Connection resiliency in the ODBC driver

Download ODBC driver

To ensure that applications remain connected, the ODBC driver can restore idle connections.

Tábhachtach

The connection resiliency feature is supported on Microsoft Azure SQL Database, Fabric SQL database, and SQL Server 2014 (and later) server versions.

The feature is available on Windows starting with Microsoft ODBC Driver 11 for SQL Server. It is available on Linux starting in version 17.2 of Microsoft ODBC Driver 17 for SQL Server.

For more information about idle connection resiliency, see Technical Article - Idle Connection Resiliency.

To control reconnect behavior, the ODBC Driver for SQL Server has two options:

• Connection retry count.

  Connect retry count controls the number of reconnection attempts if there's a connection failure. Valid values range from 0 to 255. Zero (0) means don't attempt to reconnect. The default value is one reconnection attempt.

  You can modify the number of connection retries when you:

  • Define or modify a data source that uses the ODBC Driver for SQL Server with the Connection Retry Count control.

  • Use the ConnectRetryCount connection string keyword.

    To retrieve the number of connection retry attempts, use the SQL_COPT_SS_CONNECT_RETRY_COUNT (read only) connection attribute. If an application connects to a server that doesn't support connection resiliency, SQL_COPT_SS_CONNECT_RETRY_COUNT returns 0.

• Connect retry interval.

  The connect retry interval specifies the number of seconds between each connection retry attempt. Valid values are 1-60. The total time to reconnect can't exceed the connection timeout (SQL_ATTR_QUERY_TIMEOUT in SQLSetStmtAttr). The default value is 10 seconds.

  You can modify the connection retry interval when you:

  • Define or modify a data source that uses the ODBC Driver for SQL Server with the Connect Retry Interval control.

  • Use the ConnectRetryInterval connection string keyword.

    To retrieve the length of the connection retry interval, use the SQL_COPT_SS_CONNECT_RETRY_INTERVAL (read only) connection attribute.

If an application establishes a connection with SQL_DRIVER_COMPLETE_REQUIRED and later tries to execute a statement over a broken connection, the ODBC driver won't display the dialog box again. Also, during recovery in progress,

• During recovery, any call to SQLGetConnectAttr(SQL_COPT_SS_CONNECTION_DEAD), must return SQL_CD_FALSE.
• If recovery fails, any call to SQLGetConnectAttr(SQL_COPT_SS_CONNECTION_DEAD), must return SQL_CD_TRUE.

The following state codes are returned by any function that executes a command on the server:

State	Message
IMC01	The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts.
IMC02	The server did not acknowledge a recovery attempt, connection recovery is not possible.
IMC03	The server did not preserve the exact client TDS version requested during a recovery attempt, connection recovery is not possible.
IMC04	The server did not preserve the exact server major version requested during a recovery attempt, connection recovery is not possible.
IMC05	The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection.
IMC06	The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.

Example

The following sample contains two functions. func1 shows how you can connect with a data source name (DSN) that uses the ODBC Driver for SQL Server on Windows. The DSN uses SQL Server Authentication, and it specifies the user ID. func1 then retrieves the number of connection retries with SQL_COPT_SS_CONNECT_RETRY_COUNT.

func2 uses SQLDriverConnect, ConnectRetryCount connection string keyword, and connection attributes to retrieve the setting for connection retries and retry interval.

C++

// Connection_resiliency.cpp
// compile with: odbc32.lib
#include <windows.h>
#include <stdio.h>
#include <sqlext.h>
#include <msodbcsql.h>

void func1() {
    SQLHENV henv;
    SQLHDBC hdbc;
    SQLHSTMT hstmt;
    SQLRETURN retcode;
    SQLSMALLINT i = 21;

    // Allocate environment handle
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

    // Set the ODBC version environment attribute
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

        // Allocate connection handle
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

            // Set login timeout to 5 seconds
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

                // Connect to data source
                retcode = SQLConnect(hdbc, (SQLCHAR*)"MyDSN", SQL_NTS, (SQLCHAR*)"userID", SQL_NTS, (SQLCHAR*)"password_for_userID", SQL_NTS);
                retcode = SQLGetConnectAttr(hdbc, SQL_COPT_SS_CONNECT_RETRY_COUNT, &i, SQL_IS_INTEGER, NULL);

                // Allocate statement handle
                if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

                    // Process data
                    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
                    }

                    SQLDisconnect(hdbc);
                }

                SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
            }
        }
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
    }
}

void func2() {
    SQLHENV henv;
    SQLHDBC hdbc1;
    SQLHSTMT hstmt;
    SQLRETURN retcode;
    SQLSMALLINT i = 21;

#define MAXBUFLEN 255

    SQLCHAR ConnStrIn[MAXBUFLEN] = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=server_that_supports_connection_resiliency;Encrypt=yes;UID=userID;PWD=<password>;ConnectRetryCount=2";
    SQLCHAR ConnStrOut[MAXBUFLEN];

    SQLSMALLINT cbConnStrOut = 0;

    // Allocate environment handle
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

    // Set the ODBC version environment attribute
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {

        retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3_80, SQL_IS_INTEGER);

        // Allocate connection handle
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

            // Set login timeout to 5 seconds
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                // SQLSetConnectAttr(hdbc1, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

                retcode = SQLDriverConnect(hdbc1, NULL, ConnStrIn, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
            }
            retcode = SQLGetConnectAttr(hdbc1, SQL_COPT_SS_CONNECT_RETRY_COUNT, &i, SQL_IS_INTEGER, NULL);
            retcode = SQLGetConnectAttr(hdbc1, SQL_COPT_SS_CONNECT_RETRY_INTERVAL, &i, SQL_IS_INTEGER, NULL);
        }
    }
}

int main() {
    func1();
    func2();
}

See also

Microsoft ODBC Driver for SQL Server