Windows ODBC 驅動程式中的連接恢復功能Connection Resiliency in the Windows ODBC Driver

下載下載 ODBC 驅動程式DownloadDownload ODBC Driver

若要確保應用程式仍然連線到 Azure SQL DatabaseAzure SQL Database,Windows 上的 ODBC 驅動程式可以還原閒置的連線。To ensure that applications remain connected to an Azure SQL DatabaseAzure SQL Database, the ODBC driver on Windows can restore idle connections.

重要

Microsoft Azure SQL Database 與 SQL Server 2014 (和更新版本) 伺服器版本都支援連線復原功能。The connection resiliency feature is supported on Microsoft Azure SQL Database and SQL Server 2014 (and later) server versions.

如需閒置連線恢復功能的詳細資訊,請參閱技術文章 - 閒置連線恢復功能For additional information about idle connection resiliency, see Technical Article - Idle Connection Resiliency.

為了控制重新連接的行為,Windows 上的 ODBC Driver for SQL ServerSQL Server 有兩個選項:To control reconnect behavior, the ODBC Driver for SQL ServerSQL Server on Windows has two options:

  • 連接重試計數。Connection retry count.

    連接重試計數可控制連接失敗時的重新連接嘗試次數。Connect retry count controls the number of reconnection attempts if there is a connection failure. 有效值的範圍介於 0 到 255 之間。Valid values range from 0 to 255. 零 (0) 表示不嘗試重新連接。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:

    • 透過連接重試計數控制項定義或修改使用 ODBC Driver for SQL ServerSQL Server 的資料來源。Define or modify a data source that uses the ODBC Driver for SQL ServerSQL Server with the Connection Retry Count control.

    • 使用 ConnectRetryCount 連接字串關鍵字。Use the ConnectRetryCount connection string keyword.

    若要擷取連線重試的嘗試次數,請使用 SQL_COPT_SS_CONNECT_RETRY_COUNT (唯讀) 連線屬性。To retrieve the number of connection retry attempts, use the SQL_COPT_SS_CONNECT_RETRY_COUNT (read only) connection attribute. 如果應用程式連線到不支援連線恢復功能的伺服器,SQL_COPT_SS_CONNECT_RETRY_COUNT 會傳回 0。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. 有效值為 1-60。Valid values are 1-60. 重新連接的時間總計不可超過連接逾時 (SQLSetStmtAttr 中的 SQL_ATTR_QUERY_TIMEOUT)。The total time to reconnect cannot exceed the connection timeout (SQL_ATTR_QUERY_TIMEOUT in SQLSetStmtAttr). 預設值為 10 秒。The default value is 10 seconds.

    您可以在執行下列作業時修改連接重試間隔:You can modify the connection retry interval when you:

    • 透過連接重試間隔控制項定義或修改使用 ODBC Driver for SQL ServerSQL Server 的資料來源。Define or modify a data source that uses the ODBC Driver for SQL ServerSQL Server with the Connect Retry Interval control.

    • 使用 ConnectRetryInterval 連接字串關鍵字。Use the ConnectRetryInterval connection string keyword.

    若要擷取連線重試間隔的長度,請使用 SQL_COPT_SS_CONNECT_RETRY_INTERVAL (唯讀) 連線屬性。To retrieve the length of the connection retry interval, use the SQL_COPT_SS_CONNECT_RETRY_INTERVAL (read only) connection attribute.

如果應用程式透過 SQL_DRIVER_COMPLETE_REQUIRED 建立連接,且後續嘗試透過中斷的連接執行陳述式,ODBC 驅動程式將不會再次顯示對話方塊。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,

  • 在復原期間,任何對 SQLGetConnectAttr(SQL_COPT_SS_CONNECTION_DEAD) 的呼叫,都必須傳回 SQL_CD_FALSEDuring recovery, any call to SQLGetConnectAttr(SQL_COPT_SS_CONNECTION_DEAD), must return SQL_CD_FALSE.

  • 如果復原失敗,任何對 SQLGetConnectAttr(SQL_COPT_SS_CONNECTION_DEAD) 的呼叫,都必須傳回 SQL_CD_TRUEIf 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:

StateState 訊息Message
IMC01IMC01 連接中斷,且無法復原。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. 請提高 ConnectRetryCount 的值,以增加復原嘗試次數。Increase the value of ConnectRetryCount to increase the number of recovery attempts.
IMC02IMC02 伺服器未認可復原嘗試,無法進行連接復原。The server did not acknowledge a recovery attempt, connection recovery is not possible.
IMC03IMC03 伺服器未保存在復原嘗試期間要求的確切用戶端 TDS 版本,無法進行連接復原。The server did not preserve the exact client TDS version requested during a recovery attempt, connection recovery is not possible.
IMC04IMC04 伺服器未保存在復原嘗試期間要求的確切伺服器主要版本,無法進行連接復原。The server did not preserve the exact server major version requested during a recovery attempt, connection recovery is not possible.
IMC05IMC05 連接中斷,且無法復原。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.
IMC06IMC06 連接中斷,且無法復原。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 說明如何連接使用 Windows 版 ODBC Driver for SQL ServerSQL Server 的資料來源名稱 (DSN)。func1 shows how you can connect with a data source name (DSN) that uses the ODBC Driver for SQL ServerSQL Server on Windows. DSN 會使用 SQL ServerSQL Server 驗證,而且它會指定使用者識別碼。The DSN uses SQL ServerSQL Server Authentication, and it specifies the user ID. 然後,func1 會透過 SQL_COPT_SS_CONNECT_RETRY_COUNT 擷取連接重試次數。func1 then retrieves the number of connection retries with SQL_COPT_SS_CONNECT_RETRY_COUNT.

func2 會使用 SQLDriverConnectConnectRetryCount 連接字串關鍵字和連接屬性來擷取連接重試與重試間隔的設定。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;  

   // 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 17 for SQL Server};SERVER=server_that_supports_connection_resiliency;UID=userID;PWD= password_for_userID;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

Windows 上的 Microsoft ODBC Driver for SQL ServerMicrosoft ODBC Driver for SQL Server on Windows