Sql-server – How to create a server alias in SQL Server 2012

instancesql-server-2012

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

Best Answer

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

Related Question