高可用性、嚴重損壞修復的 SqlClient 支援SqlClient Support for High Availability, Disaster Recovery

本主題討論 SqlClient 支援 (新增至 .NET Framework 4.5) 以提供高可用性、嚴重損壞修復--AlwaysOn 可用性群組。This topic discusses SqlClient support (added in .NET Framework 4.5) for high-availability, disaster recovery -- AlwaysOn Availability Groups. SQL Server 2012 新增了 AlwaysOn 可用性群組功能。AlwaysOn Availability Groups feature was added to SQL Server 2012. 如需 AlwaysOn 可用性群組的詳細資訊,請參閱《SQL Server 線上叢書》。For more information about AlwaysOn Availability Groups, see SQL Server Books Online.

現在,您可以在連線屬性中指定 (高可用性、災害復原) 可用性群組 (AG) 的可用性群組接聽程式或 SQL Server 2012 容錯移轉叢集執行個體。You can now specify the availability group listener of a (high-availability, disaster-recovery) availability group (AG) or SQL Server 2012 Failover Cluster Instance in the connection property. 如果 SqlClient 應用程式連線到容錯移轉的 AlwaysOn 資料庫,原始連線會中斷,應用程式必須開啟新的連線,才能在容錯移轉後繼續工作。If a SqlClient application is connected to an AlwaysOn database that fails over, the original connection is broken and the application must open a new connection to continue work after the failover.

如果您沒有連線到可用性群組接聽程式或 SQL Server 2012 容錯移轉叢集執行個體,或是有多個 IP 位址與主機名稱相關聯,則 SqlClient 會依序逐一查看所有與 DNS 項目相關聯的 IP 位址。If you are not connecting to an availability group listener or SQL Server 2012 Failover Cluster Instance, and if multiple IP addresses are associated with a hostname, SqlClient 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 Server 2012 容錯移轉叢集執行個體時,SqlClient 會嘗試平行建立與所有 IP 位址的連線,若其中一個連線嘗試成功,驅動程式會捨棄所有擱置中的連線嘗試。When connecting to an availability group listener or SQL Server 2012 Failover Cluster Instance, SqlClient 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 a failover, you should implement connection retry logic, retrying a failed connection until it reconnects.

在 .NET Framework 4.5 中,已將下列連接屬性新增至 SqlClient:The following connection properties were added to SqlClient in .NET Framework 4.5:

  • ApplicationIntent

  • MultiSubnetFailover

您可以透過下列方式,以程式設計方式修改這些連接字串關鍵字:You can programmatically modify these connection string keywords with:

  1. ApplicationIntent

  2. MultiSubnetFailover

注意

MultiSubnetFailover true .NET Framework 4.6.1 或更新版本不需要設定為。Setting MultiSubnetFailover to true isn't required with .NET Framework 4.6.1 or later versions.

使用 MultiSubnetFailover 進行連接Connecting With MultiSubnetFailover

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

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

如需 SqlClient 中連接字串關鍵字的詳細資訊,請參閱 ConnectionStringFor more information about connection string keywords in SqlClient, see ConnectionString.

若在連線非可用性群組接聽程式或 SQL Server 2012 容錯移轉叢集執行個體時指定 MultiSubnetFailover=True,可能會對效能產生負面影響,因此不受支援。Specifying MultiSubnetFailover=True when connecting to something other than a availability group listener or SQL Server 2012 Failover Cluster Instance may result in a negative performance impact, and is not supported.

請使用下列指導方針,連線到可用性群組或 SQL Server 2012 容錯移轉叢集執行個體中的伺服器:Use the following guidelines to connect to a server in an availability group or SQL Server 2012 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 Server 執行個體會導致連線失敗。Connecting to a SQL Server instance configured with more than 64 IP addresses will cause a connection failure.

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

  • 提高 Connect Timeout 的值來配合容錯移轉時間,並減少應用程式連線重試次數。Increase the value of Connect Timeout 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 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.

唯讀的次要複本不支援 SqlDependencySqlDependency is not supported on read-only secondary replicas.

如果設定主要複本拒絕唯讀工作負載,而且連接字串包含 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 連線關鍵字,或者如果使用了 MultiSubnetFailover=True 和 TCP 以外的通訊協定,則將會發生連線錯誤 (ArgumentException)。A connection error (ArgumentException) will occur if the MultiSubnetFailover and Failover Partner connection keywords are present in the connection string, or if MultiSubnetFailover=True and a protocol other than TCP is used. 如果使用 MultiSubnetFailover 而且 SQL Server 傳回容錯移轉夥伴回應,指出其是資料庫鏡像配對的一部分,也會發生錯誤 (SqlException)。An error (SqlException) will also occur if MultiSubnetFailover is used and the SQL Server returns a failover partner response indicating it is part of a database mirroring pair.

如果您將目前使用資料庫鏡像的 SqlClient 應用程式升級為多重子網路案例,應該移除 Failover Partner 連線屬性,並以設為 TrueMultiSubnetFailover 加以取代,然後以可用性群組接聽程式取代連接字串中的伺服器名稱。If you upgrade a SqlClient 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 True and replace the server name in the connection string with an availability group listener. 如果連接字串使用 Failover PartnerMultiSubnetFailover=True,驅動程式會發生錯誤。If a connection string uses Failover Partner and MultiSubnetFailover=True, the driver will generate an error. 不過,如果連接字串使用 Failover PartnerMultiSubnetFailover=False (或 ApplicationIntent=ReadWrite),應用程式就會使用資料庫鏡像。However, if a connection string uses Failover Partner and MultiSubnetFailover=False (or ApplicationIntent=ReadWrite), the application will use database mirroring.

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

指定應用程式意圖Specifying Application Intent

ApplicationIntent=ReadOnly 時,用戶端在連接到啟用 AlwaysOn 的資料庫時會要求讀取工作負載。When ApplicationIntent=ReadOnly, the client requests a read workload when connecting to an AlwaysOn enabled database. 伺服器會在連接時和在 USE 資料庫陳述式期間,只針對啟用 AlwaysOn 的資料庫強制執行此意圖。The server will enforce the intent at connection time and during a USE database statement but only to an Always On enabled database.

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

資料庫可以允許或不允許 AlwaysOn 目標資料庫上的讀取工作負載A database can allow or disallow read workloads on the targeted AlwaysOn database. (這會透過 PRIMARY_ROLESECONDARY_ROLE Transact-SQL 陳述式的 ALLOW_CONNECTIONS 子句來完成)。(This is done with the ALLOW_CONNECTIONS clause of the PRIMARY_ROLE and SECONDARY_ROLETransact-SQL statements.)

ApplicationIntent 關鍵字用於啟用唯讀路由。The ApplicationIntent keyword is used to enable 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:

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

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

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

使用唯讀路由的多個連接可能不會連接至相同的唯讀複本。It is possible that multiple connections using read-only routing will 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. 若要確保所有唯讀要求連接至相同的唯讀複本,請勿將可用性群組接聽程式傳遞給 Data Source 連接字串關鍵字。To ensure that all read-only requests connect to the same read-only replica, do not pass an availability group listener to the Data Source connection string keyword. 請改為指定唯讀執行個體的名稱。Instead, specify the name of the read-only instance.

唯讀路由可能比連接到主要複本的時間更長,因為唯讀路由先連接到主要複本,再尋找最佳的可讀取次要複本。Read-only routing may take longer than connecting to the primary because read only routing first connects to the primary and then looks for the best available readable secondary. 因此,您應該增加登入逾時。Because of this, you should increase your login timeout.

另請參閱See also