Alias for multiple instances on a SQL Server

Question

I have 2 SQL named instances on a SQL Server, trying to add aliases for both so that application teams can use the alias names in their connection strings.

There are 2 IP Addresses assigned for the server. 10.10.1.1, 10.10.1.2

I've assigned 10.10.1.1 to ServerName\Instance1 and 10.10.1.2 to ServerName\Instance2. Ports for the instances are 1433 and 1435 respectively

I have created 2 aliases from the SQL Server Configuration Manager: Test1, Test2

A DNS entry has also been created with IP Address specified for each alias.

Connections work only with Test1. I or the application team is unable to connect to Test2 instance.

How do I fix this?

Answer 1

It's a little tricky with multiple IP addresses and different ports when configuring SQL Server. The most important bit, is that the whole configuration has to be done with the SQL Server Configuration Manager.

1. Open up the SQL Server Configuration Manager with Runas Administrator...
2. Navigate through the SQL Server Network Configuration branch and you should find two sub-branches for ...
   a. Protocols for NAMED_INSTANCE_1
   b. Protocols for NAMED_INSTANCE_2

Repeat the following steps for each instance:

3. Click on the Protocols for NAMED_INSTANCE_n branch and in the right pane right-click on the TCP/IP Protocol Name entry.
4. Open up the properties.
5. In the Protocol register verify the following:
   a. Enabled = Yes
   b. Listen All = No
6. Switch to the IP Addresses register
7. You should find sections in the form of IPn for each of your IP addresses 10.10.1.1 and 10.10.1.2. This is the tricky bit: You are already in an instance and want to configure only one IP address for this instance.
8. In the IPn section corresponding to the IP address for this instance check the following:
   a. Active = Yes
   b. Enabled = Yes
   c. IP Address = 10.1.0.n
   d. TCP Dynamic Ports = leave empty
   e. TCP Port = 1433
9. Scroll down to the IPAll section
10. Verify the following:
    a. TCP Dynamic Ports = leave empty
    b. TCP Port = leave empty
11. In all other IPn sections ensure the following:
    a. Enabled = Yes
    b. Active = No (unless you want to communicate with the instance via the IP configuration settings in the section).

Repeat from step 3. for each instance.

You have to restart the instance and then it will only pick up on the IP address and port configured.

We have SQL Servers with up to 10 instances configured this way, but we leave the port at 1433 so that each instance will pick up on the default port. This allows for easier firewall configuration.

Good luck.

Answer 2

There are two sections to create your alias within SQL Server Configuration Manager: -

1. SQL Native Client 11.0 Configuration (32bit)
2. SQL Native Client 11.0 Configuration

Create the alias in both and test.