Slow loss of connectivity to SQL Server 2005

Question

Client has a dangerously open instance of SQL Server 2005. VB.NET 1.1 connects to this from another server without problem. Turning on Windows firewall with port 1433 open to subnets only seems like a quick stopgap solution.

DB seems fine, but begins to slow down over the course of an hour... As though it is losing connections form it's pool. Eventually no connections are possible from the app or other tools.

Does SQL Server require other ports to be open?

Linux guy here scratching his head.

Answer 1

SQL Server provides a way to monitor open connections under "SQL Server Management Studio". I would check to make sure that connections are not getting opened up and left open from the application. Because unless the application explicitly closes the connection it could be possible for it to remain open until the Garbage Collector decides to finally clean up the object and release the connection back to the pool.

Another way to check and see if this is happening is to wait until the application can no longer get connected. Once in this state if restarting the application solves this problem then it is probably an issue with the application code.

Answer 2

Assuming you're running SQL Server with the defaults, then 1433 should be the only port you need open.

About the slowdown part - use my Perfmon monitoring tutorial to gather statistics about the SQL Server. From there we can determine which part of the server is the bottleneck. If you want, gather the stats as shown in the article for a few minutes while the server is fast, and then again while the server is slow. There's no performance impact of gathering these statistics. Then send the files to me at brento@brentozar.com and I should be able to tell pretty quickly where the bottleneck is.

Answer 3

Run sp_who2 to see who is connected and what they using.

JR

Answer 4

When connections are no longer possible can you connect locally on the box? If so then try connecting again using Management Studio but instead of using "Share Memory" use "TCP/IP".

If the latter fails then indeed I would investigate the firewall situation. However I wouldn't be inclined to associate a firewall setting with "loss of connectivity over time".