SQL Connection issue

SQL Server Administration
1

Hello,

I have a virtual server machine that has multiple SQL instances installed (2 x 2008 R2, 1 x 2012, 1 x 2014)
There are database on all the instances.

Users can work remotely (from their computers) on those instances using SSMS or a delphi application. After a certain time (random), random users start experiencing issues with the connection.

The behavior is the following when the connections issues start:

  • SSMS can connect to any instance, but right after that if I try to use the object explorer to open the database list it hangs (connection time out). The only user used for both SSMS and the delphi app is the SA user of sql.
  • The application also manages first link (the first query is succesful), but after that, OLE DB error, connection time out
  • I tried to connect using CMD and it works, but not entirely. What I can do is select specific columns from tables, but I am not able to run a query such as "select * from table"
  • If the server is restarted everything is ok again but only for a while.

The SQL instances were installed according to Microsoft recommendations: starting with the oldest version first
The windows user the installation was done under is administrator. Firewall has been checked and rechecked.

Has anyone encountered something similar or has any ideas what to check?

Thank you very much

2

Have you checked the max memory of each instance? Make sure the total of all instances leaves room for the OS

3

The processor is around 1% and the memory is at 25% usage.
I have tried a DAC connection and it behaves the same way as the cmd test.

Also, on the server everything is always ok. The SSMS doesn't have any issues if it is ran locally on the server.

4

Hi AndreiViz,
What is the client version connecting to the SQL instance? which SSMS version you using?

I did heard of something like SQL 2014 might upgrade something (i forgot was it .net or something else), that might affect connection, I cannot remember for sure, but it would be better off to keep different major version of SQL separately.

Hope this helps.

5

I have SSMS 2008 R2. My work is mainly on the 2008 R2 instance on server. I will try to put on my local PC the latest SSMS version. The thing is on the server there is a SSMS version for each instance and there are no problems with them.

6

I know it might not be the exact issue, but worth a try....

try to run "netsh winsock reset" on the DB server, and see if that helps you.

7

The issue has been fixed. In the end the fault was in the network, the connection between a TP LINK router and a CISCO one. Apparently these 2 are not compatible and communication between can have hickups.