Export (0) Print
Expand All

Connection Resiliency in the Windows ODBC Driver

SQL Server 2012

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

Important note 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:

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.

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();
}
Show:
© 2014 Microsoft