Cause of access error when installing multiple versions of SQL

1. cause:

The default SQL connection port is 1433. When the port is occupied after the earlier version is installed, another port number is automatically specified when the later version is used, in this case, the client cannot connect to the correct database instance by using the default port number.

2. solution:

If a physical server has multiple SQL Server instances, SQL Server 2005 provides a new browser service to ensure that the client can access the correct instances. In this case, you need to start the SQL Server Browser Service to access the server normally.

3. Service Introduction:

SQL Server BrowserProgramRun as a Windows service. The SQL Server Browser listens for incoming requests to microsoftsql server resources and provides information about the SQL server instance installed on the computer. The SQL Server Browser can be used to perform the following operations:

View the list of available servers

Connect to the correct server instance

Connect to the dedicated administrator connection (DAC) Endpoint

The SQL Server Browser Service (sqlbrowser) provides the Instance name and version number for each instance of the database engine and SSAs. The SQL Server Browser is installed with SQL Server to provide this service for earlier versions of SQL server running on the computer (starting from SQL Server 7.0.

The SQL Server Browser can be configured during installation or by using the SQL Server Configuration Manager. By default, the SQL Server Browser Service is automatically started:

During upgrade and installation.

In parallel with SQL Server 2000 instances.

During cluster installation.

When you install an SQL Server database engine named instance that includes all SQL Server express instances.

When you install Analysis Services named instances.

4. Working principle:

After an SQL server instance is started, if TCP/IP or via is enabled for SQL server, the server will be allocated a TCP/IP Port. If the Named Pipes protocol is enabled, SQL server listens to a specific named pipeline. This particular instance will use this port (or "Pipeline") to exchange data with the client application. During the installation process, TCP port 1433 and MPs queue \ SQL \ QUERY are allocated to the default instance, but the server administrator can then use the SQL Server Configuration Manager to make changes. Since only one SQL server instance can use a port or MPs queue, different port numbers and MPs queue names are allocated to the named instance, including SQL Server Express. By default, the named instance and SQL Server express are configured to use dynamic ports when enabled, that is, the available ports are allocated when SQL Server is started. If necessary, you can assign specific ports to the SQL server instance. During connection, the client can specify a specific port. However, if the port is dynamically allocated, the port number may be changed when SQL Server is restarted. Therefore, the correct port number is unknown to the client.

After it is started, the SQL Server Browser starts and uses UDP port 1434. The SQL Server Browser reads the registry, recognizes all SQL Server instances on the computer, and specifies the ports and named pipes they use. When a server has two or more NICs, the SQL Server Browser returns the first enabled port for SQL Server. The SQL Server Browser supports IPv6 and IPv4.

When the SQL Server Client Requests SQL Server resources, the client network library uses port 1434 to send a UDP message to the server. The SQL Server Browser responds with the requested instance's TCP/IP Port or named pipe. Then, the network library in the client application will use the port or named pipe of the desired instance to send a request to the server to complete the connection.

After installing Multiple SQL instances, If You Want To correctly access the correct instance, you must start the SQL Server Browser Service corresponding to each instance, if three instances of different SQL versions are installed and only two SQL Server Browser services are started, database access will still fail.