Creating And Configuring An Alias In SQL Server

An alias in SQL Server is simply an alternative name given to the server that is easier for users to remember.  Instead of having a complicated name like ABC-SQL2016-PROD01 you could simplify it to something like PROD1.  Though this may not seem like the most necessary thing to do in a small scale environment, in larger organizations, with dozens of servers, this can be a very useful approach.

In addition to simplifying the name of the server, aliasing also provides a benefit if/when you ever rename or move the server.  Once an alias has been created and all relevant system objects have been updated to reference the alias, renaming or moving the server becomes a much less tedious process as only the alias need to be updated to reference the new server name.  This can save literally hundreds of hours of work in large scale environments, especially when it comes to updating cross server queries in system objects such as stored procedures.

In order to configure an alias you’ll first want to connect to the server hosting your SQL Server instance and open SQL Server Configuration Manager.

Under the SQL Native Client Configuration drop-down right-click Aliases and select New Alias.

The New Alias window will be displayed.  Under Alias Name you will provide the alias you want to use for the server.  In many cases you will want to use 1433 as the port, as this is the default SQL Server port.  However, there may be instances where this is not the case as some organizations may use a custom port.  If you’re reading this and you use custom ports for SQL Server then you’re probably already aware of this.

By default the protocol will be set to TCP/IP. Again, in most cases this is the protocol you will likely want to use.  There are some applications that may require the use of named pipes over TCP/IP so if you’re configuring an alias for a very specific application, Named Pipes may be the option you want to choose.  If you do go down this path, you’ll need to provide the Pipe Name in place of the Port Number.

Finally you’ll want to provide the full server name to which the alias will correspond.  Basically, whatever server you connected to in order to launch SQL Server Configuration Manager is the server that you will specify here.  A fully configured alias is shown in the screenshot below.

Once all fields have been configured click OK.  You should now see the newly created alias in SQL Server Configuration Manager.

You should now be able to connect to the instance of SQL Server using the newly created alias.  Launch SQL Server Management Studio and enter the alias name as the server and try to connect.

The steps outlined above will only allow you to reference the alias when you are launching Management Studio directly from the server. If you are connecting through your local machine the alias will not be recognized.

In order to have the alias recognized globally an additional step of adding it to the Domain Name Server (DNS) is required.  In order to do this you will need to connect to your domain controller and launch the DNS Manager.  This can be found under All Programs>>Administrative Tools>>DNS.

Depending on your organization you may have multiple zones within the DNS.  You will need to determine which zone to add the alias, though if you are familiar enough with your organization’s domain it should be pretty obvious.  In order to add the alias to the DNS simply right-click the appropriate zone and select New Alias (CNAME).

The screenshot above shows the DNS configuration of the alias created earlier in SQL Configuration Manager.  The Fully qualified domain name (FQDN) will populate automatically as you provide the Alias name.  Though I have stripped out the true FQDN for security purposes, the actual FQDN should resemble something like LAB1.organizationdomain.com.

Finally you will want to specify the FQDN of the target server.  Once this is done, click OK to add the alias to the DNS.

At this point you should be able to connect to the server using the alias name from any machine on the domain.  It’s also worth noting that in organizations with multiple domain controllers you may have to allow the alias to propagate across all domain controllers before it becomes fully accessible.