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.
Right-click on Aliases and click on 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
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
Before we create Alias, let’s try to connect with the desired name (MySQLInstance) to make sure we cannot connect using that 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.
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.
Click Apply and Ok. You can see SQL Server alias in the Aliases list.
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.
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.
Right-click on it and enable it. It gives a warning message that change will not take effect until the SQL Service is restarted.
Click Ok and restart SQL Services for the specific instance. Right-click on SQL Service and restart it.
Try to connect SQL Server instance again using SQL Server Alias. The connection is successful now.
Execute the following query to verify the server name and instance name:
|
1 2 |
SELECT HOST_NAME() AS ServerName, @@ServiceName AS SQLInstance; |
In the following screenshot, we can verify that alias [MySQLInstance] points to [Kashish\SQL1] instance.
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.
It gives you the following warning. Click on Yes to remove the alias.
It removes the defined SQL Server 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.
Launch Cliconfg.exe, and it opens the following wizard.
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.
Click Ok, and you can see configured sever alias as shown in the following screenshot.
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.
The connection is successful, and we are connected with the SQLShack alias name in SSMS.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023