SQL issue or network issue

[jacktheripper88]

Hey everybody,
Got an issue that has me a bit puzzled. I work in a PC repair shop that deals with businesses and home users, so quiet a wide array of issues to deal with on a day to day basis.

I have one customer who has a duty free store. All of his POS systems use software from a company called Winward, the individual systems are standalone and their server is visible as a shared drive where the company data is kept for all their daily transactions.
Installed on the server is Pervasive SQL Server. From what I understand of Winward they rely on the SQL Server to coordinate the new data being added from each transaction.

That is the detail of their setup, now for the issue.

They have one desktop, their Cash One, that is constantly loosing its connection to the server. It hangs while checking inventory or during a transaction and reports it has lost connection.
Every time I've gone on site and run a ping test to the server I have no issues what so ever, never misses a reply, all the ping times are between 1ms and 15ms, browses online.
From this I can only assume that there is no issue with his physical network, or with the machine itself. My guess is that for some reason there is an issue with the SQL server connection.

None of the of systems are having this issue, but I'm a little at a loss on how to check and monitor the SQL connection.
The tech support from Winward is quiet lacking as they are on the west coast and do not open early enough to accommodate us in the east, or you get the lackluster bunch from the Philippians.

Thanks in advance!

 

[cbrunny]

<-- not a pro. grains of salt.

1. I would stress test the network connection. Force it to transfer large files from client to host pc in both directions and see what happens. Test another machine and see if you get same result.

2. I would test the SQL by writing basic simple codes and seeing if they run properly on client. As I understand it, the connection is either open or closed, so there should also be some log that monitors where and when it is open. My guess is this log is server side. I'd check that for a connection opening and closing frequently.

 

[jacktheripper88]

I will have to keep the large file idea in my head the next time I'm out that way. Any suggests on how to monitor the transfer? Should I just rely on the file transfer tool built into Windows or use another utility like Teracopy or Extremecopy?

My experience with SQL is pretty nonexistent, what would you suggest I write for code?

 

[cbrunny]

I'd keep it simple until there's a reason to use a bigger tool. Just use Windows and let it do its thing. The graph will be enough to know if its network issue.

as for code, just use something like:

"select * from [table name]"

and see what happens. it doesn't matter what data you pull, just that you pull data.

 

[nakedfrog]

Are these running wired or wireless?
When you ping, are you just doing the standard ping that executes four times? You might try a continuous ping. Also try doing a telnet from the PC to the SQL port, I imagine it's probably running on the default port.

 

[jacktheripper88]

They are running wired. While I was onsite it was a continuous ping, ran it for 5 or 10 minutes, no losses, very low response rate. I'll have to look into how to do the telnet, not something I've had to do for a number of years.
Can anyone tell me how to write the code for the SQL? I've never had to deal with it directly

 

[cbrunny]

"select * from [table name]"

 

[jacktheripper88]

They are running wired. While I was onsite it was a continuous ping, ran it for 5 or 10 minutes, no losses, very low response rate. I'll have to look into how to do the telnet, not something I've had to do for a number of years.
Can anyone tell me how to write the code for the SQL? I've never had to deal with it directly

 

[mvbighead]

Sporadic loss of connectivity to me sounds like a network connection problem.

If it's simply a few stations connected to a 'dumb' switch, I would simply move connections around (EG - everything goes to the next port). Could be a bad port on the switch. If the problem moves, you likely have a bad port on the switch. If nothing happens...

Next guess would be the cable has an issue. This could be a that it is occasionally shorting, who knows. If you have a cable tester, test end to end (likely a good first step). If there is a short, it would find it most likely. If you have the option, run a new cable to the system.

All in all, usually when a connection is lost, it relates to a system and not the things the system is connecting to. A connection to SQL either works or it doesn't. If it's sporadic, I strongly doubt it has anything to do with SQL unless it is something that only happens at a particular function or job.

 

[Oyeve]

I highly doubt its an SQL issue. Is this PC connected to a managed router? Is it connected the same as the other computers? I have seen this issue many time at my company and its always been connection issues to managed hub/routers. For instance, at one of my locations we have 10 PCs. All connected to the same local network that has one SQL server for a specialized database. 2 of these PCs were connected to a wireless access port. These 2 PCs would always drop the SQL connection even tho everything was on the same LAN. Its some firewall setting on the AP.

 

[John Connor]

Off topic, but your username has 88 at the end. Did you know Jack The Ripper was committing his murders in 1888? White Chapel if I remember right. And I believe it was Aaron Kosminski.

Anyway, back to your thread already in progress.

 

[Lorne]

Power management on the NIC?

 

[Ertaz]

Have you looked at event viewer on both the client and the server for issues occuring around that time?

 

[PliotronX]

Try appending the hostname (NETBIOS) and IP of the SQL server to the hosts file of the Cash One client as well as the domain name to the PDC IP (if applicable). Should look like this:

172.16.10.31 sqlserver
172.16.10.30 waywardwinward.local

 

[Railgun]

Sporadic loss of connectivity to me sounds like a network connection problem.

If it's simply a few stations connected to a 'dumb' switch, I would simply move connections around (EG - everything goes to the next port). Could be a bad port on the switch. If the problem moves, you likely have a bad port on the switch. If nothing happens...

Next guess would be the cable has an issue. This could be a that it is occasionally shorting, who knows. If you have a cable tester, test end to end (likely a good first step). If there is a short, it would find it most likely. If you have the option, run a new cable to the system.

All in all, usually when a connection is lost, it relates to a system and not the things the system is connecting to. A connection to SQL either works or it doesn't. If it's sporadic, I strongly doubt it has anything to do with SQL unless it is something that only happens at a particular function or job.

I highly doubt its an SQL issue. Is this PC connected to a managed router? Is it connected the same as the other computers? I have seen this issue many time at my company and its always been connection issues to managed hub/routers. For instance, at one of my locations we have 10 PCs. All connected to the same local network that has one SQL server for a specialized database. 2 of these PCs were connected to a wireless access port. These 2 PCs would always drop the SQL connection even tho everything was on the same LAN. Its some firewall setting on the AP.

Haven't you guys ever heard of "It's not the network!"

Based on the information given, it points to an SQL/app issue. If I had a dime for every performance issue from some client to a DB server that was blamed on the network...

If the issue is when a query is being made, then why would that not be an SQL/app issue? If you're only able to do very basic testing, run a query while pinging the server. If you don't drop, it's an SQL issue. I'd also run a capture. What's the underlying OS? If Win, use wireshark. If some Linux distro, tcpdump to a file...then use wireshark. I doubt said query loads any switch/router by the data going across.

I'd push the vendor for support. That's the whole point.

 

[jacktheripper88]

Wow forgot I had posted this and never saw a notification in my email, sorry guys. Let me look back through your posts.

 

[jacktheripper88]

Off topic, but your username has 88 at the end. Did you know Jack The Ripper was committing his murders in 1888? White Chapel if I remember right. And I believe it was Aaron Kosminski.

Anyway, back to your thread already in progress.

Lol, not something I had thought of when I chose the name. Usually I use linkmaster6 but it was taken, so I picked jacktheripper and my birth year

 

[dave_the_nerd]

They are running wired. While I was onsite it was a continuous ping, ran it for 5 or 10 minutes, no losses, very low response rate. I'll have to look into how to do the telnet, not something I've had to do for a number of years.
Can anyone tell me how to write the code for the SQL? I've never had to deal with it directly

15ms for a hard-wired LAN is actually a really bad response rate. All my hard-wired stuff at home and at work is <1ms.

Try replacing the wires first, IMO. In school, we used to say that Rule #1 of Network Troubleshooting was "Always start with Layer 1."

 

[Enigma102083]

Haven't you guys ever heard of "It's not the network!"

Based on the information given, it points to an SQL/app issue. If I had a dime for every performance issue from some client to a DB server that was blamed on the network...

If the issue is when a query is being made, then why would that not be an SQL/app issue? If you're only able to do very basic testing, run a query while pinging the server. If you don't drop, it's an SQL issue. I'd also run a capture. What's the underlying OS? If Win, use wireshark. If some Linux distro, tcpdump to a file...then use wireshark. I doubt said query loads any switch/router by the data going across.

I'd push the vendor for support. That's the whole point.

A 15ms ping to the DB server on the LAN is most certainly a network problem. Most likely cable issue since it's isolated to one machine.

 

[jacktheripper88]

Should've explained the ping timings a little bit better. 15ms was the absolute highest it went maybe once or twice out of the 500 or 600 pings I let it do, the other 99.99% were all 1-2ms

 

[dave_the_nerd]

Should've explained the ping timings a little bit better. 15ms was the absolute highest it went maybe once or twice out of the 500 or 600 pings I let it do, the other 99.99% were all 1-2ms

1-2ms is also too high.

Two devices on the same network (hard wired and with a switch or two between them) should be able to communicate like, well, like this.

Simply the fact that there was EVER a 15ms ping between two of the devices on that network means that you probably have a wiring problem.

 

[PliotronX]

1-2ms is also too high.

Two devices on the same network (hard wired and with a switch or two between them) should be able to communicate like, well, like this.

Simply the fact that there was EVER a 15ms ping between two of the devices on that network means that you probably have a wiring problem.

Hardwire doesn't automatically make it sub millisecond at all times if for example we are talking about routed ports. I've seen inter-VLAN pings do funky stuff as well. I am not convinced it's a layer 1 problem.

 

[mxnerd]

Any Windows ping software that can show ping response time smaller than 1 ms?

Windows ping command can only show ping response time < 1 ms, but not down to 0.0x ms like Linux.

 

[dave_the_nerd]

Hardwire doesn't automatically make it sub millisecond at all times if for example we are talking about routed ports. I've seen inter-VLAN pings do funky stuff as well. I am not convinced it's a layer 1 problem.

Sure, it's not a guarantee. But has OP even tried new wires? :thumbsup: