Search This Blog & Web

Thursday, June 22, 2017

Creating Alias name for MSSQL Server Instance

Recently we shifted our production database server to new SQL Server instance. Once that activity completed we face a lot of issues to change connection string for running application from one server to other. One the major application is SharePoint because you can’t open Central administration from SharePoint server unless your instance name will be same as before.
After doing some research we find a way around for this problem. We can create database server Alias and Alias name will be same as previous database instance name so that all applications will work as they are working before. Other possible uses to create aliases are database server security in order to hide you database server name or you can create it when Named instance is there to make it easy.
In following scenario, we have Database server with Named Instance. We created Alias to qualify simple name and smooth application execution without changing much. Following are the steps to create Alias.

Open SQL Server configuration manager and go to SQL Native Client Configuration. Click to Aliases as shown in diagram


--

You need to provide following information
Alias name: Name that you want to set for Database server
Port number: Write dynamic or specific port to connect
Protocol: It can be TCP/IP preferably or Name pipe. Selected protocol must be enabled in client protocols.
Server Name: For named instance you need to mention machine\instance name, For default instance we can mention local host.

You can mention specific port number or you can find dynamic port from SQL Server Network Configuration. Open TCP/IP properties and go all the way down you will see IPAll.


--
If you have 64 bit operating system you need to configure Aliases in SQL Native Client 11.0 configuration and SQL Native Client 11.0 Configuration (32bit).


--

In the following screen shot. You can see both Instances are connected.



--