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

Post a Comment

Popular posts from this blog

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'
Read more

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

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. 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 Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Pre login Handshake or Connection Timeout Period

Image
Service was unable to open new database connection when requested. SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16225; handshake=14840; Connection string - Data Source=sqlserverintancenamehere;Initial Catalog=CatalogNamehere;Integrated Security=True;Persist Security Info=False;Max Pool Size=1000;Connect Timeout=20;Load Balance Timeout=120;Packet Size=4096;Application Name=Applicationname hereBusinessLayerHost.exe;Workstation ID=HostnameHere setspn -a MSSQLSvc/hostname.doaminname.net:1433 ServiceAccountNameHere need to run the above command in a command prompt, most of the time you would NOT have permissions to run this, please check with Active Directory Team or Windows Admin te
Read more