Solved

SQL apps slow over VPN

Posted on 2011-03-01
18
4,400 Views
Last Modified: 2012-05-11
Hi,  I have a 22down/5up cable connection at two sites and have a BOVPN between the two (both Watchguard XTM 505, 11.4).  Everything is running swimmingly with windows file transfers, internet, Outlook and other applications getting expected speed - approx 5Mb/s.  

However, we have two VB apps that pull data from our SQL server at one of the sites, and when they are accessed from the other site through the VPN they take an extremely long time to start up, and an annoying amount of time accessing different records once they are open.

Previous to this set up we had a 1Mb/s P2P connection between the two sites, and that was also annoyingly slow, but it was two times faster than what we are now getting through the VPN. I 'm probably getting a little more latency from this connection (20-30ms) than our previous one (15-20ms).

I know the encryption will add a little overhead, but other traffic is working just fine and the VPN is rated to 250Mb/s.  

Can anyone provide some insight as to what might be causing the slowness for these apps or any troubleshooting I can try to track it down?

I've already tried changing the SQL connection string at the remote site to the IP of the SQL server instead of the NETBIOS name and that had no effect.  

Thanks very much!
0
Comment
Question by:mnation1
  • 9
  • 5
  • 3
  • +1
18 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 35014078
The issue is with the number of queries that the applications sends, receives data, generates the next query, etc.

See whether you have an option to prioritize SQL traffic over VPN. See whether you can increase the allocation of bandwidth/priority to the VPN.
using Quality of Service options.
0
 
LVL 3

Author Comment

by:mnation1
ID: 35014302
Thanks.  I'll definitely try that that and check back in.  Our traffic isn't coming close to 5Mb/s, and the time the apps take to load is the same whether we have all 20 users active or none (after hours).  Any other thoughts would be appreciated.
0
 
LVL 1

Expert Comment

by:csaroli
ID: 35014498
what type of encryption are you using, if you are using 3des you could save some cpu cycles by switching over to aes (if that is an option with your equip)
0
 
LVL 3

Author Comment

by:mnation1
ID: 35014551
Thanks for the suggestion.  Using 256bit AES with SHA1.
0
 
LVL 76

Expert Comment

by:arnold
ID: 35014659
When the application starts, it has to access the database and build the various initial parameters?
(The slow down is not following a user's login.)
i.e. list of users, etc. Could the initialization process when the application is launched be converted into a single stored procedure
exec proc_initial_setup
This will depending on the complexity will be a hit on the sql server.
Shifting as many repeatitive/preditable query sequences into stored procedures/functions may offload the load from the network to the sql server.
0
 
LVL 3

Author Comment

by:mnation1
ID: 35014721
I should admit, my SQL and VB knowledge is not good.  These apps are old and compiled.  Do you think it would be possible to convert to the stored procedure on the SQL end without having to modify the client apps?

The one thing I CAN control on the client is the .ini file where I can specify an application path:

[Application]
Path="driver={SQL Server};Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=[databasename];Data Source=[sqlserverIP]"

My most recent shot in the dark thought is that I could set up an ODBC connection to the server (TCP/IP) and possibly set the above path in the .ini file to use the ODBC instead.  This thought came from the idea that if the apps were somehow coded to use named pipes, maybe this would allow me to use TCP/IP instead (my ignorance is showing through I'm sure).  I've read that named pipes are much slower than TCP/IP - especially over a VPN.  Does that sound at all promising?

By the way, our SQL server was upgraded 6 months ago from 2000 std to 2008 r2 std and all we did to make these apps work is to update that application path with the new server IP, so I'm not sure if there are enhacements/optimizations I can make with the upgraded SQL.
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 300 total points
ID: 35014841
You're using integrated security=SSPI meaning that the credentials of the logged in user are passed and access to the database is granted based on the user.
Presumably, the database compatibility is set at 80 (SQL 2000)  versus 100 (SQL 2008).

Modification to the client would be required to alter the
select columns from table
to
exec initialize_me
Which as a response the application will get in one response what it does over the initial queries it generates.

Do you have the SQL 2000 config utility which you can use to force the client connection over to TCP/IP.
I would think that the developers of the application setup a TCP/IP connection.

You could use the proiler/tunning agent to see whether their is a way to optimize the response to the queries by adding indexes, statistics etc. to improve the execution plan of the queries.
 
0
 
LVL 3

Author Comment

by:mnation1
ID: 35014922
Thanks, arnold.  The SQL 2000 config utility and profiler/tunneling (sp?) agent are greek to me so I'll be learning about these immediately.  If you have any other thoughts in the meantime please let me know.
0
 
LVL 7

Expert Comment

by:FemSteenkamp
ID: 35015021
SQL is not very good at low latency, low bandwidth connections just because of teh way that the microsoft SQL data transfers happen.

although tweaking the thruput will undoubtably help, you are not going to change the underlying sql transfer (that  does a LOT of small data teransfers).

have you considered hosting the ap on a Terminal Server and have the remote users access it on the TS server at teh main office.   Ts sessions is very lightweigt for the link and all SQL traffic is kept at the main office.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 3

Author Comment

by:mnation1
ID: 35018708
I'm coming to learn that about SQL's trouble with latency and low bandwidth, I was just surprised by the performance hit moving from a 1 Mb/s open connection to a 5 Mb/s VPN connection (the latency isn't a huge difference, and the bandwidth is considerably higher.  I've definitely considered Terminal Server as a last resort.
0
 
LVL 3

Author Comment

by:mnation1
ID: 35019405
@arnold  Can you tell me if I need to run the SQL config utility on the workstation, SQL server, or both?
0
 
LVL 76

Expert Comment

by:arnold
ID: 35019514
On the workstations to force the client over TCP/IP.
On the server, you should check the MSSQL 2008 configuration tools to make sure it has the TCP/IP protocol enabled for the server which I suspect it has since you are able to remotely connect.
The issue in the slow down might also be that the application was developed with sql 2000 in mind but you had the same issues prior to the upgrade.
0
 
LVL 3

Author Comment

by:mnation1
ID: 35038071
I played with the SQL client config utility, and made sure to force the client to connect using TCP/IP, and there was no change in the speed.

I also ran a SQL trace using the tuning template isolating the startup process of each app.  That was a real eye-opener:

For one of our apps there were 7000+ rows in the trace.  For the other there's 4700+ rows.  In both trace logs the vast majority of the events are

exec sp_cursorfetch 180150003,32,1,1
and
FETCH API_CURSOR0000000000000002

There are literally thousands of these events, and none of them take any time for SQL to process, but I'd guess they contribute quite a bit to the slowness.

The SQL tuning advisor said we could get 7% efficiency out of one app, and 23% out of the other by indexing a few tables.  I'm unsure whether this would have any significant impact on the speed through the VPN (I'm leaning towards no).  I also don't know if creating those indexes could possibly break the apps we have.

If you have any thoughts given the new information please let me know (especially if you have an idea how to get rid of those pesky cursor fetches!).  I'm waiting until the weekend to try some things with the MTU size between firewalls, and the QoS suggestion.

Thanks for your continued interest.
0
 
LVL 76

Expert Comment

by:arnold
ID: 35038435
What is the index that it is suggesting?  Presumably it will merely add an index on a regularly queried column as long as it is not an exclusionary/unique index, you should be fine.
Do you have an option to try this in a test environment? where you can test the addition of the indexes and whether it impacts the application?

index that use exclusionary rules i.e. require unique, no duplicates, etc. could have an impact during inserts/updates if the application does not check and tries to insert/update a record where one exists or with the change will match an existing one.

The slowness is not less to do with the performance/responsiveness of the SQL versus
the thousands of individual queries.

Was statistics type entries also suggested?  These help in the formulation of the execution plan.

0
 
LVL 3

Author Comment

by:mnation1
ID: 35039666
It appears it would only be creating an index to columns.  I plan to make a copy of the database to test this.  

If by "statistics" entries, you mean "Performance Statistics" (an optional event to trace when creating a trace template) then no.  I'll see if I can put together a new template that includes that and also some educated guesses about other events that might be useful and run the tuning advisor on that.

I'll post the results when possible.
0
 
LVL 7

Accepted Solution

by:
FemSteenkamp earned 200 total points
ID: 35056944
the BIG problem is the latency.  if you run a packet trace you will see thousands of packets between sql and client.

on a lan the latency ia maybe 1-4 ms, so the addiitonal latency that the 5 MB Link  incur is for each round trip, and unfortuanltly a lot of htese are sequential, ie SQL waits for confirmation about previous paket before sending next packet.  

instead of Terminal services, you can look at hardware accelarotors like riverbed steelhead appliances

http://www.netutils.com/documentation/Riverbed/FeatureBriefs/MSSQL.pdf

and no i am not working for riverbed, but have implemented their technology over low bandwith  high latency lines to increase performance

yes it will cost money, but the upside is that other traffice like fileshares etc should also be significantly faster between teh sites.
0
 
LVL 7

Expert Comment

by:FemSteenkamp
ID: 35056962
to see if the latency is the only issue, check out this link that ahs software that can simulate latency so you can test the VB apps

http://sqlblog.com/blogs/john_paul_cook/archive/2008/04/13/simulating-high-latency-and-low-bandwidth-for-database-connectivity-testing.aspx
0
 
LVL 3

Author Comment

by:mnation1
ID: 35073646
I think it really is boiling down to the latency.  I ran new SQL traces with maximum detail, applied all the tuning advisor recommendations, and still have the same speed issues.  I also worked with our firewall vendor to rule out any misconfigurations in the VPN such as MTU size and encryption modes.  I switched one of our sites back to the P2P T1 and confirmed that the latency is about 6 times less on average than through the VPN, and that correlates well with the time difference it takes to open the SQL apps from each corresponding location.  As a last resort I'm having our ISP look at the latency to see if there's any better they can do.

@Fern - I tried to install the VE desktop client, and it hung, but thanks for the suggestion.  I'll also take a look at the hardware accelerator you suggested.

Thanks for everyone's help.  Especially arnold who taught me a lot about SQL optimization.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is an article about my experiences with remote access to my clients (so that I may serve them) and eventually to my home office system via Radmin Remote Control. I have been using remote access for over 10 years and have been improving my metho…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now