How to create a server alias in SQL Server 2012

Question

We have a Windows Server 2012 server running with 2 instances of SQL Server 2012 installed on it - Live and System. Both instances are accepting connections on 1433 & Live is the default instance (1433 set in the ListenAll port).

We have a program that needs to connect to the System instance, however it does not support named instances of SQL Server.

From what I have read, we have 2 options -

1. install an extra network card, create a separate DNS record for it and have the System instance listen to all connections to 1433 on that card
2. Create a DNS record pointing to the server and use SQL Alias to point to System instance

We'd prefer to go with option 2 as it means we don't need to setup any more hardware, but I can't get it to work. The network team has set up an extra DNS A record ("programDB") to point to the server.

On the SQL Server I have created an alias using the SQL Server Configuration Manager:

• Alias Name="programDB" [I've also tried "programDB.domain.com.au"]
• Port No = 1433
• Protocol = TCP/IP
• Server = ServerName\System

I have set this up in both the 32 and 64 bit configuration

When I connect to programDB from SSMS, it connects me to the Live instance, not the System instance.

Have I miss understood how aliases work, or have I missed something in the setup?

Update: This is what I have set up:

• DNS A Record set up to point "programdb" to the IP address of the server
• SQL Aliases (x32 & x64):

SQL Alias

• TCP/IP Settings (port set to 1544):

TCPIPSettings

I've restarted the services a number of times. If I try to connect to "programdb,1544" then it connects to the System instance

• Greg: I did find some questions that were similar (eg. dba.stackexchange.com/questions/18451/…), however nobody seems to be reporting what I'm seeing
• Aaron Bertrand: Does the program support specifying a port? If not, what kind of program requires a default instance and port 1433?
• Greg: @AaronBertrand - not that I can see. It's not super helpful. Are aliases meant to allow you to handle this sort of thing? If I'm trying to do something impossible then we'll just install the 2nd NIC. It just feels like this should work.

Answer 1

Another option is to add additional IP addresses to the machine (does not require an additional network card to the best of my knowledge) each one with a different DNS entry. So now you can connect to that machine using multiple names. Then you set up the IP address in the TCPIP settings for the instance (using configuration manager). Each of those IP Address/Machine Names has a port 1433 and that port can be assigned to extra instance.

Once you are done you have MachineName1\Live and MachineName1\System instances. MachineName1\Live is also accessible by MachineLiveAlias and MachineName1\System accessible by MachineSystemAlias.

Here is a link with fairly detailed set of instructions http://kevine323.blogspot.com/2013/08/setting-up-aliases-on-sql-server-with.html

Answer 2

Set one of your instances to use a different port. The alias will allow the different port to be used without the client having to worry about it.

• Greg: So the client can connect to the default port of 1433, but the alias redirects it to port XXXX? We can't specify the port for this program and we don't really want to change the default port of the Live database - too much to change.
• Rob Farley: You don't tell it to connect to 1433, you simply don't specify the port. The alias tells it which port to use.
• Greg: ok, so I set server\System to listen on port 1544. If I connect to "server,1544" in SSMS it works, but if I connect to programdb it still connects to server\Live. What else do I need to setup?
• Rob Farley: And you've set programdb to talk to 1544 in Configuration Manager?
• Greenstone Walker: "Do I need to setup the alias on the client computer?" Yes, you do. Personally, though, I'd go for the "two IP addresses solution" that Kenneth suggests.