Connection Resiliency in the Windows ODBC Driver

Connection Resiliency in the Windows ODBC Driver

 

DownloadDownload ODBC Driver

To ensure that applications remain connected to a Azure SQL Database, the ODBC driver on Windows can restore idle connections.

System_CAPS_ICON_important.jpg Important


The connection resiliency feature is supported on Microsoft Azure SQL Databases and SQL Server 2014 (and later) server versions.

For additional information about idle connection resiliency, see Technical Atricle – Idle Connection Resiliency.

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

  • Connection retry count.

    Connect retry count controls the number of reconnection attempts if there is a connection failure. Valid values range from 0 to 255. Zero (0) means do not 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 does not 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 cannot 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 will not 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_TRUE.

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

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

StateMessage
IMC01The 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.
IMC02The server did not acknowledge a recovery attempt, connection recovery is not possible.
IMC03The server did not preserve the exact client TDS version requested during a recovery attempt, connection recovery is not possible.
IMC04The server did not preserve the exact server major version requested during a recovery attempt, connection recovery is not possible.
IMC05The 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.
IMC06The 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.

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.

// 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;  
  
   SQLCHAR * OutConnStr = (SQLCHAR * )malloc(255);  
   SQLSMALLINT * OutConnStrLen = (SQLSMALLINT *)malloc(255);  
  
   // 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 11 for SQL Server};SERVER=server_that_supports_connection_resiliency;UID=userID;PWD= password_for_userID;ConnectRetryCount=2";  
   SQLCHAR ConnStrOut[MAXBUFLEN];  
  
   // SQLSMALLINT OutConnStrLen;  
   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();  
}  

Microsoft ODBC Driver for SQL Server on Windows

Community Additions

ADD
Show:
© 2016 Microsoft