How to create an SQL Server Alias name and how to connect it from another SQL Server which is on another Windows Server

 

I have two sql server nodes NODE1 and NODE2 and I am creating an alias for NODE2 default sql server instance and want to give an alias name as myNode2. Follow the procedure below.

1) Connect NODE2 windows server and open SQL Server Configuration Manager and go to "SQL Native Client 11.0 Configuration" and under which you can find "aliases"








Right click and new alias and the below window will pop up. Provide the values as below for the parameters and click on apply and after that ok.

Alias Name: myNode2

Port No: 1433

Server : NODE2
















Now you could see the value like below from SQL Server Configuration Manager.










Try to create the same from "SQL Server Network Configuration(32Bit)" and "SQL NativeClient 11.0 Configuration" side too.  Then only you connect from SQL Server Management side by providing the alias name.  Please notice below image.





Now try to connect to the myNode2 alias name which indirectly routing to NODE2 sql server default instance from the same windows server itself.















Now we need to connect the same SQL Server alias name from another windows server that is NODE1

All the below changes are from NODE1:

Now login into windows server NODE1 and configure the setup below. If you try to connect the myNode2 alias directly it will not allow you, you need to configure the setup below and you may encounter error below.











From window server NODE1 go to run command and type the command below "CLICONFG.EXE".And you can see the image below and click on TCP/IP and bring it to enable state to the right side.











































Now click on "Alias" you could see the image below and provide the values like below. 
Server Alias = MyNode2
Port=1433
NetworkLibrary = TCP/IP

Please don't consider "NODE2s" as it is wrongly typed, consider only "NODE2"





























 an
and click on "OK" and you can see the image below , after that click "Apply" and "ok".





















Even after this you can not connect to "myNode2" SQL Server alias from Windows Server NODE1, the reason  is we need to make some changes in the "host" level. Go to run command in Windows Server NODE1 and type "drivers" and it will take you to "C:\Windows\System32\drivers" path from there you chose "C:\Windows\System32\drivers\etc" and find "hosts" file.













and open "hosts" file with "Open with Notepad" and make the changes below.




Now you can easily connect to the myNode2 SQL Server alias which is created in "NODE2" server from "NODE1".  If yours is application server you need to make changes in your WebConfig file.





Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server