1

I am hosting an encrypted SQL server database (SQL Server 2012) on a VMware host at my Salt Lake location. My Salt Lake data pipe is 100 Mbps over fiber via Comcast. My Phoenix side has a 50 Mbps over fiber via Cox. My latency between sites is 45 ms. Each office has around 40 users. 20 users are logging into our E-manage application from Phoenix and 20 or so are logging in from the Salt Lake side.

Salt Lake LAN users utilizing E-manage have no speed issues, however, my Phoenix LAN users report slow access when working within the E-manage application. The developer feels that the issue stems from the use of virtualization (VMWare ESxi 5.5). When I examine the resource utilization from VMware and my SAN, I see that all resources (CPU, RAM, etc.) are basically sleeping. I had bandwidth reports run for Cox and Comcast and my bandwidth utilization is within normal ranges. I have no other issues with slowdowns from the internal applications I use.

I have opened tickets with Comcast and Cox to see what can be done to reduce the latency from 45ms down to the high 20's low 30s. I feel that by doing that it would make a difference. I am running WAN accelerators on both sides of the network to accelerate TCP traffic and am excluding SQL traffic. To rule out the Phoenix network, I had a user connect a laptop directly to the Cox fiber circuit and then utilize E-manage. Same results. Response time was slow. I do have an IPSec tunnel between sites and to rule out the IPSec tunnel, I altered the Phoenix DNS record to utilize the public A record I have setup for my SQL server.

I have asked the SQL developer for another customer's contact so I can see what their access/response time is like when running from a remote site. I would be happy to provide more detailed information to see if there is something I am missing. I have also considered running independent SQL queries from a 3rd party to see what the health of my SQL server is and if there are any potential problems with the SQL database.

I do have a physical server that I am prepare to configure to see if it would make a difference.

I look forward to any comments, suggestions, and counsel.

Thanks in advance - Troy

troyB
  • 13
  • 3
  • Blaming virtualization is the low-hanging fruit of buck passing these days. If I had to guess I'd say that your software makes many small queries instead of a few large queries. When the latency between application and DB is <5ms the overhead is not a problem, but once you bump that up to 45ms RTT users might notice some lag. You can try replicating a copy of the app DB to the remote office, but that will present its own challenges as you're not likely going to be able to write to the slave and who knows if this app will handle multi-master replication. – Sammitch Jul 03 '15 at 23:46
  • Agree w/ @Sammitch. The server/virtualization is the same regardless of which location the query/access originates. If server performance, whether RAM, IO or "virtualization" was the problem then users at SLC would experience the issue when the server was unable to provide the resources needed. Since this is not the case then it seems likely latency is the issue. – Ed Fries Jul 06 '15 at 03:54
  • That's what I figured. Thank you Sammitch and Ed for your comments. – troyB Jul 06 '15 at 17:28

1 Answers1

2

IMO (and I don't have any empirical data) running a SQL based application over a WAN connection rarely works well from a performance perspective. There are multiple layers that may be causing the problem for WAN users that aren't going to be readily apparent to LAN users; application latency, SQL encryption latency, operating system latency, network latency, etc. Everything else being equal, the network latency is, as you've stated, probably where the problem manifests itself from a user perspective. Unless you've got a dedicated, private connection between the two locations from the same provider there probably isn't anything either one can do. They only have control over their own network infrastructure, not each others and certainly not anything in between. Your latency doesn't strike me as all too terrible so I'd be interested in knowing what kind of packet loss (if any) occurs between the two offices.

joeqwerty
  • 109,901
  • 6
  • 81
  • 172