473,320 Members | 1,854 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

sql server and vpn: performance/speed

Hello NG,

We have a performance problem in using a client
server solution based on MS SQL-Server 2000 through
a VPN tunnel (via broadband internet connection).

The SQL Server is running on a Windows 2003 Server
which is configured as VPN server as well.

We figured out that not the performance
of the VPN tunnel itself is the problem,
but the problem is the access to the MS SQL Server.

We tested it by executing sql statements on the Query Analyzer
on the VPN client (WIN XP built in) which is connected to the server
through the tunnel.

For example execution of the SELECT statement
"select TOP 1000 * from Items" on the
database trough the tunnel takes more than 30 times as much
than the same execution on the server itself or within the
server's local network.

But we can make FTP downloads from our server through
the tunnel with 600 KBit/s under same conditions
so the bandwidth of the tunnel should not be the problem.

Do you have any ideas how we can accelerate this
or does any one has made experiences
related to this topic?

Thanks for your help!

JENS

Jul 23 '05 #1
5 2702
JENS CONSER wrote:
Hello NG,

We have a performance problem in using a client
server solution based on MS SQL-Server 2000 through
a VPN tunnel (via broadband internet connection).

The SQL Server is running on a Windows 2003 Server
which is configured as VPN server as well.

We figured out that not the performance
of the VPN tunnel itself is the problem,
but the problem is the access to the MS SQL Server.

We tested it by executing sql statements on the Query Analyzer
on the VPN client (WIN XP built in) which is connected to the server
through the tunnel.

For example execution of the SELECT statement
"select TOP 1000 * from Items" on the
database trough the tunnel takes more than 30 times as much
than the same execution on the server itself or within the
server's local network.

But we can make FTP downloads from our server through
the tunnel with 600 KBit/s under same conditions
so the bandwidth of the tunnel should not be the problem.

Do you have any ideas how we can accelerate this
or does any one has made experiences
related to this topic?

Thanks for your help!

JENS


I don't think you can easily compare download speed (simple
unidirectional) with JDBC server communications (several calls back and
forth). Network latency is probably killing you. This latency might stem
from WAN and / or from encryption overhead.

You can try this

- increase fetch size for query results

- choose another select method (cursor vs. direct)

- tweak network settings to change the size of packets transferred

IMHO any DB protocol is sub optimal for wide area deployment. I'd rather
use a protocol tailored to your application's needs between client and
server, which also means you need another component on the server side
that is a client to the db and a server to your clients (middle tier).

Kind regards

robert

Jul 23 '05 #2
JENS CONSER (je**@e-sistrum.com) writes:
We tested it by executing sql statements on the Query Analyzer
on the VPN client (WIN XP built in) which is connected to the server
through the tunnel.

For example execution of the SELECT statement
"select TOP 1000 * from Items" on the
database trough the tunnel takes more than 30 times as much
than the same execution on the server itself or within the
server's local network.

But we can make FTP downloads from our server through
the tunnel with 600 KBit/s under same conditions
so the bandwidth of the tunnel should not be the problem.


When you run QA directly on the SQL Server Box, or in your local
network, you have something like 100 MBit/s. 600 KBit/s is far
more than 30 times slower.

What speed is your VPN connection labeled at?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
I dont know much about VPN (yet) but I know that SELECT * causes an
extra trip to the database to fetch the fieldnames.

Are enterprise manager tools required a lot in the live environment?
if so you could look at the SQL Server Web Data Administrator, but
would need to deploy IIS/.NET.

http://www.microsoft.com/downloads/d...displaylang=en

Jul 23 '05 #4
we did the ftp-test only to proof that the tunnel speed is not
the reason and to see the current performance of the
tunnel. the vpn connection is shown on the network monitor
on the server with 28kbit/s and on the client with 100mbit/s
but both has nothing to do with the real performance.
especially the server speed is a real fake.
and also in a lan the speed is not real 100 mbit/s
despite it is shown for the network adapter.
so approx. 600 kbit/s should be the maximum performance
of the tunnel from our client (in egypt) to the server (in usa)

when i check the usage of the tunnel i see e.g. for this
select statement a data transfer of around 15 kbit/s.
so the tunnel bandwith should not be the reason.

when i executed the sql statement it takes 5 seconds
on the lan and more than 3 minutes through the tunnel.

i tend more to roberts opinion that the network latency is
the reason and the bidirectional communication,
although i can't image that a simple select causes
so much communication.

or does the server send each row seperately and waits
for the response :) ?

by the way ... i only used the query analyzer for testing,
as i wrote we have the real problem with our client-server-application.

i think i will try first to increase the packet size like robert said,
does anyone know how to do it in windows, before i waste
time to serach for it?
i have to do it on the client and on the server as well, right?

robert, is the increasing of the fetch size something global
(which i would need) or only a option in the query analyzer.

thanks for your help
JENS

Jul 23 '05 #5
JENS CONSER (je**@e-sistrum.com) writes:
we did the ftp-test only to proof that the tunnel speed is not
the reason and to see the current performance of the
tunnel. the vpn connection is shown on the network monitor
on the server with 28kbit/s and on the client with 100mbit/s
but both has nothing to do with the real performance.
especially the server speed is a real fake.
and also in a lan the speed is not real 100 mbit/s
despite it is shown for the network adapter.
so approx. 600 kbit/s should be the maximum performance
of the tunnel from our client (in egypt) to the server (in usa)
OK, but I assumed that there is some underlying physical connection,
and this one should have some speed as labeled by the service provider.
when i executed the sql statement it takes 5 seconds
on the lan and more than 3 minutes through the tunnel.
I don't know what sort of statement you executed, but does response
time change if you first say SET NOCOUNT ON?

Also, if you are in Query Analyzer, do you get different results for
grid mode and text mode?
i tend more to roberts opinion that the network latency is
the reason and the bidirectional communication,
although i can't image that a simple select causes
so much communication.
Looking at the application, it may matter what access methods you
use. Server-side cursors could have quite some penalty here.
i think i will try first to increase the packet size like robert said,
does anyone know how to do it in windows, before i waste
time to serach for it?


I don't know about Windows, but you can set the packet size for
SQL Server communication when you connect. For instance, in
Query Analyzer you find this under Tools->Options->Connection.
It's also possible to set a different default packet size for
the server with sp_configure.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: neptune | last post by:
I've been reading the numerous posts on using SQL Server vs. Access, but still don't know if SQL server is right for my applications. I work in a section of a large corporate accounting department...
9
by: Morning DJ | last post by:
I have read many posts which say putting a back end mdb on a wan will make for some sorry performance for those not local to the machine with the mdb. Is the same true if the back end is an MSDE...
3
by: Patrick Fisher | last post by:
What are the implications of running a MS-Access BE and an SQL-Server BE on the same Terminal Server? Assuming that the TS can handle the load are there any other issues? Thanks Patrick
2
by: steve | last post by:
Hi All Has anybody had experience connecting to SQL server 2005 on a remote site via VPN A client has a warehouse in each capital city in Australia and wants to have them all record sales etc...
14
by: Toni | last post by:
I have some program in VB6 (I can make new in .NET if it is necessary). This program is working with SQL Server 2000 database from long distance (computers are in agencies and is connected to DSL...
1
by: jonathan184 | last post by:
Hi before i put on the router. I setup a basic vpn server and was able to connect and authenticate using pptp. my client side i am using windows xp pro ms vpn client On the server it DSL...
0
by: KIKOY | last post by:
HI! I am using sql server 2000 for database and VB6 as programming language. my program is working in our LAN. my problem is when I used VPN connection for the remote offices to the main...
0
by: Bill E. | last post by:
I will be creating an application using MS Access as a client to SQL Server 2005. Each user will have the client installed on his/her machine. Some users will be attached to the local network...
15
by: ingejg | last post by:
I am starting to study internet synchronization, and my head is still spinning since internet is not my forte, however my boss is breathing down my neck at the moment. Our company has only one...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.