SQL connection issue

Question

Post reply

SQL connection issue

andrei.vizitiu

SSC Journeyman

Points: 83
More actions
September 4, 2015 at 6:55 am

#304044

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

This is hard to pinpoint due to the infrequent behaviour, but I suspect it is a general performance issue.

Because it is all on a virtual machine the performance issue could be both on the VM as on the host of the VM.

Does the host of the VM service multiple VM's? Is CPU and/or memory over committed?

When you experience the problems, are you able to connect using the DAC connection?

Use this DAC connection to see if actions on the database instance is causing trouble.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

andrei.vizitiu (9/4/2015)
Hello,
....
The only user used for both SSMS and the delphi app is the SA user of sql.
....

BTW: it is bad practice to actively use the SA account. This account should be disabled and you need to use accounts (preferrably Windows accounts using Integrated Security) with minimal permissions.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

andrei.vizitiu SSC Journeyman Points: 83 More actions · Answer 3

The physical machine does hold indeed multiple VMs.

I have tried with a DAC connection and it behaves like the CMD connection: specific select works like "select column1,column, etc from table" but general ones don't (select * from table).

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

Just a thought, but do you have Max memory set for each Instance as to not choke the OS out?

Are you able to RDP in/is windows unresponsive too or is it just SQL?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

andrei.vizitiu SSC Journeyman Points: 83 More actions · Answer 5

The RDP works fine. On the server itself there are no issues. All SQL instances can be accessed ok.

As for the resource allocation the main machine has dynamic allocation and tries to keep everything balanced.

The usage of memory is about 25% and the processor usually stays at 1%.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 6

andrei.vizitiu (9/4/2015)
The RDP works fine. On the server itself there are no issues. All SQL instances can be accessed ok.
As for the resource allocation the main machine has dynamic allocation and tries to keep everything balanced.
The usage of memory is about 25% and the processor usually stays at 1%.

If locally all works fine at the time when the client machines are having problems, I guess it will be a network issue (congestion?). Because locally you experience no issues the conclusion is the SQL instances and (instance-)host machine are working fine.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

andrei.vizitiu SSC Journeyman Points: 83 More actions · Answer 7

I was discussing again with our network technician and he discovered that there is a compatibility issue between the routers that link our current location to the server location (TP LINK router versus CISCO router).

He will change the TP LINK one, hopefully this is the root of the problem.

I will reply if the issue persists after this. In the mean time I am still testing connections and viewing logs.

andrei.vizitiu SSC Journeyman Points: 83 More actions · Answer 8

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 them can have hickups.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 9

Thanks for the update. This could be helpfull if others end up reading this thread. 😉

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **