On Friday I had an opportunity to setup a web application connecting a front-end IIS server to a backend SQL 2005 server using two separate networks. In addition to connecting via a dedicated secondary network I was also connecting to a second instance of SQL 2005 on the server. The first network is the 172 network that is used throughout the company. The second network (192) is a dedicated gigabyte network used only for the IIS and SQL servers to communicate SQL data. I hadn’t setup a solution like this before now but decided it would be beneficial for a project that we needed to run. So let me break down the steps to put this in place.
1. Both servers need to have two NICs. I designated NIC 1 as the 172 network and NIC 2 as the 192 network on each server. I set the SQL server NIC 2 to use 192.168.0.100 and the IIS NIC 2 to use 192.168.0.102.
2. Install the SQL 2005 Connection tools on the IIS server. You’ll need this to direct the client connection to the correct port and IP for the instance on the SQL server.
3. On the SQL server open the SQL Server Configuration Manager\ SQL Server 2005 Network Configuration\ Protocols for Instance Name (where instance name is the name of the SQL instance) and make sure that IP2 is Active, Enabled and has the correct IP and port. The IP should be the IP for NIC 2 of the SQL server. In this case I used port 1533 as 1433 was already being used by the first instance of SQL running on the server.
4. On the IIS server open the SQL Server Configuration Manager\ SQL Native Client Configuration\ Aliases. Create a new alias for the SQL server. This alias is what will be used in the connection string of the web application. In this case I had to configure not only the SQL server IP but also the instance name of the SQL server. Having this connection set up in the connection manager gives the web app a shortcut to the explicit directions to the server’s name, instance name, and the IP of the SQL server that is needed for the connection. In this case the SQL instance is at IP 192.168.0.100, port 1533 and instance name Stage. In the web application connection string, all that is needed to designate the server is SQL001Stage.
5. Make sure that the firewall is not enabled on NIC 2 for either server.
6. Configure the connection string to connect to the SQL server and recycle the app pool.
At this point the web application should be connecting to the SQL server and returning data.
Thanks to Jason for his assistance getting this set up.
Robba