Wednesday, January 21, 2009

Multiple SQL Server Instances working magically

Configuring an instance of SQL 2000 to run on an non-standard port or having SQL dynamically assign a port to listen on and not configuring the client to point to that port somehow works and the client connects to the proper port. The standard SQL port (1433) was not listening. So how did the SQL client know what port to connect to?
When an instance of SQL Server uses dynamic port allocation, the connection string that is built at the SQL Server client does not specify the destination TCP/IP port unless the user or the programmer explicitly specifies the port. Therefore, the SQL Server client library queries the server computer on UDP port 1434 to collect the information about the destination instance of SQL Server. When SQL Server returns the information, the SQL Server client library sends the data to the appropriate instance of SQL Server.
But the SQL client also querries 1434 if your client is configured to connect to 1433. If you have 1433 UDP blocked, then it works as advertized. Even if you have manually specified the port your instance should listen on.
If UDP port 1434 is disabled, the SQL Server client cannot dynamically determine the port of the named instance of SQL Server. Therefore, the SQL Server client may not be able to connect to the named instance of SQL Server. In this situation, the SQL Server client must specify the dynamically allocated port where the named instance of SQL Server 2000 is listening.
For more information about how to configure an instance of SQL Server to listen on a specific TCP port or dynamic port, see this Microsoft article

No comments: