Some techniques Help you to test the connection of SQL Server between the Servers

Sometimes I faced problems while installing SQL Server and Configuration the Farm and these problems happened between the connection of SQL Server and frontend servers. These problems sometimes related to ports , firewall or these server internally and the others in DMZ etc… so these are some techniques will help you to test the connection and find out which port is used by SQL server.



For example to check which port currently used by SQL Server you have many options:

1.  Go to regedit and the registry key for SQL Server instance name

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

Note: there are two different type of ports in SQL server:

a. Static port: by this option you select specific port and always SQL Server uses this port to listen to the connection.
b. Dynamic port: by this option each time SQL Server restarted it will assign a new port if the last used port closed or not available.

2.Go to SQL Server Configuration Manager >> SQL Server Network Configuration >> Protocols for [Instance Name] >> TCP/IP then right click and select properties

3.Use this Query

use master

go

Xp_readerrorlog

4.Use this Query (this query return information about the connection established to this instance of SQL Server)

select net_transport,local_tcp_port from sys.dm_exec_connections

5.Go to command prompt and run this command which return the list of listened and established prots


netstat –a

6.To test the connection between the servers for example login to frontend server to see this server can connect to SQL server .Go to Control Panel\All Control Panel Items\Administrative Tools and click on Data Sources (ODBC) and try to connect to the instance name.

7.To test the connection also you can use the following steps:
    a. Create abc.udl text file (extension .udl)
    b.Double click to this file and then select the provider and enter the connection information

Last notes:

1. when you test the connection, port... first check it locally in SQL Server server and make sure you can connect to SQL Server
2. when you install SQL Server the default port used is 1433.
3. try to check if you can connect to SQL Server from other server internally or externally and if you face any problems always check the connectivity between the servers and the firewall.
4. there are also other options to check the ports and the connection between the server like telnet ,look@me and other third party tools.