連線到 Always On 可用性群組接聽程式Connect to an Always On availability group listener

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

設定可用性群組接聽程式之後,您必須更新連接字串,以連線到 Always On 可用性群組接聽程式。Once you've configured your availability group listener, you'll need to update your connection string to connect to the Always On availability group listener. 這會將來自應用程式的流量自動路由至預定複本,而不需要在每次容錯移轉之後手動更新連接字串。This will route traffic from your application automatically to the intended replica without having to manually update the connection string after every failover.

連線到主要複本Connect to the primary replica

在連接字串中指定可用性群組接聽程式的 DNS 名稱,以連線到主要複本進行讀寫存取。Specify the availability group listener DNS name in the connection string to connect to the primary replica for read-write access.

例如,若要在 SQL Server Management Studio 中透過接聽程式連線到主要複本,請在 [伺服器名稱] 欄位中輸入接聽程式 DNS 名稱:For example, to connect to the primary replica in SQL Server Management Studio through the listener, enter the listener DNS name in the server name field:

在 SSMS 中連線到接聽程式

在容錯移轉期間,當主要複本變更時,會中斷與接聽程式的現有連線,並將新連線路由至新的主要複本。During a failover, when the primary replica changes, existing connections to the listener are disconnected and new connections are routed to the new primary replica.

ADO.NET 提供者 (System.Data.SqlClient) 的基本連接字串範例如下:An example of a basic connection string for the ADO.NET provider (System.Data.SqlClient):

Server=tcp: AGListener,1433;Database=MyDB;Integrated Security=SSPI  

您可執行下列 Transact-SQL (T-SQL) 命令,以確認目前透過接聽程式連線到哪個複本:You can verify which replica you're currently connected to through the listener by running the following Transact-SQL (T-SQL) command:

SELECT @@SERVERNAME

例如,當 SQLVM1 是主要複本時:For example, when SQLVM1 is my primary replica:

檢查複本連線

您仍然可以使用主要或次要複本的執行個體名稱來直接連線到 SQL Server 的執行個體,而不是使用可用性群組接聽程式。You can still connect directly to the instance of SQL Server using the instance name of the primary or secondary replica instead of using the availability group listener. 不過,您將因此失去新連線會自動路由至目前新主要複本的優點。However, you will then lose the benefit of new connections being routed automatically to the new current primary replica. 此外,您將失去唯讀路由的優點,其中以 read-intent 指定的連線會自動路由至可讀取次要複本。Additionally, you will lose the benefit of read-only routing, where connections specified with read-intent are automatically routed to the readable secondary replica.

連線到唯讀複本Connect to a read-only replica

「唯讀路由」 是指將連入接聽程式連線自動路由至設定為允許唯讀工作負載的可讀取次要複本。Read-only routing refers to automatically routing incoming listener connections to a readable secondary replica that is configured to allow read-only workloads.

如果下列條件成立,則會將連線自動路由至唯讀複本:Connections are automatically routed to the read-only replica if the following are true:

  • 至少一個次要複本設定為唯讀存取,且每個唯讀次要複本和主要複本都設定為支援唯讀路由At least one secondary replica is set to read-only access, and each read-only secondary replica and the primary replica are configured to support read-only routing.

  • 連接字串會參考可用性群組中的相關資料庫。The connection string references a database involved in the Availability Group. 您有一個替代方案,就是將資料庫設定為連線所使用之登入的預設資料庫。An alternative to this would be the login used in the connection has the database configured as its default database. 如需詳細資訊,請參閱演算法如何使用唯讀路由一文。For more information, see this article on how the algorithm works with read-only routing.

  • 連接字串參考可用性群組接聽程式,而且內送連接的應用程式意圖設定為唯讀,例如,透過在 ODBC 或 OLEDB 連接字串或連接屬性 (attribute) 或屬性 (property) 中使用 Application Intent=ReadOnly 關鍵字。The connection string references an availability group listener, and the application intent of the incoming connection is set to read-only (for example, by using the Application Intent=ReadOnly keyword in the ODBC or OLEDB connection strings or connection attributes or properties).

應用程式意圖屬性在登入期間是儲存在用戶端的工作階段,然後 SQL Server 會處理此意圖,並依據可用性群組的設定和次要複本中目標資料庫的目前讀寫狀態,決定要執行的動作。The application intent attribute is stored in the client's session during login and the instance of SQL Server will then process this intent and determine what to do according to the configuration of the availability group and the current read-write state of the target database in the secondary replica.

例如,若要使用 SQL Server Management Studio 連線到唯讀複本,請選取 連線到伺服器 對話方塊上的 選項 ,選取 其他連線參數 索引標籤,然後在文字方塊中指定 ApplicationIntent=ReadOnlyFor example, to connect to a read-only replica using SQL Server Management Studio, select Options on the Connect to Server dialog box, select the Additional Connection Parameters tab, and then specify ApplicationIntent=ReadOnly in the text box:

SSMS 中的唯讀連線

指定唯讀應用程式意圖的 ADO.NET 提供者 (System.Data.SqlClient) 其連接字串範例如下:An example of a connection string for the ADO.NET provider (System.Data.SqlClient) that designates read-only application intent:

Server=tcp:AGListener;Database=AdventureWorks;Integrated Security=SSPI;ApplicationIntent=ReadOnly  

如需詳細資訊,請參閱設定可用性複本上的唯讀存取 (SQL Server)For more information, see Configure Read-Only Access on an Availability Replica (SQL Server)

無預設連接埠Non-default port

建立接聽程式時,您可指定接聽程式使用的連接埠。When creating your listener, you designate a port for the listener to use. 如果連接埠是預設連接埠 1433,則當連線到接聽程式時,就不需要指定連接埠號碼。If the port is the default port of 1433, then you do not have to specify a port number when connecting to your listener. 不過,如果連接埠不是 1433,則必須以 listenername,port 格式在連接字串中指定連接埠,例如:However, if the port is not 1433, then the port must be specified in the connection string in the format of listenername,port such as:

使用非預設連接埠進行連線

指定接聽程式使用非預設連接埠的 ADO.NET 提供者 (System.Data.SqlClient) 其連接字串範例如下:An example of a connection string for the ADO.NET provider (System.Data.SqlClient) that specifies a non-default port for the listener:

Server=tcp:AGListener,1445;Database=AdventureWorks;Integrated Security=SSPI 

略過接聽程式Bypass listeners

雖然可用性群組接聽程式支援容錯移轉重新導向和唯讀路由,但用戶端連接不一定要使用它們。While availability group listeners enable support for failover redirection and read-only routing, client connections are not required to use them. 用戶端連接也可以直接參考 SQL Server 執行個體,而不連接到可用性群組接聽程式。A client connection can also directly reference the instance of SQL Server instead of connecting to the availability group listener.

對 SQL Server 執行個體來說,連線使用可用性群組接聽程式或另一個執行個體端點來登入並沒有關係。To the instance of SQL Server, it is irrelevant whether a connection logs in using the availability group listener or using another instance endpoint. SQL Server 執行個體會確認目標資料庫的狀態,並依據可用性群組的組態和執行個體上資料庫的目前狀態,允許或不允許連接。The instance of SQL Server will verify the state of the targeted database and either allow or disallow connectivity based on the configuration of the availability group and the current state of the database on the instance. 例如,如果用戶端應用程式直接連線到 SQL Server 執行個體的通訊埠,並連線到裝載於可用性群組的目標資料庫,且目標資料庫處於主要狀態並已上線,則連線就會成功。For example, if a client application connects directly to an instance of SQL Server port and connects to a target database hosted in an availability group, and the target database is in primary state and online, then connectivity will succeed. 如果目標資料庫處於離線狀態或處於轉換狀態,資料庫連接就會失敗。If the target database is offline or in a transitional state, connectivity to the database will fail.

另外,從資料庫鏡像移轉至 Always On 可用性群組Always On availability groups時,只要僅一個次要複本存在且不允許使用者連接,應用程式就可以指定資料庫鏡像連接字串。Alternatively, while migrating from database mirroring to Always On 可用性群組Always On availability groups, applications can specify the database mirroring connection string as long as only one secondary replica exists and it disallows user connections.

資料庫鏡像連接字串Database mirroring connection strings

如果可用性群組只擁有一個次要複本,並且已針對次要複本設定 ALLOW_CONNECTIONS = READ_ONLY 或 ALLOW_CONNECTIONS = NONE,則用戶端可以使用資料庫鏡像連接字串連接至主要複本。If an availability group possesses only one secondary replica and is configured with either ALLOW_CONNECTIONS = READ_ONLY or ALLOW_CONNECTIONS = NONE for the secondary replica, clients can connect to the primary replica by using a database mirroring connection string. 從資料庫鏡像將現有的應用程式移轉到可用性群組時,這種方法會很實用,前提是您要將可用性群組限制為只能有兩個可用性複本 (一個主要複本和一個次要複本)。This approach can be useful while migrating an existing application from database mirroring to an availability group, as long as you limit the availability group to two availability replicas (a primary replica and one secondary replica). 如果您加入其他次要複本,您需要為可用性群組建立可用性群組接聽程式,並更新您的應用程式使用可用性群組接聽程式 DNS 名稱。If you add additional secondary replicas, you will need to create an availability group listener for the availability group and update your applications to use the availability group listener DNS name.

當使用資料庫鏡像連接字串時,用戶端可以使用 SQL ServerSQL Server Native Client 或 .NET Framework Data Provider for SQL ServerSQL ServerWhen using database mirroring connection strings, the client can use either SQL ServerSQL Server Native Client or .NET Framework Data Provider for SQL ServerSQL Server. 用戶端提供的連接字串至少必須提供一個伺服器執行個體名稱,也就是 「初始夥伴名稱」 ,以識別一開始裝載您打算連接之可用性複本的伺服器執行個體。The connection string provided by a client must minimally supply the name of one server instance, the initial partner name, to identify the server instance that initially hosts the availability replica to which you intend to connect. 此連接字串也可以選擇性地提供另一個伺服器執行個體的名稱,也就是 「容錯移轉夥伴名稱」 (Failover Partner Name),以識別一開始將次要複本裝載為容錯移轉夥伴名稱的伺服器執行個體。Optionally, the connection string can also supply the name of another server instance, the failover partner name, to identify the server instance that initially hosts the secondary replica as the failover partner name.

如需資料庫鏡像連接字串的詳細資訊,請參閱 將用戶端連接至資料庫鏡像工作階段 (SQL Server)For more information about database mirroring connection strings, see Connect Clients to a Database Mirroring Session (SQL Server).

多重子網路容錯移轉Multi-subnet failovers

若所用的用戶端程式庫可在連接字串中使用 MultiSubnetFailover 連線選項,即可根據所正在使用提供者的語法,將 MultiSubnetFailover 設定為 "True" 或 "Yes",以最佳化可用性群組容錯移轉至不同的子網路。If you're using client libraries that support the MultiSubnetFailover connection option in the connection string, you can optimize availability group failover to a different subnet by setting MultiSubnetFailover to "True" or "Yes", depending on the syntax of the provider you are using.

注意

對於可用性群組接聽程式以及 SQL Server 容錯移轉叢集執行名稱的單一和多重子網路連接,建議使用此設定。We recommend this setting for both single and multi-subnet connections to availability groups listeners and to SQL Server Failover Cluster Instance names. 啟用此選項會增加額外的最佳化,甚至在單一子網路案例也一樣。Enabling this option adds additional optimizations, even for single-subnet scenarios.

MultiSubnetFailover 連接選項只適用於 TCP 網路通訊協定,而且只在連接到可用性群組接聽程式時以及任何虛擬網路名稱連接到 SQL ServerSQL Server時才受支援。The MultiSubnetFailover connection option only works with the TCP network protocol and is only supported when connecting to an availability group listener and for any virtual network name connecting to SQL ServerSQL Server.

可啟用多重子網路容錯移轉的 ADO.NET 提供者 (System.Data.SqlClient) 連接字串範例如下:An example of the ADO.NET provider (System.Data.SqlClient) connection string that enables multi-subnet failover is as follows:

Server=tcp:AGListener,1433;Database=AdventureWorks;Integrated Security=SSPI; MultiSubnetFailover=True  

MultiSubnetFailover 連接選項應該設定為 True ,即使可用性群組只跨越單一子網路也一樣。The MultiSubnetFailover connection option should be set to True even if the availability group only spans a single subnet. 這可讓您將新用戶端預先設定為支援子網路的未來跨越,而不需要在未來變更用戶端連接字串,此外也會最佳化單一子網路容錯移轉的容錯移轉效能。This allows you to preconfigure new clients to support future spanning of subnets without any need for future client connection string changes and also optimizes failover performance for single subnet failovers. 雖然 MultiSubnetFailover 連接選項不是必要,但是它提供更快子網路容錯移轉的好處。While the MultiSubnetFailover connection option is not required, it does provide the benefit of a faster subnet failover. 這是因為用戶端驅動程式會嘗試對與可用性群組平行相關的每個 IP 位址開啟 TCP 通訊端。This is because the client driver will attempt to open up a TCP socket for each IP address in parallel associated with the availability group. 用戶端驅動程式會等候第一個成功回應的 IP,一旦回應,就會將它用於連接。The client driver will wait for the first IP to respond with success and once it does, will then use it for the connection.

接聽程式與 TLS/SSL 憑證Listeners & TLS/SSL certificates

連線到可用性群組接聽程式時,如果參與的 SQL Server 執行個體同時使用 TLS/SSL 憑證和工作階段加密,連線的用戶端驅動程式需要支援 TLS/SSL 憑證中的主體替代名稱,才能強制加密。When connecting to an availability group listener, if the participating instances of SQL Server use TLS/SSL certificates in conjunction with session encryption, the connecting client driver will need to support the Subject Alternate Name in the TLS/SSL certificate in order to force encryption. 我們已計劃針對 ADO.NET (SqlClient)、Microsoft JDBC 和 SQL Native Client (SNAC),提供憑證主體替代名稱的 SQL Server 驅動程式支援。SQL Server driver support for certificate Subject Alternative Name is planned for ADO.NET (SqlClient), Microsoft JDBC, and SQL Native Client (SNAC).

您必須針對容錯移轉叢集中的每個參與伺服器節點來設定 X.509 憑證,並在憑證的主體替代名稱中設定所有可用性群組接聽程式清單。An X.509 certificate must be configured for each participating server node in the failover cluster with a list of all availability group listeners set in the Subject Alternate Name of the certificate.

憑證值的格式為:The format for the certificate values is:

CN = Server.FQDN  
SAN = Server.FQDN,Listener1.FQDN,Listener2.FQDN

例如,您有下列值:For example, you have the following values:

Servername: Win2019   
Instance: SQL2019   
AG: AG2019   
Listener: Listener2019   
Domain: contoso.com  (which is also the FQDN)

針對有單一可用性群組的 WSFC,憑證應有伺服器的完整網域名稱 (FQDN),以及接聽程式的 FQDN:For a WSFC that has a single availability group, the certificate should have the fully qualified domain name (FQDN) of the server, and the FQDN of the listener:

CN: Win2019.contoso.com
SAN: Win2019.contoso.com, Listener2019.contoso.com 

若使用此設定,連線到執行個體 (WIN2019\SQL2019) 或接聽程式 (Listener2019) 時,系統將會加密您的連線。With this configuration, your connections will be encrypted when connecting to the instance (WIN2019\SQL2019), or the the listener (Listener2019).

取決於網路設定的方式而定,有一小部分的客戶可能也需要將 NetBIOS 新增至 SAN。Depending on how networking is configured, there is a small subset of customers that may need to add the NetBIOS to the SAN as well. 在此情況下,憑證值應該是:In which case, the certificate values should be:

CN: Win2019.contoso.com
SAN: Win2019,Win2019.contoso.com,Listener2019,Listener2019.contoso.com

如果 WSFC 有三個可用性群組接聽程式,例如:Listener1、Listener2、Listener3If the WSFC has three availability group listeners, such as: Listener1, Listener2, Listener3

則憑證值應該為:Then the certificate values should be:

CN: Win2019.contoso.com
SAN: Win2019.contoso.com,Listener1.contoso.com,Listener2.contoso.com,Listener3.contoso.com

接聽程式與 Kerberos (SPN)Listeners and Kerberos (SPNs)

網域系統管理員必須在 Active Directory 中設定每個可用性群組接聽程式的伺服器主體名稱 (SPN),以便針對接聽程式的用戶端連線啟用 Kerberos。A domain administrator must configure a Service Principal Name (SPN) in Active Directory for each availability group listener to enable Kerberos for client connections to the listener. 註冊 SPN 時,您必須使用裝載可用性複本的伺服器執行個體服務帳戶。When registering the SPN, you must use the service account of the server instance that hosts the availability replica. 若要讓 SPN 在所有複本上運作,相同服務帳戶必須用於裝載可用性群組之 WSFC 叢集的所有執行個體。For the SPN to work across all replicas, the same service account must be used for all instances in the WSFC cluster that hosts the availability group.

您可以使用 setspn Windows 命令列工具來設定 SPN。Use the setspn Windows command line tool to configure the SPN. 例如,若要設定 SPN 用於 AG1listener.Adventure-Works.com 可用性群組,而此可用性群組裝載於一組都設為使用 corp\svclogin2網域帳戶的 SQL Server 執行個體:For example to configure an SPN for an availability group named AG1listener.Adventure-Works.com hosted on a set of instances of SQL Server all configured to run under the domain account corp\svclogin2:

setspn -A MSSQLSvc/AG1listener.Adventure-Works.com:1433 corp\svclogin2  

如需有關為 SQL Server 手動註冊 SPN 的詳細資訊,請參閱< 註冊 Kerberos 連接的服務主體名稱>。For more information about manual registration of a SPN for SQL Server, see Register a Service Principal Name for Kerberos Connections.

後續步驟Next steps

當成功連線到接聽程式之後,請考慮將唯讀工作負載備份卸載至次要複本,以提升效能。Once you've successfully connected to the listener, consider offloading read-only workloads and backups to the secondary replica to improve performance. 您也可以檢閱各種可用性群組監視策略,以確保可用性群組的健康狀態。You can also review various availability group monitoring strategies to ensure the health of your availability group.

如需可用性群組的詳細資訊,請參閱 Always On 可用性群組概觀 (SQL Server)For more information about availability groups, see the Overview of Always On Availability Groups (SQL Server).