Can anyone explain this intolerable latency on a WAN?

[Edward]

I have some clients (there are about six team members) working on an
Access 2003 Front End which is connected by a WAN to an Access 2003
Back End some hundred or so miles away.

When the application starts there are some housekeeping operations
that take place (some once a day when the first person logs on, others
when anyone logs on) before the initial screen is displayed. When the
back-end used to be hosted locally the time to display the first
screen was a matter of five or ten seconds. Now that the back-end is
on the WAN it typically takes TWENTY FIVE MINUTES before the first
screen is displayed.

I've tested the network connection by copying the back-end database in
its entirety onto a local machine. It's only 55 Mb and the copy took
about 55 seconds. I've also configured the application to connect to
the local copy and it runs much, much quicker.

Since the network connection *seems* ok, can anyone suggest any other
possible candidates for the unusable latency that my clients are
experiencing? They are a small team in an enormous organisation with
a very uncooperative IT Support department.

Thanks

Edward

 

[Stefan Hoffmann]

hi Edward,

I've tested the network connection by copying the back-end database in
its entirety onto a local machine. It's only 55 Mb and the copy took
about 55 seconds.

When copying one (big) block of data a high network latency does not
necessarily slow this operation down.

Access/Jet back-end operations over WAN consists of many single network
ops. Here does the network latency adds up to an amount you really have
to care about.

Solutions:
You can optimize your application

or

consider using Terminal Services or the new Remote Desktop Services. So
that the whole application is run on your server and your clients
connect via RDP to that machine.


mfG
--> stefan <--

 

[Paul Shapiro]

You can find lots of postings explaining that an Access backend db is not
meant to be used over a WAN. Besides the performance issue, db corruption is
much more likely because any network interruption to any workstation can
corrupt the db. Either use Terminal Services, so the file access is running
on the LAN and just keystrokes and screen pictures go over the network, or
you could consider using SQL Server for the backend. With Access each
workstation directly reads and writes the db file from the server, so there
is a great deal of network activity. With SQL Server, the Access frontend
just sends sql statements to the db server, and gets recordsets in return.
As long as the application minimizes the data transfer size and the number
of db roundtrips, performance can be excellent on a WAN.

 

[Armen Stein]

You can find lots of postings explaining that an Access backend db is not
meant to be used over a WAN. Besides the performance issue, db corruption is
much more likely because any network interruption to any workstation can
corrupt the db. Either use Terminal Services, so the file access is running
on the LAN and just keystrokes and screen pictures go over the network, or
you could consider using SQL Server for the backend. With Access each
workstation directly reads and writes the db file from the server, so there
is a great deal of network activity. With SQL Server, the Access frontend
just sends sql statements to the db server, and gets recordsets in return.
As long as the application minimizes the data transfer size and the number
of db roundtrips, performance can be excellent on a WAN.

To make Access work with SQL Server, and to get the best performance
over a WAN or any other slower connection, there are techniques you
need to use. Just switching to linked SQL tables usually won't quite
do it.

I've written a PowerPoint presentation on techniques for using Access
as a client-server front-end to SQL Server databases. It's called
"Best of Both Worlds" at www.JStreetTech.com/Downloads. It includes
some thoughts on when to use SQL Server, performance and security
considerations, concurrency approaches, and techniques to help
everything run smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Albert D. Kallal]

Since the network connection *seems* ok

Are you sure?

What is the speed compared to your standard office network?
I don't need or even want an answer of bits, kilo bauds or whatever, but
just a simply factor number.

eg:
My wan is ___ times slower then my standard cheap-o office network.

Most vpn/wans are about 100 times slower. So, do fill in the above number.
When you see that number then I think it goes a long way to helping your
grasp this problem. Is your wan 5 times slower, or 50 times slower? I mean
if something that takes 20 seconds on your regular office network is now 50
times slower then that 20 seconds becomes 1000 seconds (16 minutes!). On
other the hand, as I mentioned, often a wan is 100 times slower so that
gives now 32 minutes. So, fill in the above question of my network is "X"
timers slower. Remember for our discussion we really don't care about mbits,
buads, or or all kinds of fancy mubo jumbo network lingo and stuff. Just a
100% super plane Jane number of "x". times factor is all we need here (save
that fancy talk for the network guy).

, can anyone suggest any other

possible candidates for the unusable latency that my clients are
experiencing? They are a small team in an enormous organisation with
a very uncooperative IT Support department.

Some other helpful posters here have pointed you to my article, but it
should help you resolve much of your problems and help you understand what
you are up against:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[Tony Toews [MVP]]

You can optimize your application

I don't think there's too much that can be done in the area of
optimizing other than moving to SQL Server.

Tony

 

[Tony Toews [MVP]]

Either use Terminal Services, so the file access is running
on the LAN and just keystrokes and screen pictures go over the network,

Actually with Terminal Services aka RDP screen pictures generally
don't go down the wire. I don't know much if any of the details but
apparently there are a lot of raw Windows GDI calls which go down the
wire. So that, for example, changing the colour of the desktop
background sounds only a few bytes down the wire. An extreme example
but hey I was grasping for one.

Tony

 

[Edward]

You can find lots of postings explaining that an Access backend db is not
meant to be used over a WAN. Besides the performance issue, db corruptionis
much more likely because any network interruption to any workstation can
corrupt the db. Either use Terminal Services, so the file access is running
on the LAN and just keystrokes and screen pictures go over the network, or
you could consider using SQL Server for the backend. With Access each
workstation directly reads and writes the db file from the server, so there
is a great deal of network activity. With SQL Server, the Access frontend
just sends sql statements to the db server, and gets recordsets in return..
As long as the application minimizes the data transfer size and the number
of db roundtrips, performance can be excellent on a WAN.

Thanks to one and all - stuff here I never realised! Unfortunately
there are reasons why SQL Server is not an option - at least not
without a major redesign of the Front End. (Briefly, I didn't design
any of this - I just have to maintain it. The original designer
exposed the Query Designer through the Front End (yes, I know, don't
tell me!) and changes to queries have to be propagated to other
users. I cannot see how this could be done with SQL Server, however).

I will recommend the TS route to the clients.

Thanks

Edward

 

[Larry Linson]

How are the queries changes propagated?
Not a shared front end I hope?

"Hope springs eternal," it is said.

But, if trying to run a split Access/[Jet|ACE] database on a WAN (or worse,
just having everyone log in to the same, unsplit database), it wouldn't
surprise me. And, if that is so, with all the executing objects having to
come across the WAN as well as data, "intolerable" would certainly be an
appropriate term but "latency" might well be replaced with "eternity".

Larry Linson
Microsoft Office Access MVP

 

[Roger]

 > How are the queries changes propagated?
 > Not a shared front end I hope?

"Hope springs eternal," it is said.

But, if trying to run a split Access/[Jet|ACE] database on a WAN (or worse,
just having everyone log in to the same, unsplit database), it wouldn't
surprise me. And, if that is so, with all the executing objects having to
come across the WAN as well as data, "intolerable" would certainly be an
appropriate term but "latency" might well be replaced with "eternity".

 Larry Linson
 Microsoft Office Access MVP

and pulling executing objects across a lan can cause problems (even if
your data is all on sql server)
so, in a single user environment, if you have your front end on
server1, and you are running the front end from your desktop (ie. a
shortcut), errors can still occurs, usually as 'disk or network
errors'

even if you're using new gigabit switches with expensive cabling and
your desktop is sitting next to server1

given that you have to use executing objects within the access front
end, and that you might want to do so on your desktop, and that this
problem can easily be duplicated, you'd think Microsoft would be able
to design a solution for this... nope

 

[Larry Linson]

I've been implementing solutions in Access for clients since 1994, and never
had a situation where there was a "need" or "requirement" to execute the
front end from a server or shared folder on another PC in the network. As
far as I am concerned, separating the data (back end) and user interface
(front end) and putting a copy of the front end on each user's machine is a
reasonable "solution". You can also use Terminal Server, but, even so, each
user needs their own copy of the front end in their own disk space on the
server.

There were times when the client's IT department _desired_ for that to be
the case. I cautioned the client that was a recipe for disaster, and that I
would not be associated with such an implementation because I had a good
reputation that I wished to protect.

On the other hand, some years ago, I was in a client environment which along
with more current networks had an older 4MBPS ethernet. Our team had a small
database that we used only for our own "support", and as an experiment we
put it on a server on the 4MB net, and executed it from there. It was slow,
but we did not have any corruption problems - it was lightly used, so one
user at a time was normal, and, if ever there was more than one concurrent
user, it was likely only two. That client had a superb networking
department, and that network, tho' old and slow, was solid and stable.

Larry Linson
Microsoft Office Access MVP

How are the queries changes propagated?
Not a shared front end I hope?

"Hope springs eternal," it is said.

But, if trying to run a split Access/[Jet|ACE] database on a WAN (or
worse,
just having everyone log in to the same, unsplit database), it wouldn't
surprise me. And, if that is so, with all the executing objects having to
come across the WAN as well as data, "intolerable" would certainly be an
appropriate term but "latency" might well be replaced with "eternity".

Larry Linson
Microsoft Office Access MVP

and pulling executing objects across a lan can cause problems (even if
your data is all on sql server)
so, in a single user environment, if you have your front end on
server1, and you are running the front end from your desktop (ie. a
shortcut), errors can still occurs, usually as 'disk or network
errors'

even if you're using new gigabit switches with expensive cabling and
your desktop is sitting next to server1

given that you have to use executing objects within the access front
end, and that you might want to do so on your desktop, and that this
problem can easily be duplicated, you'd think Microsoft would be able
to design a solution for this... nope

 

[Roger]

I've been implementing solutions in Access for clients since 1994, and never
had a situation where there was a "need" or "requirement" to execute the
front end from a server or shared folder on another PC in the network. As
far as I am concerned, separating the data (back end) and user interface
(front end) and putting a copy of the front end on each user's machine isa
reasonable "solution".  You can also use Terminal Server, but, even so,each
user needs their own copy of the front end in their own disk space on the
server.

There were times when the client's IT department _desired_ for that to be
the case.  I cautioned the client that was a recipe for disaster, and that I
would not be associated with such an implementation because I had a good
reputation that I wished to protect.

On the other hand, some years ago, I was in a client environment which along
with more current networks had an older 4MBPS ethernet. Our team had a small
database that we used only for our own "support", and as an experiment we
put it on a server on the 4MB net, and executed it from there.  It was slow,
but we did not have any corruption problems - it was lightly used, so one
user at a time was normal, and, if ever there was more than one concurrent
user, it was likely only two.  That client had a superb networking
department, and that network, tho' old and slow, was solid and stable.

 Larry Linson
 Microsoft Office Access MVP

"Hope springs eternal," it is said.

But, if trying to run a split Access/[Jet|ACE] database on a WAN (or
worse,
just having everyone log in to the same, unsplit database), it wouldn't
surprise me. And, if that is so, with all the executing objects having to
come across the WAN as well as data, "intolerable" would certainly be an
appropriate term but "latency" might well be replaced with "eternity".

Larry Linson
Microsoft Office Access MVP

and pulling executing objects across a lan can cause problems (even if
your data is all on sql server)
so, in a single user environment, if you have your front end on
server1, and you are running the front end from your desktop (ie. a
shortcut), errors can still occurs, usually as 'disk or network
errors'

even if you're using new gigabit switches with expensive cabling and
your desktop is sitting next to server1

given that you have to use executing objects within the access front
end, and that you might want to do so on your desktop, and that this
problem can easily be duplicated, you'd think Microsoft would be able
to design a solution for this... nope- Hide quoted text -

- Show quoted text -

here's a situation... using citrix with 6 citrix servers and a domain
controller
I'm the only developer, but for any given login, I could land on any
of the 6 citrix servers

so it doesn't make sense to put the development folder on a citrix
server (cause you don't know which one to put it on)

and putting the development folder within my user profile doesn't make
sense, since roaming profiles are used and the system would have to
copy the development folder from the HOME folder when I log in

so I put the development folder on the domain controller, with a
shortcut in my user profile...

so I login and I'm connected to ctxN, accessing an mdb on DC (which
has links to sqlserver tables)
and I occasionally get disk/network errors

if I move the development folder to ctxN, and I set my citrix policies
to always logon to ctxN, so that everything is on ctxN, everything
works fine

these are all Microsoft products, and they're smart people - why can't
they fix this

 

[David W. Fenton]

Actually with Terminal Services aka RDP screen pictures generally
don't go down the wire. I don't know much if any of the details
but apparently there are a lot of raw Windows GDI calls which go
down the wire. So that, for example, changing the colour of the
desktop background sounds only a few bytes down the wire. An
extreme example but hey I was grasping for one.

RDP speaks the Windows GDI primitives more-or-less directly, rather
than sending bitmaps (as is the case with VNC, for instance). In
other words, what the remote computer gets sent is for all practical
purposes the same thing that GDI.exe on the server would send to the
server's local graphics card.

It's a little more complicated than that, but the basic idea is
correct in regard to how it reduces traffic so much.

 

[David W. Fenton]

:

you'd think Microsoft would be able
to design a solution for this

Um, they *have* designed a solution for it -- Windows Terminal
Services.

And it works phenomenally well.

It is easy to maintain.

It is also much less expensive than any of the alternatives
(redeveloping for SQL Server, or porting the app to run in a web
browser).

 

[David W. Fenton]

On the other hand, some years ago, I was in a client environment
which along with more current networks had an older 4MBPS
ethernet.

My first paid Access app ran on a 10Mbps LAN (this was 1996), and it
ran fine. Sure, the first time you dropped down some of the combo
boxes was slow, but after that, Access cached the data and it wasn't
a problem at all.

I was pretty darned impressed, and it was really badly-designed app
to boot (I was even more of a moron then than I am today, if you can
believe it).

 

[David W. Fenton]

m:

these are all Microsoft products, and they're smart people - why
can't they fix this

Uh, you kept mentioning Citrix, which is categorically *not* a
Microsoft product. And this bit of roaming profiles and dynamic
choice of Citrix server is all something that Access was simply not
designed for.

You can also get yourself in huge trouble if you put an Access back
end on a server using file replication. This is not a Microsoft
problem, but a case where you need to choose your technology
appropriately and deploy it according to the specifics of the
environment in which it's running.

 

[Larry Linson]

It is also much less expensive than any of the
alternatives (redeveloping for SQL Server, or
porting the app to run in a web browser).

I have, on occasion, brought frowns to a number of colleagues who leapt
feet-first into the world of DotNet when I contend that there are many more
web applications than actual requirements for web applications.

But, I suppose it gave some of those clients "bragging rights" at their
professional societies, even if it cost them dearly in development time,
effort, and user friendliness. And, I am quite certain that it provided
lucrative opportunities for some of those DotNetters.

Larry

 

[Tony Toews [MVP]]

here's a situation... using citrix with 6 citrix servers and a domain
controller
I'm the only developer, but for any given login, I could land on any
of the 6 citrix servers

so it doesn't make sense to put the development folder on a citrix
server (cause you don't know which one to put it on)

and putting the development folder within my user profile doesn't make
sense, since roaming profiles are used and the system would have to
copy the development folder from the HOME folder when I log in

so I put the development folder on the domain controller, with a
shortcut in my user profile...

so I login and I'm connected to ctxN, accessing an mdb on DC (which
has links to sqlserver tables)
and I occasionally get disk/network errors

Are ctxN and DC in the same server room? Or are they geographically
separated?

Tony

 

[Roger]

Are ctxN and DC in the same server room?   Or are they geographically
separated?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
Granite Fleet Managerhttp://www.granitefleet.com/- Hide quoted text -

- Show quoted text -

same room, same mounting rack

 

[lyle fairfield]

 > It is also much less expensive than any of the
 > alternatives (redeveloping for SQL Server, or
 > porting the app to run in a web browser).

I have, on occasion, brought frowns to a number of colleagues who leapt
feet-first into the world of DotNet when I contend that there are many more
web applications than actual requirements for web applications.

But, I suppose it gave some of those clients "bragging rights" at their
professional societies, even if it cost them dearly in development time,
effort, and user friendliness. And, I am quite certain that it provided
lucrative opportunities for some of those DotNetters.

 Larry

One of the advantages of web applications is that senior executives
and board members can see real-time, current reports of what's
happening, wherever they may be, provided that place has an internet
connection. My experience is that they like that and when they like
something, more funds for more similar applications are forthcoming.
As for time and costs, I've found dot.net to be like most technologies
I've learned. After a bit of time when I struggle, the "how to"
becomes second nature and things progress quite quickly. Dot.Net VB
has some niceties that we're not so familiar with in VBA, such as Try,
Catch.
I try to consider, when thinking about time of development
requirements, the notion that a well-crafted web dot.net application
almost always works, as long as we have an adequate internet
connection; there have been thousands of posts to this group that
might suggest that the smooth-running of an Access app is not such a
sure thing and that the app and its connections may require
modification from time to time.
Access 2007 has turned me off Access development; I guess I would
create an Access application if a client insisted, but only if the
money was good and I assuaged my conscience with a little presentation
about alternatives first.
MS owns Access and, IMO, MS has moved it along the line from a RAD
tool towards being an interactive tool. That's fine; it's their
product. But I don't see that it's an appropriate tool for me anymore.
What does this have to do with Intolerable Latency on a Wan? IMO, it's
very unlikely that a well designed dot.net/MS-Sql Server would suffer
from such problems.
(Regardless of the above, from time to time I still get enormously
frustrated by dot.net.)