In our development environment, we have numerous named instances of SQL Server 2008 running. I have recently added a new one it doesn't work as expected. Here is our set up:
- DEFAULT on 10.2.2.48:1433
- SQL1 on 10.2.2.40:1434
- SQL2 on 10.2.2.41:1435
- SQL3 on 10.2.2.42:1436
When the TCP/IP Properties of SQL3 are set to "Listen All: Yes", it stars correctly but can't be accessed using it's alias name of "car-sql3". When I set it to "Listen All: No", the service fails to start giving the following error in the System Event Log:
The SQL Server (SQL3) service terminated with service-specific error. You were not connected because a duplicate name exists on the network. If joining a domain, go to System in Control Panel to change the computer name and try again. If joining a workgroup, choose another workgroup name.
When setting the values under "IP Addresses" (in the Configuration Manager), does the "IP1, IP2, IPnn" designation mean anything? Can I set IP1 for one instance to one particular IP:Port and IP1 on another instance to a different one, or do I need to be doing something different here.
Best Answer
Port 1434 is used by the SQL browser (similar to the Oracle listener in functionality) and is used to direct client connections to the correct port for the instance specified. If you are trying to connect to the server with SERVERNAME\SQL2 and the browser is not active it will probably time out. Optionally you can explicitly specify the port when you connect to the instance using the format SERVERNAME, 1435 and that does not require the SQL browser. For each instance in SQL Server Configuration Manager you should only have to specify the port number in the IPALL section at the bottom of the window.