設定 Database Engine 接聽多個 TCP 通訊埠Configure the Database Engine to Listen on Multiple TCP Ports

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

此主題描述如何使用 SQL Server 組態管理員,在 Database EngineDatabase Engine 中設定 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 於多個 TCP 通訊埠上接聽。This topic describes how to configure the Database EngineDatabase Engine to listen on multiple TCP ports in SQL Server 2019 (15.x)SQL Server 2019 (15.x) by using SQL Server Configuration Manager. SQL ServerSQL Server啟用 TCP/IP 時, Database EngineDatabase Engine 將會在由 IP 位址及 TCP 通訊埠編號構成的連接點上接聽內送連接。下列程序會建立表格式資料流 (TDS) 端點,因此 SQL ServerSQL Server 將可以在其他 TCP 通訊埠上接聽。When TCP/IP is enabled for SQL ServerSQL Server, the Database EngineDatabase Engine will listen for incoming connections on a connection point consisting of an IP address and TCP port number.The following procedures create a tabular data stream (TDS) endpoint, so that SQL ServerSQL Server will listen on an additional TCP port.

必須建立第二個 TDS 端點的原因包括:Possible reasons to create a second TDS endpoint include:

  • 設定防火牆以對特定子網路的本機用戶端電腦,限制存取預設端點。Increase security by configuring the firewall to restrict access to the default endpoint to local client computers on a specific subnet. 建立該防火牆對網際網路公開的新端點,以及對伺服器支援團隊限制連接此端點的權限,為您的支援團隊維護 SQL ServerSQL Server 的網際網路存取。Maintain Internet access to SQL ServerSQL Server for your support team by creating a new endpoint that the firewall exposes to the Internet, and restricting connection rights to this endpoint to your server support team.

  • 在使用非統一記憶體存取 (NUMA) 時,相似化特定處理器的連接。Affinitizing connections to specific processors when using Non-Uniform Memory Access (NUMA).

設定 TDS 端點包含下列步驟,您能夠以任意順序來完成:Configuring a TDS endpoint consists of the following steps, which can be done in any order:

  • 建立 TCP 通訊端的 TDS 端點,然後還原預設端點的存取權 (如有必要)。Create the TDS endpoint for the TCP port, and restore access to the default endpoint if appropriate.

  • 對想要的伺服器主體授與端點存取權。Grant access to the endpoint to the desired server principals.

  • 指定選取 IP 位址的 TCP 通訊埠編號。Specify the TCP port number for the selected IP address.

如需預設 Windows 防火牆設定的詳細資訊以及影響 Database Engine、Analysis Services、Reporting Services 和 Integration Services 之 TCP 通訊埠的描述,請參閱 設定 Windows 防火牆以允許 SQL Server 存取For more information about the default Windows firewall settings, and a description of the TCP ports that affect the Database Engine, Analysis Services, Reporting Services, and Integration Services, see Configure the Windows Firewall to Allow SQL Server Access.

建立 TDS 端點To create a TDS endpoint

  • 發出以下陳述式以為伺服器上所有可用的 TCP 位址,為通訊埠 1500 建立一個稱為 CustomConnection 的端點。Issue the following statement to create an endpoint named CustomConnection for port 1500 for all available TCP addresses on the server.

    USE master;  
    GO  
    CREATE ENDPOINT [CustomConnection]  
    STATE = STARTED  
    AS TCP  
       (LISTENER_PORT = 1500, LISTENER_IP =ALL)  
    FOR TSQL() ;  
    GO  
    

當您建立新的 Transact-SQLTransact-SQL 端點時,就會撤銷預設 TDS 端點的 public 連接權限。When you create a new Transact-SQLTransact-SQL endpoint, connect permissions for public are revoked for the default TDS endpoint. 如果預設端點需要存取 public 群組,請使用 GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]; 陳述式重新套用此權限。If access to the public group is needed for the default endpoint, reapply this permission by using the GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]; statement.

授與端點的存取權To grant access to the endpoint

  • 發出以下陳述式以對公司網域的 SQLSupport,授與 CustomConnection 端點的存取權。Issue the following statement to grant access to the CustomConnection endpoint to the SQLSupport group in the corp domain.

    GRANT CONNECT ON ENDPOINT::[CustomConnection] to [corp\SQLSupport] ;  
    GO  
    

設定 SQL Server Database Engine 接聽其他 TCP 通訊埠。To configure the SQL Server Database Engine to listen on an additional TCP port

  1. 在 SQL Server 組態管理員中,展開 [SQL Server 網路組態],然後按一下 [<執行個體名稱> 的通訊協定]。In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click Protocols for<instance_name>.

  2. 展開 [<執行個體名稱> 的通訊協定],然後按一下 [TCP/IP]。Expand Protocols for<instance_name>, and then click TCP/IP.

  3. 在右窗格中,以滑鼠右鍵按一下您要啟用之已停用的 IP 位址,然後按一下 [啟用]。In the right pane, right-click each disabled IP address that you want to enable, and then click Enable.

  4. 以滑鼠右鍵按一下 [IPAll],然後按一下 [內容]。Right-click IPAll, and then click Properties.

  5. [TCP 通訊埠] 方塊中,輸入想要 Database EngineDatabase Engine 接聽的通訊埠,並以逗號分隔。In the TCP Port box, type the ports that you want the Database EngineDatabase Engine to listen on, separated by commas. 在此範例中,如果列出預設通訊埠 1433,請輸入 ,1500 ,如此該方塊會讀為 1433,1500,然後按一下 [確定] 。In our example, if the default port 1433 is listed, type ,1500 so the box reads 1433,1500, and then click OK.

    注意

    如果未啟用所有 IP 位址上的通訊埠,請只有在想要的位址,以內容方塊設定其他通訊埠。If you are not enabling the port on all IP addresses, configure the additional port in the property box for only for the desired address. 接著在主控台窗格中,以滑鼠右鍵按一下 [TCP/IP],按一下 [內容],然後在 [全部接聽] 方塊中選取 [否]。Then, in the console pane, right-click TCP/IP, click Properties, and in the Listen All box, select No.

  6. 在左窗格中,按一下 [SQL Server 服務]In the left pane, click SQL Server Services.

  7. 在右窗格中,以滑鼠右鍵按一下 [SQL Server <執行個體名稱>],然後按一下 [重新啟動]。In the right pane, right-click SQL Server<instance_name>, and then click Restart.

    Database EngineDatabase Engine 重新啟動時,錯誤記錄檔將會列出 SQL ServerSQL Server 正在接聽的通訊埠。When the Database EngineDatabase Engine restarts, the Error log will list the ports on which SQL ServerSQL Server is listening.

連接新的端點To connect to the new endpoint

  • 使用信任連接,並假設使用者是 [corp\SQLSupport] 群組的成員,在稱為 ACCT 的伺服器上發出以下陳述式,連接 SQL Server 之預設執行個體的 CustomConnection 端點。Issue the following statement to connect to the CustomConnection endpoint of the default instance of SQL Server on the server named ACCT, using a trusted connection, and assuming the user is a member of the [corp\SQLSupport] group.

    sqlcmd -SACCT,1500  
    

另請參閱See Also

CREATE ENDPOINT (Transact-SQL) CREATE ENDPOINT (Transact-SQL)
DROP ENDPOINT (Transact-SQL) DROP ENDPOINT (Transact-SQL)
GRANT 端點權限 (Transact-SQL) GRANT Endpoint Permissions (Transact-SQL)
將 TCP/IP 通訊埠對應到 NUMA 節點 (SQL Server)Map TCP IP Ports to NUMA Nodes (SQL Server)