SQL Server over WAN

SQL Server over WAN

Post by Gregory Haye » Wed, 14 Oct 1998 04:00:00



Hello,

We are having a large performance problem with SQL Sever over a WAN.  We
have an application which retrieves small result sets that performs
perfectly fine over the LAN (1 to 2 second response), but lags over all Wan
connections (10 seconds to over a minute for the same results).  We've been
able to determine that it is not a Wan bandwith problem (usage is well
below capacity), nor a WAN configuration issue (all routers are at
defaults, and plain 5Mb file copies between points show no performance
problems at all).  We've also been able to determine that the client is
receiving varying packet sizes fromt the server during these requests (some
packets at 1500 bytes, then some much smaller).

We are wondering if this could be a SQL server issue, as we've eliminated
most everything else.  Is there any way to configure SQL Sever network
communications, beyond choosing protocol.  We are using TCP/IP exclusively.
 Any help will be greatly appreciated.

Thanks,

Greg Hayes

 
 
 

SQL Server over WAN

Post by Neil Pik » Thu, 15 Oct 1998 04:00:00


Greg,

 Not really.  Other than using tcp-ip sockets net-lib there's nothing
much you can do from the config side.

 Q.      How can I speed up SQL Server applications running over slow
links.
(v1.0   02.10.1998)

A.  First we need to define what a "slow" link is.  Typically this is
anything from 64Kbit/sec and down.  On links of this speed the size of
a resultset and the number of network packets that are exchanged can
make a significant difference to overall response times.

First, either do a network trace, or use SQL Trace to see what exactly
is being transferred during a typical client session.  Then try the
following :-

1.  If large intermediate resultsets are being returned, then see if
you can write the logic into a stored-procedure so that only the end
results are returned.  Try and reduce the number of sent/received
pieces of SQL by using stored-procedures as much as possible.

2.  If the connection uses ODBC and the overhead it creates running
sp_serverinfo, sp_cursor, temporary stored-procedures etc. is causing
the problem then use passthrough queries if possible and turn off the
temporary stored-proc creation in the ODBC dsn properties.

3.  Configure the db-lib/ODBC connection to use the tcp-ip sockets
net-lib.  This performs best over slow network connections and can make
a significant difference.

4.  Is the application using client-side cursors?  Try v3 or above of
ODBC which should give you transparent server-side cursors.

5.  Don't return 1000 rows to the client if all they need to see on the
screen is the first 20.

6.  If there are large amounts of static data that need to be retrieved
then consider replication to a client copy of Access, SQL 6.5
Workstation or with SQL 7.0 a local copy of SQL Server.  Over slow
links this should only really be used for mainly static data.

7.  Don't send any SQL across the link at all.  Use Citrix or NT
Terminal Edition to run the application centrally and install ICA/RDP
clients on the remote machines.  The applications then all run locally
on a server next to the SQL Server (the same box isn't recommended).  
The only thing that goes across the slow-link are screen-updates, which
are optimised and compressed and so will often work satisfactorily on a
14.4Kbit/sec modem link.  This also has the advantage that there is no
longer any client code to maintain at the remote sites either.  There
are whitepapers on Citrix, MS and Compaq's sites about sizing the
server(s) you will need to run in this mode.

 Neil Pike MVP/MCSE
 Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated
otherwise)

 
 
 

1. Help wanted ASAP: SQL-server in WAN with MS-Access clients

Hello SQL experts,

We have just bought a new SQL 7.0 server. Previously we were using
MS-Access as our "database".

We still do want to use MS-Access as the client in the Client/Server
(read MS-Acces/SQL-server).

Problem :
-------------
We have two different NT networks (says NetworkA and NetworkB)
connected via CISCO 1603 routers (using a ISDN dial-in). The Primary
Domain controllers (PDC) of each network (domainA and domainB)  have a
non-trusted relation with each other.

The SQL server is located in domainA and configured using its own
security and login features (i.e. not that of the PDC of which it is a
member)

We have developed a MS-Acces application that uses ODBC and
pass-through queires to communicate with the SQL server.
The MS-Access Application work fine if started from the same network
and domain as the SQL-server. If started from the other network the
application gives an ODBC and SQL error.

Question
---------------
a. What am I doing WRONG ?
b. Could somebody tell me how to configure the SQL server, NT server
and the MS-Access clients such that MS-Access client application will
be able to communicate correctly with the SQL-server on both networks.

Additional client/server/network information
---------------------------------------------------------------------
1. The networks have two diiferent IP address ranges.

2. Both networks are using NT 4.0 server

3. A client on say network B is able to "ping" the SQL-server on
network A.

4. A client on network B can access files on network A and vica versa

Many thanks in  advance.

Would you be so kind to mail a possible solution to the e-mail
addresses below :


and


Miguel Eersel

2. Generated HTML page in PEQS

3. Problems connecting to SQL Server over WAN

4. US-TX-ORACLE MIGRATION EXPERT

5. how to connect two sql server through WAN

6. ms sqlserver 2000 & sp3

7. SQL Server on WAN

8. Linked Server Question

9. Problems connecting to SQL Server over WAN

10. how to connect two sql server through WAN