Running Multiple Instances of SQL Server

or “so you think you’ve got problems!”

We’re currently updating our server infrastructure at National Rural. As an interim measure I’ve got to run an instance of SQL-Server 2005 and SQL-Server 2008 on the same box. Both instances are consumed by web apps and the box has a firewall installed. I need to connect to both instances via SQL-Server management studio but ran into some pretty bizarre gotchas which I’m going to list here for future reference.

Summary

For those that need the quick fix, here’s how I got it running

1. Run each SQL-Server instance on its own TCP port.
2. Ensure the required ports are configured for TCP connections on the firewall.
3. Open port 1433 for UPD connections.
4. Ensure the firewall ports are configured to operate using the narrowest scope possible to fulfil your needs.

To configure a SQL Server instance to run on a specific port, open the SQL Server configuration manager, ideally on the local machine that the server instance is running on.

1. Select SQL Server Network Configuration.
2. Select Protocols for the named instance of the server you wish to configure.
3. Double click TCP/IP and select Enabled.
4. Set “Listen All” to yes in the Protocol tab.
5. On each IP Addresses listing, ensure all IP Entries have empty values for the TCP Dynamic Ports and TCP Port values
6. In the “IPAll” section, ensure TCP Dynamic ports is empty (NOT 0!) and the TCP Port value is set to the port number you wish the server instance to listen on.
7. Select “Apply” and restart the SQL Server instance in question.

Common Pitfalls and Gotchas

There are a number of connectivity issues you can run into if you’re SQL-Server version is express and developer edition as by default, they are configured out of the box to not accept Remote Server connections, and there are plenty of articles to get round this issue like this one here.

The issues listed below and error messages occur when the server is already configured to allow remote connection but you are still running into issues. If like me, you had a default instance of SQL server running, whatever version, it would have grabbed TCP port 1433 to listen on and all is well with the world. Allowing the firewall to allow TCP Connections for this port number is all you need to do to connect remotely using SQL-Server management studio. I had a 2005 instance running as the default instance on my dev box but then needed to install an instance of 2008 to run side-by-side with it. This instance I named SVR2008 (They don’t pay me for originality!). I could connect to it locally via SQL Server Management Studio and all was well with the world. The problems arose when trying to connect remotely…

Figure 1 above shows my two instances of SQL-Server on the same machine when connected to locally in SQL-Server management studio. Note that the default instance is version 9.0 – a SQL-Server 2005 instance.

When I try to connect remotely to this box now from another machine using SQL-Server management studio, I can see the default instance but not the named instance. This is because, initially, the named instance of SQL-Server will be configured to run on a dynamic port - basically when the SQL-Server instance starts, it grabs a dynamically allocated TCP port to listen on and this port will be different every time the server instance restarts. If that is the case, the firewall will block the connection and SQL-Server management Studio will display the following error.

Cannot connect to servername\instancename.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476

To solve this issue, configure the SQL-Server instance to use a specific TCP Port (see above). If you get the following error message when trying to start the second db instance:

Cannot connect to servername\instancename.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

..you may have not opened the firewall wall for the port the server is listening on. Change the TCP port of the named instance to a different port and restart the db instance. Ensure that that port is open for business on the firewall though and that port 1434 is configured for to accept UDP connections.

Like wise if you get this message:

Cannot connect to server\instance

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476

The only difference here is that you’ve not opened the listener port for the db instance but you’ve opened port 1434 for UDP connections. UDP port 1434 is used by when determining the named instance of the server you’re trying to connect to, which makes perfect sense as you don’t need it open when accessing the default instance (in figure 1) this would be W2003-DEV-2. However, SQL-Server seems to assume that any db instance that sits on port 1433 is the default instance. To prove this, swap the TCP ports used by the default and the named instances on the server and voila, on remotely connecting to the default instance of the database instance, you’ll actually be seeing the named instance. You have been warned!!!

Figure 2. Note the version number of the default instance. We’re actually looking at the same db instance in both connections. That’s because the SVR2008 instance is running on port 1433. In this scenario, we can’t actually connect to the actual default instance in this way.