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?
Best Answer
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.
Runas Administrator...
a. Protocols for NAMED_INSTANCE_1
b. Protocols for NAMED_INSTANCE_2
Repeat the following steps for each instance:
a. Enabled = Yes
b. Listen All = No
a. Active = Yes
b. Enabled = Yes
c. IP Address = 10.1.0.n
d. TCP Dynamic Ports = leave empty
e. TCP Port = 1433
a. TCP Dynamic Ports = leave empty
b. TCP Port = leave empty
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.