Why sometime port number required for SQL Server Instance connectivity?



Situation:

Sometime as a Database administrator, we come across a problem with the SQL Server instance connectivity. Application team has reported a problem that they are unable to connect to the newly deployed SQL Server sever from their web application server. In this scenario, the application web server sits in the DMZ zone and SQL Server is residing in transit zone.  SQL server instance port number (other than default port 1433) is opened on the fireball to allow this communication. However, still application team is getting the connectivity issues.


Solution:

When we install a SQL Server instance then SQL Server Browser service is also get installed as part of the installation. If server has only default instance then browser service would remain in the disabled state. However, for named instance, it plays a significant role while connecting to the SQL Server instance.

Browser service always listen on the UDP port 1434. When client requested to make connection with named instance then its provide the correct port number to the requesting application. For instance, if client has requested to make a connection with Server\SQLNamedInstnace then browser service replies to the client application through UDP port 1434 with the correct TCP/IP port number that the application is intended to connect with. The TCP port number could either be dynamic or static depending upon the infrastructure requirements. The browser service detect all the running NAMED SQL instances and bypass the DEFAULT instance. When client application send a request to connect with the default instance then browser service does not respond to the request. However, service only respond for the named instance connection.

 As mentioned earlier, SQL server instance port is opened on the firewall that allows communication between the client application and SQL server instance. At the sametime,it is mandatory to open the UDP port 1434 on firewall as well, to allow browser service to respond to the application request with the correct instance port number. If UDP port is NOT open on the firewall then, client application MUST have to specify the SQL instance port number while connecting to the instance like Server\SQLNamesInstance,1185 else client application will get the below error:


"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. "

 We can use Windows Telnet command to test if a client application is able to communicate with the specific SQL port. Run below command from command prompt window :

> Telnet  <SQL Server IP>  <SQL Port number>

it should show us a blank screen attached below:



However if there is an error then error cmd window looks like this:














Regards,
Ashish

Comments

Post a Comment

Popular posts from this blog

Property Size is not available for Database '[tempdb]'

Image
Situation: Many times, DBA try to view the TEMPDB properties but they get the following error: The user is unable to view the TEMPDB property even if he has proper rights on the SQL Server instance. In fact, sometimes SYSADMIN role user is also unable to view the property and getting the same error. Solution: For user databases, this error can be resolved by changing the database ownership. We can run this command to check the Database ownership: sp_hepldb 'database name' user databases ownership can be SET by using the following command: USE 'db name' go sp_changedbowner 'sa' However, you can not change ownership of SYSTEM databases (tempdb, master, model & msdb) through the sp_changedbowner command. 'sa' is the default owner of all the system databases. I workaround this problem and found that this error frequently arises when tempdb is highly utilised and lots of contention is there on tempdb. In this scenario, SQL server...
Read more

Split backup files for faster SQL Backups

Image
Situation: SQL backups for large database are always be a challenging task. Depending upon the available system resources, a typical 5 TB database backup may take 6 - 7 hrs to complete. In addition to this, it is very difficult for DBAs to copy a single large backup file over the network. Solution: SQL Server provides a flexibility to split single backup file to multiple files. This would allow SQL server to split the workload by creating multiple threads. The advantages of this is backups will run faster and creates smaller files that can be easily copied across the network. Additionally, in most of the cases, Database administrator takes compressed backups which reduce the backup file size. However, for large database backups, overall backups time would be large as SQL server uses single thread to complete the backup operation. In a critical production environment it is always be a requirement to complete the backups before business starts next day. This would ensure the succe...
Read more

Resolve SQL Server I/O bottleneck issues

Image
Situation: Nowadays, most of the SQL Server environment is running on virtualized environment.  VMWare, Hyperviser, Xen, Elastic compute cloud etc. are the most popular virtualization platform available in the market. As per the recommended best practice, we should have separate dedicated drives for  SQLTemp, SQLData, SQLLog, SQLBinary & SQLBackups. All these drives should be presented as individual LUNs to provide the best I/O throughput to the SQL Server. In-spite of implementing these best practices, SQL Server frequently encounters disk I/O latencies issues in most of the environments. We can see following error in the SQL Server error logs: SQL Server has encountered xxx occurrence(s) of I/O requests taking longer than 50 seconds to complete on file [D:\MSSQL\MSSQL.1\MSSQL\Data\MyProdDB.mdf] in database [database].  The OS file handle is 0x00000000.  The offset of the latest long I/O is: 0x00000000000000 Solution: There are many...
Read more