You are currently offline, waiting for your internet to reconnect

How to set up SQL Server to listen on multiple static TCP ports

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Summary
This article describes how to enable a SQL Server default instance to listen on multiple TCP ports.
More information
For each server-side network library, SQL Server sets up a separate "listen on" thread. If the server becomes overwhelmed with connection requests, the client connections are reset and are forced to reestablish a new session. To increase the performance of the SQL Server server to simultaneously process more connection requests, additional TCP ports to listen on can be added.To add additional TCP ports, follow these steps:
  1. Start the SQL Server Network Utility in SQL Server 2000. Select TCP, click Properties, and then enter the additional port separated by a comma; for example: 1433,5000.
  2. Stop and then restart the SQL Server server, and review the errorlog. Your errorlog should resemble the following:
    SQL server listening on TCP, Shared Memory, Named Pipes.<BR/>SQL server listening on 157.54.178.42:1433, 157.54.178.42:5000, 127.0.0.1:1433, 127.0.0.1:5000.					
  3. Use the SQL Server Client Network Utility to modify your clients to spread the load accross the TCP ports. For example, two Web servers, both connecting to the same SQL Server on port 1433, can now split the load across 1433 and 5000. This allows the administrator to separate the traffic across multiple ports and increases the connection throughput.
Note To perform the same operation in SQL Server 2005, use the SQL Server Configuration Manager tool.
Properties

Article ID: 294453 - Last Review: 06/22/2014 11:06:00 - Revision: 5.0

Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Enterprise Edition

  • kbinfo KB294453
Feedback