Thursday, August 1, 2013

Setting up aliases on a SQL Server with multiple instances

This article will explain how to set up multiple aliases for a SQL Server running multiple instances. Aliases can be an important part of a company's disaster recovery plan as well as aiding in the replacement of an underlying physical or virtual server. By abstracting the name a user or application uses to connect to SQL Server, we gain the ability change the underlying hardware with a few changes to DNS.

At the end of this article, there will be 3 instances of SQL Server running that can accessed either by their SQL instance names or the 3 aliases we are going to create, SQLAlias1, SQLAlias2, SQLAlias3.

Our initial setup is a physical server named WinServer and 3 instances of SQL Server 2008. The first instance is the default instance, with the other two instances named Instance2 and Instance3. We can connect to all 3 instances by using the names WinServer, WinServer\Instance2, and WinServer\Instance3.

Once all 3 instances are setup, we need to add 2 more IP addresses to the WinServer. There is one IP address already assigned to WinServer, 192.168.1.1. By adding two new IP's, 192.168.1.2 and 192.168.1.3, WinServer can be accessed by any of the 3 IP's on the network. Below are screenshots on how to add the 2 new IP's. You may need to coordinate with your network administrator.

Add the IP's in the Local Area Connection Properties of the NIC.

Advanced

Add the 2 addresses

Uncheck Register this connection's addresses in DNS

Now that the IP's have been created, we need to move into DNS to configure our host names and aliases. Again, you may need to coordinate with your network administrator.

  1. WinServer (the physical machine) must be setup as a static IP in DNS. In this instance, 192.168.1.1.
  2. SQLAlias1, which will point to the default instance on WinServer, will be setup as a DNS alias or CNAME. Basically, it is a pointer to WinServer.
  3. SQLAlias2, which will point to WinServer\Instance2, will be setup as a new Host (A) record in DNS with an address of 192.168.1.2.
  4. SQLAlias3, which will point to WinServer\Instance3, will be setup as a new Host (A) record in DNS with an address of 192.168.1.3.
Once these are setup, you may have to flush your DNS cache and re-register. You can do this by running the following commands in the command prompt. 
  1. ipconfig /flushdns
  2. ipconfig /registerdns
You should now be able to ping all 3 aliases and each should resolve to their associated IP address:
  • SQLAlias1: 192.168.1.1
  • SQLAlias2: 192.168.1.2
  • SQLAlias3: 192.168.1.3
Now that everything has been completed on the DNS side, open up SQL Server Configuration Manager on WinServer. There, under SQL Server Network Configuration, you will see the protocols for the 3 instances of SQL Server.


Configure the protocols for each instance one at a time. First, the protocols for the default (MSSQLServer) instance.

Double click the TCP/IP protocol set Listen All to No.

Now, move to the IP Addresses tab and enter the 3 IP Addresses (192.168.1.1, 192.168.1.2, 192.168.1.3) all with the port of your choosing. I chose to keep the default port of 1433 for all three instances. Notice that on the first instance, the Active and Enabled is set to Yes on the IP address of 192.168.1.1 while the other two addresses have Active set to Yes, but Enabled is set to No. Dynamic Ports on the first instance is set to blank, not 0. 


Select OK. You will get a warning box that these changes will not take affect until the instance is restarted, but I chose to wait until I'd configured all 3 instances before restarting the 3 SQL services.

Now that the TCP/IP protocol is configured on the default instance, we will configure SQLInstance2 and SQLInstance3 in the same way, with a few subtle differences.

On the TCP/IP protocol setup of SQLInstance2, we will once again set the Listen All to No.

Inside the IP Addresses tab, enter your 3 IP Addresses just like on the first instance. The difference here is that all 3 will again be set to Active, but only 192.168.1.1 and 192.168.1.2 will be set to Enabled, with TCP Dynamic Ports set to 0 on the 192.168.1.1 address. This is done so we can still access the WinServer\Instance2 instance by that name and not just the SQLAlias2 alias.

For SQLIntance3, the TCP/IP protocol setup is basically the same as SQLInstance2, execpt for 1 difference. Listen All will still be set to No and the 192.168.1.1 address will still be set to Yes for Active and Enabled, along with TCP Dynamic Ports set to 0. The different is that on the 192.168.1.2 address, it will be set to No for Enabled, while Enabled is set to Yes for the 192.138.1.3 address.


Note: On all 3 instances, under the IPAll section of the IP Addresses, TCP Dynamic Ports and TCP Port will always be blank.

If you restart all 3 instances now, you will be able to connect to the default instance but will most likely get the following error message when connecting to SQLInstance2 and SQLInstance3. 

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)
If that’s the case you will need to add a new DWORD registry entry under HKEY_LOCAL_MACHINE\SYSTSM\CurrentControlSet\Control\Lsa called DisableLoopbackCheck and set to 1.

Once that is set you should immediately be able to connect to all the instances on you server using either the SQL instance name or the new aliases setup in DNS.



11 comments:

  1. Very good and detailed article!
    Have you tried using non-default ports on all these instances? How would the corresponding DNS records look (say for instance you want WinServer\Instance2 to listen on port 29355 and WinServer\Instance3 to listen on 39576 - bascally random ports).
    Thank you for your post.

    ReplyDelete
    Replies
    1. Hi Ashok --
      The DNS records should look the same as if the SQL instances were listening on port 1433. You cannot specify ports inside DNS. If you've setup the TCP ports for the instance correctly to listen on 29355 or 39576 and added the correct number of IP's to your server, then you will reference the instances with a DNS alias to those IP's, which are mapped to instance.

      Delete
  2. Hi Kevin, thanks for the information you provided in this article - it's very helpful!
    Are you aware if the steps to accomplish something like this has changed much between SQL Server 2008 and SQL Server 2012 or even the new 2014?
    Also, once these alias are setup, how much work do you think it would be to migrate them to the new version of SQL? I'm just curious about maintanability.
    Thanks,
    Moneka

    ReplyDelete
    Replies
    1. Hi Kevin,

      I followed the setup mentioned above and it works perfect for SQL2008 & SQL2008R2 but it fail for SQL2012, do you have any idea? It will prompt below error.

      TITLE: New Server Registration
      ------------------------------

      Testing the registered server failed. Verify the server name, login credentials, and database, and then click Test again.

      ------------------------------
      ADDITIONAL INFORMATION:

      Instance failure. (System.Data)

      ------------------------------
      BUTTONS:

      OK
      ------------------------------

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hi Kevin,

    SQL Alias Name are perfectly working. I can able to connect to correct instances either by using SQL Alias Names or Static Ips. However, I am facing little issue while adding a member to AVG group. The remote DR SQL Server (Stand Alone Installations) has 3 SQL Instances (1 default, 2 named) listening on 1433 ports with SQL Aliases & Static Ip's set up correctly. I need to add these 3 SQL instances to 3 separate AVG groups. Though I am generating the scripts for adding remote DR node to AVG group, ALTER AVAILABILITY GROUP statement is still taking SQL Instance Name (Windows Server\Instance) instead of using SQL Alias Name. Hence, even though the member able to join, it is not coming online to add remote databases to AVG group. Do we have any work around to solve this issue?

    :Connect avg-primary-server

    ALTER AVAILABILITY GROUP [AVG-NAME]
    ADD REPLICA ON N'server-name\instance-name' WITH (ENDPOINT_URL = N'TCP://server-name.domain.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

    GO

    :Connect Remote-DR-Standby

    ALTER AVAILABILITY GROUP [Avg-Name] JOIN;

    GO

    ReplyDelete
  5. Do you have similar steps for Fail over SQL cluster Instances ? In this case SQL server Instance can reside on either of cluster nodes

    ReplyDelete
  6. What query I use to find the alias for the server name. something equivalent to @@SERVERNAME

    ReplyDelete
  7. Beautiful post.
    3 years later and it's still relevant.
    We had some issues with the implementations of aliases and your post helped to clear it out.
    Thanks !

    ReplyDelete
  8. Hi Kevin - one question, why do you Uncheck Register this connection's addresses in DNS?

    We tested aliases creation with your tutorial, and the only thing that didn't work for us is the first alias that points to the default instance.
    In addition, suddenly the DNS entry of the WinServer(the physical machine) vanished from the DNS admin tool.

    Once we checked back that option gister this connection's addresses in DNS, all the 3 aliases worked, along with their original instance names.


    Thanks,
    ROni.

    ReplyDelete
  9. Thanks for providing this informative information you may also refer.
    http://www.s4techno.com/blog/2016/07/27/introduction-to-sql/

    ReplyDelete