|
|
 Cluster
DOS
IBM - AS400
MOM
Performance Counters
Service Broker
 SQL Server
Temporary Database
Views
Backup
SQL Mail
DTS - Data Transformation Services
Memory
Security
Excel
Jobs SQL Agent
MSDE
Install
DBCC
User Management
System Databases
BCV
Deadlocks
Temp Tables
 Find the physical se...
Enable xp_cmdshell
Intermittent Connect...
Connection String Pr...
Login Error ( State)...
Turn Default Tracing...
Re-index fails when ...
SMO AND DMO
Tracing of Errors
Extra carriage Retur...
Query to find Object...
Named Instance Port ...
Adding Domain Login ...
Rename Logical File ...
Error 3624 Delete st...
SQL DMO Error 21776
NetworkConnections
SQL Server Service m...
Trace Flags for Dead...
Change Server Name
Alternate to Count(*...
Windows OS
Will be added as a sub-category of, SQL Server
|
|
Named Instance Port resolution
|
|
How SQL Server Browser Works
When an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port, or "pipe," is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator using SQL Server Configuration Manager. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. By default, when enabled, both named instances and SQL Server Express are configured to use dynamic ports, that is, an available port is assigned when SQL Server starts. If you want, a specific port can be assigned to an instance of SQL Server. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime SQL Server is restarted, so the correct port number is unknown to the client.
Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server 2005 and SQL Server Browser support ipv6 and ipv4.
When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.
For information about starting and stopping the SQL Server Browser service, see "How to: Start and Stop the SQL Server Browser Service" in SQL Server Books Online.
Using SQL Server Browser
If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.
However, if the SQL Server Browser service is not running, the following connections do not work:
Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).
Any component that generates or passes server\instance information that could later be used by other components to reconnect.
Connecting to a named instance without providing the port number or pipe.
DAC to a named instance or the default instance if not using TCP/IP port 1434.
The OLAP redirector service.
Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.
If you are using SQL Server in a client-server scenario (for example, when your application is accessing SQL Server across a network), if you stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and write your client application code to always use that port number. This approach has the following problems:
You must update and maintain client application code to ensure it is connecting to the proper port.
The port you choose for each instance may be used by another service or application on the server, causing the instance of SQL Server to be unavailable.
Side-by-Side Installation with SQL Server 2000
In SQL Server 2000, the identification of the server connection endpoints is performed by the SQL Server service. SQL Server 2005 replaces that function with the SQL Server Browser service. If you install SQL Server on a computer that is also running SQL Server 2000 or MSDE, you must make sure that SQL Server 2000 or MSDE is upgraded to Service Pack 3 (SP3) or later. Versions earlier than SP3 do not properly share port 1434 and may not make your instances of SQL Server available to requesting client applications. Although you can change the services startup order so that the SQL Server Browser service starts before SQL Server 2000 or MSDE, we recommend that you update all earlier versions of SQL Server to the latest service pack.
When an instance of SQL Server 2000 is installed on the computer, if the SQL Server Browser is not running, the SQL Server 2000 listener service starts. If SQL Server Browser starts after the listener service, it waits 5 seconds for SQL Server 2000 to give up port 1434. If that does not occur, SQL Server Browser fails to start. To resolve this problem with versions of SQL Server 2000 earlier than Service Pack 3, stop SQL Server 2000, start SQL Server Browser, then restart SQL Server 2000. The SQL Server 2000 listener service continues to attempt to start on port 1434; therefore, the instance of SQL Server 2000 should be upgraded to Service Pack 3 as soon as possible.
SQL Server 7.0 has no similar capabilities and has no conflicts with SQL Server Browser.
|
|
|
|
|