Thursday, July 23, 2009

Multiple SQL Instances on Different IPs

The first thing I thought of to get additional SQL installations on the same server was to use different port numbers.
That would have been far too easy. Our application doesn't support that anyway.

Right, so the alternative approach is to leave the port the same (1433) and use a second IP address.

1) First of all, add your additional IP addresses on the host machine >


2) Configure SQL Server to use the new IP >


SQL Server Configuration Manager is the tool to use.
By using the 2008 version I can see the services & protocols for both the 2005 & 2008 instances on my machine.

By default SQL is listening on all ports and dynamically determining which to use.
We need to stop this behavior i.e restrict a SQL installation to a single port on a single port so that instances can co-exist.

Under each instance open the properties of TCP/IP
  1. Set "Listen All" = No.
  2. Set the desired IP and port
  3. Disable listening on IPs other than your chosen main one.
  4. Disable dynamic ports by removing 'o' from TCP Dynamic ports for all IP's (except for 127.0.0.1 and "IPAll")
  5. Restart SQL services.

No comments: