高可用性/災害復原的 SQL Server Native Client 支援SQL Server Native Client Support for High Availability, Disaster Recovery

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

本主題將討論適用於 SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x) Native Client 支援 (在 AlwaysOn 可用性群組Always On availability groups 中所新增)。This topic discusses SQL ServerSQL Server Native Client support (added in SQL Server 2012 (11.x)SQL Server 2012 (11.x)) for AlwaysOn 可用性群組Always On availability groups. 如需 AlwaysOn 可用性群組Always On availability groups 的詳細資訊,請參閱可用性群組接聽程式、用戶端連接性及應用程式容錯移轉 (SQL Server)建立及設定可用性群組 (SQL Server)容錯移轉叢集和 AlwaysOn 可用性群組 (SQL Server)使用中次要:可讀取的次要複本 (AlwaysOn 可用性群組)For more information about AlwaysOn 可用性群組Always On availability groups, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server), Creation and Configuration of Availability Groups (SQL Server), Failover Clustering and AlwaysOn Availability Groups (SQL Server), and Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups).

您可以在連接字串中指定給定可用性群組的可用性群組接聽程式。You can specify the availability group listener of a given availability group in the connection string. 如果 SQL ServerSQL Server Native Client 應用程式連接到可用性群組中發生容錯移轉的資料庫,則原始連接會中斷,而且應用程式必須在容錯移轉後開啟新連接,才能繼續工作。If a SQL ServerSQL Server Native Client application is connected to a database in an availability group that fails over, the original connection is broken, and the application must open a new connection to continue work after the failover.

如果未連接到可用性群組接聽程式,而且如果多個 IP 位址與主機名稱相關聯,SQL ServerSQL Server Native Client 會循序逐一查看與 DNS 項目相關聯的所有 IP 位址。If you are not connecting to an availability group listener, and if multiple IP addresses are associated with a hostname, SQL ServerSQL Server Native Client will iterate sequentially through all IP addresses associated with DNS entry. 如果 DNS 伺服器所傳回的第一個 IP 位址未繫結至任何網路介面卡 (NIC),這項作業可能會很費時。This can be time consuming if the first IP address returned by DNS server is not bound to any network interface card (NIC). 在連接到可用性群組接聽程式時,SQL ServerSQL Server Native Client 會嘗試平行建立與所有 IP 位址的連接,如果某個連接嘗試成功,驅動程式就會捨棄任何暫止的連接嘗試。When connecting to an availability group listener, SQL ServerSQL Server Native Client attempts to establish connections to all IP addresses in parallel and if a connection attempt succeeds, the driver will discard any pending connection attempts.

注意

增加連接逾時並實作連接重試邏輯可提高應用程式連接到可用性群組的機率。Increasing connection timeout and implementing connection retry logic will increase the probability that an application will connect to an availability group. 此外,因為連接可能會由於可用性群組容錯移轉而失敗,所以您應該實作連接重試邏輯,並重試失敗的連接,直到重新連接為止。Also, because a connection can fail because of an availability group failover, you should implement connection retry logic, retrying a failed connection until it reconnects.

使用 MultiSubnetFailover 進行連接Connecting With MultiSubnetFailover

在連接到 SQL Server 2012 可用性群組接聽程式或 SQL Server 2012 容錯移轉叢集執行個體時,永遠指定 MultiSubnetFailover=YesAlways specify MultiSubnetFailover=Yes when connecting to a SQL Server 2012 availability group listener or SQL Server 2012 Failover Cluster Instance. MultiSubnetFailover會針對 SQL Server 2012 中的所有可用性群組和容錯移轉叢集實例啟用更快速的容錯移轉,並大幅縮短單一和多重子網 Always On 拓撲的容錯移轉時間。MultiSubnetFailover enables faster failover for all Availability Groups and failover cluster instance in SQL Server 2012 and will significantly reduce failover time for single and multi-subnet Always On topologies. 在多重子網路容錯移轉期間,用戶端會平行嘗試連接。During a multi-subnet failover, the client will attempt connections in parallel. 在子網路容錯移轉期間,SQL ServerSQL Server Native Client 會積極重試 TCP 連接。During a subnet failover, SQL ServerSQL Server Native Client will aggressively retry the TCP connection.

MultiSubnetFailover 連接屬性指示,應用程式正在可用性群組或容錯移轉叢集執行個體中部署,而且 SQL ServerSQL Server Native Client 會透過嘗試連接到所有 IP 位址,嘗試連接到主要 SQL ServerSQL Server 執行個體的資料庫。The MultiSubnetFailover connection property indicates that the application is being deployed in an availability group or Failover Cluster Instance, and that SQL ServerSQL Server Native Client will try to connect to the database on the primary SQL ServerSQL Server instance by trying to connect to all the IP addresses. 為連接指定 MultiSubnetFailover=Yes 時,用戶端會重試 TCP 連接,其速度比作業系統的預設 TCP 重新傳輸間隔更快。When MultiSubnetFailover=Yes is specified for a connection, the client retries TCP connection attempts faster than the operating system's default TCP retransmit intervals. 這可在容錯移轉 Always On 可用性群組或 Always On 容錯移轉叢集實例之後,加速重新連接,同時適用于單一和多重子網可用性群組和容錯移轉叢集實例。This enables faster reconnection after failover of either an Always On Availability Group or an Always On Failover Cluster Instance, and is applicable to both single- and multi-subnet Availability Groups and Failover Cluster Instances.

如需連接字串關鍵字的詳細資訊,請參閱搭配 SQL Server Native Client 使用連接字串關鍵字For more information about connection string keywords, see Using Connection String Keywords with SQL Server Native Client.

當連接到可用性群組接聽程式或容錯移轉叢集執行個體以外的某個項目時,指定 MultiSubnetFailover=Yes 將會產生負面效能影響,而且不支援這樣的處理方式。Specifying MultiSubnetFailover=Yes when connecting to something other than an availability group listener or Failover Cluster Instance may result in a negative performance impact, and is not supported.

請使用下列指導方針,連接到可用性群組或容錯移轉叢集執行個體中的伺服器:Use the following guidelines to connect to a server in an availability group or Failover Cluster Instance:

  • 如果在連接到單一子網路或多重子網路時使用 MultiSubnetFailover 連接屬性,則會提高兩者的效能。Use the MultiSubnetFailover connection property when connecting to a single subnet or multi-subnet; it will improve performance for both.

  • 若要連接到可用性群組,在連接字串中指定可用性群組的可用性群組接聽程式做為伺服器。To connect to an availability group, specify the availability group listener of the availability group as the server in your connection string.

  • 連接到設定超過 64 個 IP 位址的 SQL ServerSQL Server 執行個體會導致連接失敗。Connecting to a SQL ServerSQL Server instance configured with more than 64 IP addresses will cause a connection failure.

  • 根據驗證的類型,使用 MultiSubnetFailover 連接屬性之應用程式的行為不會受到影響:SQL ServerSQL Server 驗證、Kerberos 驗證或 Windows 驗證。Behavior of an application that uses the MultiSubnetFailover connection property is not affected based on the type of authentication: SQL ServerSQL Server Authentication, Kerberos Authentication, or Windows Authentication.

  • 您可以增加 loginTimeout 的值,以容納容錯移轉時間並減少應用程式連接重試次數。You can increase the value of loginTimeout to accommodate for failover time and reduce application connection retry attempts.

  • 不支援分散式交易。Distributed transactions are not supported.

如果唯讀路由不在作用中,在下列狀況下,連接到可用性群組中的次要複本位置將會失敗:If read-only routing is not in effect, connecting to a secondary replica location in an availability group will fail in the following situations:

  1. 如果未設定次要複本位置接受連接。If the secondary replica location is not configured to accept connections.

  2. 如果應用程式使用 ApplicationIntent=ReadWrite,而且已針對唯讀存取設定次要複本位置。If an application uses ApplicationIntent=ReadWrite (discussed below) and the secondary replica location is configured for read-only access.

如果設定主要複本拒絕唯讀工作負載,而且連接字串包含 ApplicationIntent=ReadOnly,則連接會失敗。A connection will fail if a primary replica is configured to reject read-only workloads and the connection string contains ApplicationIntent=ReadOnly.

從資料庫鏡像升級到使用多子重網路叢集Upgrading to Use Multi-Subnet Clusters from Database Mirroring

如果連接字串中有 MultiSubnetFailoverFailover_Partner 連接關鍵字,則會發生連接錯誤。A connection error will occur if the MultiSubnetFailover and Failover_Partner connection keywords are present in the connection string. 如果使用 MultiSubnetFailover 而且 SQL ServerSQL Server 傳回容錯移轉夥伴回應,指出它是資料庫鏡像配對的一部分,也會發生錯誤。An error will also occur if MultiSubnetFailover is used and the SQL ServerSQL Server returns a failover partner response indicating it is part of a database mirroring pair.

如果您將目前使用資料庫鏡像的 SQL ServerSQL Server Native Client 應用程式升級為多重子網路案例,應該移除 Failover_Partner 連接屬性,以設為 YesMultiSubnetFailover 取代它,並以可用性群組接聽程式取代連接字串中的伺服器名稱。If you upgrade a SQL ServerSQL Server Native Client application that currently uses database mirroring to a multi-subnet scenario, you should remove the Failover_Partner connection property and replace it with MultiSubnetFailover set to Yes and replace the server name in the connection string with an availability group listener. 如果連接字串使用 Failover_PartnerMultiSubnetFailover=Yes,驅動程式會發生錯誤。If a connection string uses Failover_Partner and MultiSubnetFailover=Yes, the driver will generate an error. 不過,如果連接字串使用 Failover_PartnerMultiSubnetFailover=No (或 ApplicationIntent=ReadWrite),應用程式就會使用資料庫鏡像。However, if a connection string uses Failover_Partner and MultiSubnetFailover=No (or ApplicationIntent=ReadWrite), the application will use database mirroring.

如果可用性群組中的主要資料庫使用資料庫鏡像,而且如果在連接到主要資料庫 (而不是可用性群組接聽程式) 的連接字串中使用 MultiSubnetFailover=Yes,驅動程式會傳回錯誤。The driver will return an error if database mirroring is used on the primary database in the availability group, and if MultiSubnetFailover=Yes is used in the connection string that connects to a primary database instead of to an availability group listener.

指定應用程式意圖Specifying Application Intent

關鍵字ApplicationIntent可以在您的連接字串中指定。The keyword ApplicationIntent can be specified in your connection string. 指派的值為ReadWrite或是ReadOnlyThe assignable values are ReadWrite or ReadOnly. 預設值是ReadWriteThe default is ReadWrite.

ApplicationIntent = ReadOnly,用戶端在連接時要求讀取工作負載。When ApplicationIntent=ReadOnly, the client requests a read workload when connecting. 在連接時間和期間,伺服器強制執行的意圖使用資料庫陳述式。The server enforces the intent at connection time, and during a USE database statement.

ApplicationIntent 關鍵字不適用於舊版唯讀資料庫。The ApplicationIntent keyword does not work with legacy read-only databases.

為 ReadOnly 的目標Targets of ReadOnly

當連線選擇ReadOnly,連線會指派給任何下列資料庫可能存在的特殊組態:When a connection chooses ReadOnly, the connection is assigned to any of the following special configurations that might exist for the database:

  • Always OnAlways On

    • 資料庫可以允許或不允許 Always On 目標資料庫上的讀取工作負載。A database can allow or disallow read workloads on the targeted Always On database. 這項選擇使用控制ALLOW_CONNECTIONS子句PRIMARY_ROLESECONDARY_ROLE TRANSACT-SQL 陳述式。This choice is controlled by using the ALLOW_CONNECTIONS clause of the PRIMARY_ROLE and SECONDARY_ROLE Transact-SQL statements.
  • 異地複寫Geo-Replication

  • 讀取相應放大Read Scale-Out

如果沒有任何這些特殊目標可用時,一般的資料庫會從讀取。If none of those special targets are available, the regular database is read from.

 

ApplicationIntent關鍵字可讓唯讀路由The ApplicationIntent keyword enables read-only routing.

唯讀路由Read-Only Routing

唯讀路由功能可確保資料庫之唯讀複本的可用性。Read-only routing is a feature that can ensure the availability of a read-only replica of a database. 若要啟用唯讀路由,下列所有項目適用於:To enable read-only routing, all of the following apply:

  • 您必須連接到 AlwaysOn 可用性群組的可用性群組接聽程式。You must connect to an Always On Availability Group availability group listener.

  • ApplicationIntent 連接字串關鍵字必須設為 ReadOnlyThe ApplicationIntent connection string keyword must be set to ReadOnly.

  • 可用性群組必須由資料庫管理員設定為啟用唯讀路由。The Availability Group must be configured by the database administrator to enable read-only routing.

多個連線使用唯讀路由不是所有可能會連線至相同的唯讀複本。Multiple connections each using read-only routing might not all connect to the same read-only replica. 資料庫同步處理的變更或伺服器路由組態的變更,可能會導致用戶端連接至不同的唯讀複本。Changes in database synchronization or changes in the server's routing configuration can result in client connections to different read-only replicas. 您可以確保所有唯讀要求連接至相同的唯讀複本。You can ensure that all read-only requests connect to the same read-only replica. 請確定此相同性所傳遞至可用性群組接聽程式Server連接字串關鍵字。Ensure this sameness by not passing an availability group listener to the Server connection string keyword. 請改為指定唯讀執行個體的名稱。Instead, specify the name of the read-only instance.

唯讀路由可能會花費超過連接到主要複本。Read-only routing may take longer than connecting to the primary. 等候較久是因為唯讀路由會先連線到主要複本,然後尋找最佳可用的可讀次要複本。The longer wait is because read-only routing first connects to the primary, and then looks for the best available readable secondary. 由於這些多個 staps,您應該增加登入逾時為至少 30 秒。Due to these multiple staps, you should increase your login timeout to at least 30 seconds.

ODBCODBC

已加入兩個 ODBC 連接字串關鍵字來支援 AlwaysOn 可用性群組Always On availability groups Native Client 中的 SQL ServerSQL ServerTwo ODBC connection string keywords were added to support AlwaysOn 可用性群組Always On availability groups in SQL ServerSQL Server Native Client:

  • ApplicationIntentApplicationIntent

  • MultiSubnetFailoverMultiSubnetFailover

如需 SQL ServerSQL Server Native Client 中 ODBC 連接字串關鍵字的詳細資訊,請參閱搭配 SQL Server Native Client 使用連接字串關鍵字For more information about ODBC connection string keywords in SQL ServerSQL Server Native Client, see Using Connection String Keywords with SQL Server Native Client.

同等的連接屬性如下:The equivalent connection properties are:

  • SQL_COPT_SS_APPLICATION_INTENTSQL_COPT_SS_APPLICATION_INTENT

  • SQL_COPT_SS_MULTISUBNET_FAILOVERSQL_COPT_SS_MULTISUBNET_FAILOVER

如需 SQL ServerSQL Server Native Client 中 ODBC 連接屬性的詳細資訊,請參閱 SQLSetConnectAttrFor more information about ODBC connection properties in SQL ServerSQL Server Native Client, see SQLSetConnectAttr.

開始, ApplicationIntentMultiSubnetFailoverSQL ServerSQL Server 關鍵字的功能將會公開在使用 SQL Server 2012 (11.x)SQL Server 2012 (11.x) Native Client 驅動程式之 DSN 的 ODBC 資料來源管理員中。The functionality of the ApplicationIntent and MultiSubnetFailover keywords will be exposed in the ODBC Data Source Administrator for DSNs that use the SQL ServerSQL Server Native Client driver, beginning in SQL Server 2012 (11.x)SQL Server 2012 (11.x).

SQL ServerSQL Server Native Client ODBC 應用程式可以使用三個函數的其中一個進行連接:A SQL ServerSQL Server Native Client ODBC application can use one of three functions to make the connection:

函數Function 描述Description
SQLBrowseConnectSQLBrowseConnect SQLBrowseConnect 傳回的伺服器清單不包括 VNN。The list of servers returned by SQLBrowseConnect will not include VNNs. 您只會看到伺服器清單,無從得知伺服器是否為獨立伺服器或是 Windows Server 容錯移轉叢集 (WSFC) 中,包含兩個或多個已啟用 SQL ServerSQL ServerAlwaysOn 可用性群組Always On availability groups 執行個體的主要或次要伺服器。You will only see a list of servers without any indication if the server is a standalone server, or a primary or secondary server in a Windows Server Failover Clustering (WSFC) cluster that contains two or more SQL ServerSQL Server instances that have been enabled for AlwaysOn 可用性群組Always On availability groups. 如果您連接到伺服器而且發生失敗狀況,可能是因為您已經連接到伺服器,而且 ApplicationIntent 設定與伺服器組態不相容。If you connect to a server and get a failure, it may be because you have connected to a server, and the ApplicationIntent setting is not compatible with the server configuration.

因為 SQLBrowseConnect 無法辨識 Windows Server 容錯移轉叢集 (WSFC) 中,包含兩個或多個已啟用 SQL ServerSQL ServerAlwaysOn 可用性群組Always On availability groups 執行個體的伺服器,所以 SQLBrowseConnect 會忽略 MultiSubnetFailover 連接字串關鍵字。Because SQLBrowseConnect does not recognize servers in a Windows Server Failover Clustering (WSFC) cluster that contains two or more SQL ServerSQL Server instances that have been enabled for AlwaysOn 可用性群組Always On availability groups, SQLBrowseConnect ignores the MultiSubnetFailover connection string keyword.
SQLConnectSQLConnect SQLConnect 可透過資料來源名稱 (DSN) 或連接屬性來支援 ApplicationIntentMultiSubnetFailoverSQLConnect supports both ApplicationIntent and MultiSubnetFailover via a data source name (DSN) or connection properties.
SQLDriverConnectSQLDriverConnect SQLDriverConnect 可透過連接字串關鍵字、連接屬性或 DSN 來支援 ApplicationIntentMultiSubnetFailoverSQLDriverConnect supports ApplicationIntent and MultiSubnetFailover via connection string keywords, connection properties, or DSN.

OLE DBOLE DB

SQL ServerSQL Server Native Client 中的 OLE DB 不支援 MultiSubnetFailover 關鍵字。OLE DB in the SQL ServerSQL Server Native Client does not support the MultiSubnetFailover keyword.

SQL ServerSQL Server Native Client 中的 OLE DB 將會支援應用程式意圖。OLE DB in the SQL ServerSQL Server Native Client will support application intent. OLE DB 應用程式與 ODBC 應用程式的應用程式意圖將會有相同的行為 (請參閱上面的內容)。Application intent will behave the same for OLE DB applications as ODBC applications (see above).

已加入一個 OLE DB 連接字串關鍵字來支援 AlwaysOn 可用性群組Always On availability groups Native Client 中的 SQL ServerSQL ServerOne OLE DB connection string keyword added to support AlwaysOn 可用性群組Always On availability groups in SQL ServerSQL Server Native Client:

  • Application IntentApplication Intent

如需 SQL ServerSQL Server Native Client 中連接字串關鍵字的詳細資訊,請參閱搭配 SQL Server Native Client 使用連接字串關鍵字For more information about connection string keywords in SQL ServerSQL Server Native Client, see Using Connection String Keywords with SQL Server Native Client.

同等的連接屬性如下:The equivalent connection properties are:

  • SSPROP_INIT_APPLICATIONINTENTSSPROP_INIT_APPLICATIONINTENT

  • DBPROP_INIT_PROVIDERSTRINGDBPROP_INIT_PROVIDERSTRING

SQL ServerSQL Server Native Client OLE DB 應用程式可以使用其中一個方法來指定應用程式意圖:A SQL ServerSQL Server Native Client OLE DB application can use one of the methods to specify application intent:

IDBInitialize::InitializeIDBInitialize::Initialize
IDBInitialize::Initialize 會使用之前設定的屬性集合來初始化資料來源及建立資料來源物件。IDBInitialize::Initialize uses the previously configured set of properties to initialize the data source and create the data source object. 將應用程式意圖指定為提供者屬性或是擴充屬性字串的一部分。Specify application intent as a provider property or as part of the extended properties string.

IDataInitialize::GetDataSourceIDataInitialize::GetDataSource
IDataInitialize::GetDataSource 會採用可包含 Application Intent 關鍵字的輸入連接字串。IDataInitialize::GetDataSource takes an input connection string that can contain the Application Intent keyword.

IDBProperties:: GetPropertiesIDBProperties::GetProperties
IDBProperties::GetProperties 會擷取目前在資料來源上設定的屬性值。IDBProperties::GetProperties retrieves the value of the property that is currently set on the data source. 您可以透過 DBPROP_INIT_PROVIDERSTRING 屬性和 SSPROP_INIT_APPLICATIONINTENT 屬性擷取 Application Intent 值。You can retrieve the Application Intent value through the DBPROP_INIT_PROVIDERSTRING property and SSPROP_INIT_APPLICATIONINTENT property.

IDBProperties::SetPropertiesIDBProperties::SetProperties
若要設定 ApplicationIntent 屬性值,請呼叫 IDBProperties::SetProperties,其傳入值為 "ReadWrite" 或 "ReadOnly" 的 SSPROP_INIT_APPLICATIONINTENT 屬性,或是值包含 "ApplicationIntent=ReadOnly" 或 "ApplicationIntent=ReadWrite" 的 DBPROP_INIT_PROVIDERSTRING 屬性。To set the ApplicationIntent property value, call IDBProperties::SetProperties passing in the SSPROP_INIT_APPLICATIONINTENT property with value "ReadWrite" or "ReadOnly" or DBPROP_INIT_PROVIDERSTRING property with value containing "ApplicationIntent=ReadOnly" or "ApplicationIntent=ReadWrite".

您可以在 [資料連結屬性] 對話方塊中,[全部] 索引標籤的 [應用程式的意圖屬性] 欄位內指定應用程式意圖。You can specify application intent in the Application Intent Properties field of the All tab in the Data Link Properties dialog box.

當建立隱含連接時,隱含連接將會使用父連接的應用程式意圖設定。When implicit connections are established, the implicit connection will use the application intent setting of the parent connection. 同樣地,從相同資料來源建立的多個工作階段將會繼承資料來源的應用程式意圖設定。Similarly, multiple sessions created from the same data source will inherit the data source's application intent setting.

另請參閱See Also

SQL Server Native Client 功能 SQL Server Native Client Features
搭配 SQL Server Native Client 使用連接字串關鍵字Using Connection String Keywords with SQL Server Native Client