Rajendra Gupta
Add network library configuration

Overview of SQL Server Aliases

October 7, 2019 by

This article gives an overview of SQL Server Alias and its usage for connecting with SQL Server.

Introduction

Many organizations follow specific naming conventions for the database servers. We might manage multiple servers, and it might be challenging to remember the specific server names. In another scenario, we might be running SQL Server on a specific port other than the default port 1433. In this case, we also require remembering the specific port number and using it in the connection string or in SSMS to make a connection.

We can register the servers in SSMS and give it a friendly name for connecting it with the central management server. You can connect to a specific SQL Server instance using a familiar name from the CMS server only. SQL Server does not recognize that name, and it does not allow connections from any other server using a familiar name.

Consider one more scenario, in which we want to move the databases from server A to server B, but the application should not affect due to that change.

SQL Server Configuration Manager

SQL Server provides a configuration utility SQL Server Configuration Manager. In this configuration manager, we can do the following tasks:

  • View the status of SQL Server services
  • Start, stop, pause, resume the services
  • Change service account and service startup (manual\automatic)
  • Specify SQL Server port ( static\dynamic)
  • Manage server and client protocols

We can launch the SQL Server configuration manager from the start menu. Alternatively, we can look for it in the following directory if Windows is in the C drive.

SQL Server 2019

C:\Windows\SysWOW64\SQLServerManager15.msc

SQL Server 2017

C:\Windows\SysWOW64\SQLServerManager14.msc

SQL Server 2016

C:\Windows\SysWOW64\SQLServerManager13.msc

We require RDP on the SQL Server instance to open the SQL Server Configuration Manager. In the left menu bar, you can find the following options:

  • SQL Server Services
  • SQL Server Network configuration 32-bit
  • SQL Native client configuration 32-bit
  • SQL Server Network configuration
  • SQL Native client configuration

Configuration of SQL Server alias

Expand SQL Native client configuration, and you can find a subfolder Alias. By default, we do not have any alias defined for the SQL Server instance. In the screenshot, we can see it does not see any items for the Alias.

SQL Server alias

Right-click on Aliases and click on New Alias.

create new Alias

We require the following information for SQL Server Alias:

  • SQL Instance name
  • SQL Server port

You can use article Overview of SQL Server Ports to determine the various methods to identify SQL Server ports.

Expand SQL Server Network configuration and click on specific SQL instance. It opens up the protocols list. Right-click on TCP/IP and open the properties. In the IP addresses, you can find out the specific port number.

In my case, I want to create SQL Server Alias for the following information:

  • Instance: [Kashish\SQL1]
  • Protocol: Named pipes

Protocol for SQL Server instance

We can see the following windows for the New SQL Server Alias:

  • Alias Name: It is the familiar name for SQL instance. By default, it shows New Alias. Let’s change it to MySQLInstance
  • Port No: Specify port no of the specific instance
  • Protocol: We can choose from TCP\IP or Named pipe protocol. I want to connect SQL Server using named pipe protocol
  • Server: Specify the instance name. For a default instance, we can specify a server name. We need to use [servername\instance] for the named instance

create new alias

Before we create Alias, let’s try to connect with the desired name (MySQLInstance) to make sure we cannot connect using that name.

Connect using alias name

It tries to connect to SQL Server, but gives the following error message. It cannot open a connection to SQL Server because the network path was not found.

error in connection with alias name

Let’s configure SQL Server Alias with the information provided earlier. Once we provide the server name, it automatically takes value for the pipe name column as shown below.

alias details

Click Apply and Ok. You can see SQL Server alias in the Aliases list.

configured SQL Server alias

Let’s try to connect using this SQL Server Alias name in SSMS.

We get the error message again that the system cannot find the file specified.

Connection error message

Why we get this error message again? It should connect with the Alias name.

Let’s perform some troubleshooting steps. If you recall from the earlier discussion, we want to connect [Kashish\SQL1] instance using named pipes protocol. Named pipes protocol should be enabled in this case.

In the SQL Server Configuration Manager, expand SQL Server Network Configuration and click on the protocol for the specific instance. In this case, we want to connect to SQL1 instance; therefore, click on Protocol for SQL1.

In the following screenshot, we can see that the Named pipes protocol is disabled. We cannot connect using the named pipe protocol until it is enabled.

enable the protocol

Right-click on it and enable it. It gives a warning message that change will not take effect until the SQL Service is restarted.

warning message

Click Ok and restart SQL Services for the specific instance. Right-click on SQL Service and restart it.

Restart SQL Service

Try to connect SQL Server instance again using SQL Server Alias. The connection is successful now.

connection with friendly name

Execute the following query to verify the server name and instance name:

In the following screenshot, we can verify that alias [MySQLInstance] points to [Kashish\SQL1] instance.

Verify host name and instance name

We do not need to remember the SQL instance name and port no to connect. We can specify a friendly name for the SQL Server instance for connection. We call this familiar name SQL Server Alias.

In the above steps, we explained the process of configuration of SQL Server Alias. We need to note here that this alias can be used directly from the server. You require to RDP server hosting SQL Server instance and use the alias. You cannot use the alias name from outside the server. The reason behind this is that alias is not registered in the domain name server ( DNS).

In each organization, we can DNS servers to register server names, and this alias name is not registered in the DNS server. You can contact domain administrators to configure an alias (CNAME).

You can connect to an instance using the alias from outside the server as well after registering alias in DNS.

Delete SQL Server Alias

You can delete alias anytime without influencing the SQL Services. Right-click on the alias name and delete it.

Delete SQL Server Alias

It gives you the following warning. Click on Yes to remove the alias.

Confirm alias deletion

It removes the defined SQL Server alias.

delete alias

SQL Server alias using SQL Server Client network utility

In the above steps, we used the SQL Server configuration manager for the alias. We can use SQL Server client network utility as well. You can find this utility in the C:\Windows\SysWOW64 folder.

SQL Server Client network utility

Launch Cliconfg.exe, and it opens the following wizard.

Add alias in SQL Server Client network utility

In the Alias section, currently, we do not see any SQL Server alias. Click on Add, and it open add network library configuration.

We want to connect with the following information in SSMS:

  • SQL Alias(friendly name): SQLShack
  • SQL instance: [Kashish\SQL1]
  • Port Number: 51425
  • Network libraries: TCP/IP protocol

Specify the input in the network library configuration, as shown below.

Add network library configuration

Click Ok, and you can see configured sever alias as shown in the following screenshot.

Server alias configuration

Let’s connect using newly configured alias SQLShack in the SSMS. We only need to provide the alias name without SQL instance and port number. It automatically redirects the connection to the SQL Server instance on a specified port using the entry in client network utility.

Specify alias in SSMS

The connection is successful, and we are connected with the SQLShack alias name in SSMS.

successful connection using alias

Conclusion

SQL Server alias gives you the flexibility to choose a familiar name for the SQL Server instance. You do not require remembering the instance details such as instance name, port number, and protocol. You can register alias in DNS to use it from every client machine.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

168 Views