Access database on a WAN

[Aneta]

Hello,
Here's the situation:
I have an Access front end application with back end on MS SQL 2000
server. We have roughly 20 users using the database on a daily basis,
2 of which are located in our European office and possibly more remote
users in the near future. The database works fine for us, but our
European team is experiencing really slow performance and delays (so
50 times slower). They are connected to us via the WAN (Wide Area
Network). I was told that the link between our office and the European
one is private and should not be saturated since only our traffic
traverses the link. Supposedly the issue is latency which is a
function of distance. It takes at a minimum 115 milliseconds for a
round trip between Detroit (us) and Amsterdam (them), whereas between
computers within headquarters facility it takes only one to two
milliseconds.

So far I have made some changes to the database which did improve the
performance, but still it is far from being what we hope for. So, the
next thing I had in mind was to archive some records, but the tables
aren't so big (100k tops) and I doubt this will help that much. So, I
did some more brain storming and here are my ideas:


- Use Thin Client technology - Set up Windows Server 2003 with
Terminal Services here locally, and have European team/remote users
log in and use it to work with the database. I assume this would
reduce the amount of data that would be transferred between the client
and server and thus speed up the whole process. What are the
advantages and disadvantages of this approach?
- Set up Second SQL Server in Europe to store exact replica of our
database and perform synchronization/replication on a daily basis. I
don't even know if that's even possible if the data will be updated on
both sides at the same time. If yes, can please someone explain of how
this can be done and how difficult this is. What are the drawbacks?
Please keep in mind that I'm not the SQL server DBA and I know very
little about SQL server.
- Create Web Application, which probably is the best solution for the
time being is rather out of question due to time constraints and
limited resources

Any help, suggestions, examples, or other ideas would be greatly
appreciated. Thank you in advance for any and all of your help.

Aneta

 

[microb0x]

Couple things you can try or I can suggest here. I have a similar
instance at my work.

1. Do you have a citrix environment setup at your work? This proves
to be a great solution, atleast at my place of work. I was able to
rollout an access application with a user base of 2000+ spread across
5 countries. The response times were not quite as fast as the local
users but were acceptable for use. It was a VAST improvement over a
wan connection.

2. Terminal Server would also be a solution to explore. The only
drawback here, as well as with a citrix solution, that I'm aware of
anyways, is the administrative overhead of getting everyone setup for
citrix and/or terminal server. But that is more of just a delay in
rolling this out as opposed to being a drawback.

3. A replica of the database in Europe would most likely be the
solution with the best response times within the application. While I
have a basic knowledge of setting this up you would be better off
working with your dba who maintains that sql server.

Hope this helps!

-microb0x

 

[Aneta]

Thank you for your response. It's good to know that I'm not the only
one dealing with a situation like that.
No, I don't have anything set up just jet. I'm just doing my homework
for now and getting ideas of what's the best solution to use.
I'm not familiar with Citrix. what is is and how different is it from
TS? If possible, could you please give me more info?
As far as the SQL DBA, I try as much as possible to limit my
interactions with her, as she's not the easiest person to deal with
and doesn't want to help at all (she's a pain in the a.. in other
words . But I may have to get her to cooperate if I want this thing
done right.

Thank you once angain and I'm looking forward to some other great
ideas.
Aneta

 

[Jerry Whittle]

Are you using Pass Through queries? If not search Help for "Create an
SQL-specific query". A pass through query runs on the server and not on the
PC. That way it's usually faster plus returns only the data needed.

Also try opening forms and queries with a limited number of records using a
Where clause. If you open a form attached to 100000 records miles away, it
can take a while. However if you ask for the last 1000 by using the Top
statement with records sorted by date in descending order, it might really
speed things up.

 

[microb0x]

Citrix and Terminal Server are pretty similar in some aspects. Citrix
would be the better of the two solutions in my opinion.

You can log in to citrix similar to like logging in to a terminal
server session. A citrix environment is usually setup with a cluster
of servers, meaning it can load balance all your users, rather than
having everyone logging in to a single server with terminal sessions.
Terminal Server is usually used when you only need a couple admins to
access a server, or similar situation. Citrix is basically a virtual
desktop, and works well to deploy something to multiple users

Citrix also provides better line compression than term. serv., so the
response times would be better.

I'm keeping this at a high level, going in to greater detail in the
way citrix works isnt really a discussion for this group.

Bottom line, Citrix would be the better solution to Terminal Server,
but could also prove costly to get an environment up and running if
your company doesnt already have one in place.

A replica of the SQL server is the ideal solution for you.

 

[Albert D. Kallal]

I was told that the link between our office and the European

one is private and should not be saturated since only our traffic
traverses the link.

Who cares about saturation? What about with one user? The
issue here is not saturation, but simply the speed of the network.

Maybe you should ask them to give you a number, and have
them tell you what is the speed difference between your
standard lan, and that wan?

Note, we don't care about bits, bytes, mega bits etc.

We want a plain Jane number. That WAN is 30 times
slower then the LAN (or 10 times...or whatever).
Real super simple question, but
as always, am absolute STUNNED that such a simple
question usually can't be answered...

I mention this problem of asking about speed here:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

So, get the speed of wan, but MORE IMPORTANTLY get
a super simple number: how many times slower is this
WAN then the LAN? Don't ask about bandwidth, bytes,
bits, tc/ip...get that SUPER SIMPLE number!!!

Supposedly the issue is latency which is a
function of distance. It takes at a minimum 115 milliseconds for a
round trip between Detroit (us) and Amsterdam (them), whereas between
computers within headquarters facility it takes only one to two
milliseconds.

So, the difference in latency is 113 milliseconds.....

That is still only 1/10th of a second. surly your users are not complaining
about a delay of 115 milliseconds...are they???

- Use Thin Client technology - Set up Windows Server 2003 with
Terminal Services here locally, and have European team/remote users
log in and use it to work with the database. I assume this would
reduce the amount of data that would be transferred between the client
and server and thus speed up the whole process. What are the
advantages and disadvantages of this approach?

The above is most certainly an excellent approach. the advantage of the
above is that all your software, and support, and deployment of the
software is in ONE place. You don't have to setup anything in
Europe, except a secure connection, and you likely have that
working already. In Europe, the only thing they will need setup
is the "client" software...and that is built into windows xp...so,
only setup need be done...and only done once.

So, TS is a ideal system, as no software updates or support need
occur in Europe. Everything stays in one place. In fact the end
users don't even need ms-access installed on their machines (again,
versions....setup...patches....all is eliminated).

What are some disadvantages? Well, for one, you need a good
server, you have to setup TS, and purchase some CAL
(client access licenses). Further, the main drawback is that
your appcation can't integrate with word, or outlook on THEIR
local machines. So, if you have a lot of outlook, or email features
in your software, this will be a problem. I explain this problem
in detail here:

http://www.members.shaw.ca/AlbertKallal/Articles/ThinClientsand.net.html

You can solve some of the problems by setting up outlook to run on the
TS box..but, it is one weak spot because you can't use the local copy
of word, or outlook when you use remote software such as TS.

- Set up Second SQL Server in Europe to store exact replica of our
database and perform synchronization/replication on a daily basis.

You can do the above, but now you have two servers to support, and
someone has to maintain that remote database system. Certainly doable,
but I think TS is far more preferable, and will also perform better anyway.
I suppose this depends on how many changes, or modifications occur
to your appcation. You might run the same version of word for a few
years, but if your application is being updated monthly, or more often,
then issues of deployment..and updating users desktop software becomes
a big issue..one that you must plan for.

- Create Web Application, which probably is the best solution for the
time being is rather out of question due to time constraints and
limited resources

Sure....but, do note that TS will likely perform as well, or even better
in the case with a rich interface. And, the beauty of TS is that you
don't have to re-write one line of code (however, your forms should
be free of kindergarten like colors and graphics that so many
amateur developers seem to like. So, you want nice clean forms
with silly background graphics. This clean screens work much
better for TS.

Do note that Citrix is simply a different commercial version of Terminal
Services. The main differences is that Citrx can be "tuned" more, and has
some better features, but for the most part...you don't have a lot of remote
users..and thus I would not worry.

Note that Microsoft actually purchased the Terminal Services technology from
citrix. They are really much the same technology...

 

[Tony Toews [MVP]]

You can log in to citrix similar to like logging in to a terminal
server session. A citrix environment is usually setup with a cluster
of servers, meaning it can load balance all your users, rather than
having everyone logging in to a single server with terminal sessions.
Terminal Server is usually used when you only need a couple admins to
access a server, or similar situation.

A client is happily using TS, no Citrix, for 25 remote users.

Citrix also provides better line compression than term. serv., so the
response times would be better.

Not necessarily. The difference between Citrix and TS may be so
minute that we can't really see it. Also the latency between
continents is likely to be the biggest performance bottleneck.

A replica of the SQL server is the ideal solution for you.

SQL Server replication may be a lot more trouble than it's worth
especially if she has a cranky DBA to deal with.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Aneta]

Thank you all for such a wonderful response and so much great
information. I really appreciate this more than I can express it.
I think I have my mind pretty much made up and I think I'll be going
with the Terminal Services. I'm currently checking with our IT guys to
see if we possibly have something like this already in place and is so
if we can utilize it for our purposes. This would probably be the best
because I wouldn't have to purchase anything and could have them set
it up in no time (I hope). But then I could learn much more if I did
it myself...
Anyway, I think I'm onto something (finally) and I can see a light at
the end of the tunnel.
Thank you all once again.
Sincerely,
Aneta

 

[Aneta]

As I'm reading through your responses, there are a couple more
questions that came to my mind.
1. I'm pretty certain that I'll be using the TS and not second SQL
server, but I'm very interested in the concept of having two instances
of identical dabases that are simultaneusly updated on both ends and
having them synchronized/replicated on a daily basis. Can anyone delve
into this idea some more? I'm very interested in how this can be done
and what about the data integrity? What about Primary key's and
foreing keys when new records are added/changed? I've read about
different types of replications on Microsoft website (I think merge
replication would work in this case), but I think I'd understand it
better in a plain language.
2. I haven't really experimented with Pass Through queries, but I
think I may have to. Most of the queries that we do have are very
complex, nested queries and I don't know how easy it would be to
recreate them to use them as Pass through queries. Are Pass Through
queries in Access basically the same as views on SQL server, or am I
far off with this?
3. Also, most of the forms are bound sinle forms opening using the
where criteria. Would it be better if I rewrote them to be unbound, or
it doesn't really matter that much?
Thank you once again for sharing your wisdom and expertise with me
(and all of us trying to learn something new).
Aneta

 

[Larry Linson]

The key to performance in the client-server environment is to minimize the
amount of data that must be passed across the network. I worked on a
database application that had an Access 2.0 client to an Informix server
backend. Three remote locations were each served by a dedicated T-1 line
(approx. 1.5 MBPS) for fewer than a dozen users at each location, fifty or
fewer users on a LAN at 100MBPS, and the rest of 275 users at locations on a
corporate WAN with varying speeds. Performance was, according to feeback
given by the users to the sponsor of the application, "acceptable". It had
NOT been considered acceptable by the users when the remote users were
sharing a 256KB leased line, prior to the installation of the T-1.

No Form should have a RecordSource that opens a complete, large table and
then locates, or filters, locally to find the exact Record. It is surprising
how often the number of Records needed for a business function is either
one, if it exists, or none, if the record doesn't already exist.

I would suggest that your first step be examining the design of your
application -- that might identify places where minor redesign and
reimplementation of some functions would improve performance to an
acceptable level without having to install or set up a new software
configuration. In the application I describe, Jet and ODBC drivers did the
querying (efficiently, perhaps surprisingly so) and the only stored
procedures were for the purpose of returning the next unique key for
particular tables... three or four passthrough queries to access those
stored procedures in a database with around a thousand objects.

Stored procedures can be very useful, but they aren't the only way to gain
performance. We used (un-updateable) Views to force some retrieval functions
to be done on the server for reports... ours were much simpler than typical
stored procedures. Terminal server and Citrix can be useful options, too,
but, again, they are not the only way.

Larry Linson
Microsoft Access MVP

 

[Tony Toews [MVP]]

1. I'm pretty certain that I'll be using the TS and not second SQL
server, but I'm very interested in the concept of having two instances
of identical dabases that are simultaneusly updated on both ends and
having them synchronized/replicated on a daily basis. Can anyone delve
into this idea some more? I'm very interested in how this can be done
and what about the data integrity? What about Primary key's and
foreing keys when new records are added/changed? I've read about
different types of replications on Microsoft website (I think merge
replication would work in this case), but I think I'd understand it
better in a plain language.

As far as I'm concerned replication is a huge PITA (Pain in the *ss).
Among other things it needs a bit of monitoring and such.

If you can avoid it, especially with an excellent solution such as TS
then do so.

2. I haven't really experimented with Pass Through queries, but I
think I may have to. Most of the queries that we do have are very
complex, nested queries and I don't know how easy it would be to
recreate them to use them as Pass through queries. Are Pass Through
queries in Access basically the same as views on SQL server, or am I
far off with this?

Yes, I think so. I never got too deeply into those as I never
completed a largish back conversion to SQL Server.

3. Also, most of the forms are bound sinle forms opening using the
where criteria. Would it be better if I rewrote them to be unbound, or
it doesn't really matter that much?

No, there is no need to convert forms to unbound. A few will argue
the point and I'll argue right back at them. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[James A. Fortune]

As far as I'm concerned replication is a huge PITA (Pain in the *ss).
Among other things it needs a bit of monitoring and such.

If you can avoid it, especially with an excellent solution such as TS
then do so.




Yes, I think so. I never got too deeply into those as I never
completed a largish back conversion to SQL Server.




No, there is no need to convert forms to unbound. A few will argue
the point and I'll argue right back at them. <smile>

Tony

It's no wonder you've become so well aquainted with corruption issues.
<bigger smile>

Actually, with a WAN you have control over the number of users who
connect so it's much less of a problem than with a broader context. As
long as bound forms work well I say keep using them. If you're not
using TS, I suspect that long edit times with a WAN might cause
corruption problems. With TS the edit times should be fast enough not
to worry about bound forms at all because the edits happen right there.

James A. Fortune
(e-mail address removed)

 

[Tony Toews [MVP]]

It's no wonder you've become so well aquainted with corruption issues.
<bigger smile>

Were we having a disagreement about bound vs unbound forms and
corruptions? I recall having such a discussion but don't recall who.

The one client has had a total of five corruptions in five years.
Three of which we rapidly traced to faulty hardware. The client never
lost any data. And this was with a 25 users in all day long some via
Citrix and most locally on a LAN.

Actually, with a WAN you have control over the number of users who
connect so it's much less of a problem than with a broader context.

How do you have control over the number of users on a WAN?

If you're not
using TS, I suspect that long edit times with a WAN might cause
corruption problems.

Agreed. Although I'd call it latency and dropped packets. But to me
this has nothing to do with bound vs unbound forms.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[James A. Fortune]

Were we having a disagreement about bound vs unbound forms and
corruptions? I recall having such a discussion but don't recall who.

It might have been me. Don't you have a website that discusses causes
of corruption in Access and how to deal with it?

The one client has had a total of five corruptions in five years.
Three of which we rapidly traced to faulty hardware. The client never
lost any data. And this was with a 25 users in all day long some via
Citrix and most locally on a LAN.




How do you have control over the number of users on a WAN?

I was thinking of control over the number of users on a WAN compared to
a web interface that everyone on the internet is allowed to use. A site
where users log in is like the WAN situation regarding control of the
number of users.

Agreed. Although I'd call it latency and dropped packets. But to me
this has nothing to do with bound vs unbound forms.

I believe that it does. Although my experience is mostly with older
versions of Access (most users here have either A97 or A2K3) the time
that an edit takes over the network is related to how long records are
locked. The lock time seems to be related to the probability of
corruption. Has your experience been different?

James A. Fortune
(e-mail address removed)

 

[Tony Toews [MVP]]

It might have been me. Don't you have a website that discusses causes
of corruption in Access and how to deal with it?

Ahhh, yes. Sure but the kernel of that information was started by
Frank Miller and many more additions I've gleaned from years of
following newsgroup posting on corruption.

I was thinking of control over the number of users on a WAN compared to
a web interface that everyone on the internet is allowed to use. A site
where users log in is like the WAN situation regarding control of the
number of users.

Ah, gotcha. I wasn't even thinking of a web interface.

I believe that it does. Although my experience is mostly with older
versions of Access (most users here have either A97 or A2K3) the time
that an edit takes over the network is related to how long records are
locked. The lock time seems to be related to the probability of
corruption. Has your experience been different?

From what I've seen when using a bound form the only update occurs
when you move off the record. That is in that brief moment of time
before the pencil disappears from the upper left hand corner. Thus
there is no difference between a bound and unbound form. You still
have the same data update going down the wire and acknowledgement
coming back.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[David W. Fenton]

Citrix and Terminal Server are pretty similar in some aspects.
Citrix would be the better of the two solutions in my opinion.

But you could easily test TS without having to purchase any
additional software.

And I don't know what's so superior about Citrix -- I've never used
it and have had plenty of success with just WTS.

 

[David W. Fenton]

As far as I'm concerned replication is a huge PITA (Pain in the
*ss). Among other things it needs a bit of monitoring and such.

SQL Server replication is rather different from Jet Replication. SQL
Server replication is basically designed for the main purpose of
expanding the load of a SQL Server database, so that many servers
can provide access to the same data. Jet Replication, on the other
hand, is mostly designed to allow editing of data in multiple
locations.

Thus, there are differences in the way things are handled.

SQL Server replication (insofar as I understand it, not having much
experience with it) does not have the problems that Jet Replication
does in terms of people being able to break it easily, but this is a
function of SQL Server being a server database, rather than a
file-server database. Jet Replication is most often broken by people
thoughtlessly copying replicas around to different locations.

Second, SQL Server replication is more "Internet-friendly" by virtue
of the same fact, i.e., it's a server database. Jet is just not
Internet-friendly.

The synergy between Jet and SQL Server is heterogenous replication,
where an Access MDB can be a merge subscriber to a SQL Server
replicated database. I've never been involved in this, but it
excites my interest, as it sounds *very* powerful. Given that the
team that developed Jet replication in large part moved over to the
SQL Server replication team, I think it's likely that there is a lot
in common (this is, in part, why a single conflict resolver wizard
is able to work for both Jet 4 and SQL Server).

Microsoft does some pretty incredible things that they never get any
credit for, and replication in both SQL Server and Jet is one of
them, i think. Too bad they lost the courage of their convictions
and didn't follow through on creating voluminous developer support
for these wonderful technologies.

I can see one argument for that approach, though: replication
involves complex concepts that your garden-variety user/developer is
unlikely to grasp. It took me *years* to get it right and I think of
myself as a smarter-than-average Access developer (rightly or
wrongly). Without the guidance of geniuses like Michael Kaplan, I
think I never would have gotten it right.

 

[James A. Fortune]

Ahhh, yes. Sure but the kernel of that information was started by
Frank Miller and many more additions I've gleaned from years of
following newsgroup posting on corruption.




Ah, gotcha. I wasn't even thinking of a web interface.




From what I've seen when using a bound form the only update occurs
when you move off the record. That is in that brief moment of time
before the pencil disappears from the upper left hand corner. Thus
there is no difference between a bound and unbound form. You still
have the same data update going down the wire and acknowledgement
coming back.

Tony

Tony,

Your comment has brought up some interesting questions. By our own
admission, we don't have lots of data points regarding corruption.
Since I can only surmise Access' internal workings perhaps you can help
me get some information so that we are not working from ignorance.

1) If I have a bound form open and someone else on the network makes a
change, temporarily locking the record, does the lock stay on until the
data in the BE is changed, or until the change gets propagated to all
the users with the bound form open?

2) Is there any performance overhead involved in propagating those changes?

3) With an unbound form, all the fields are updated at almost the same
time and I would guess that the update doesn't take much longer than a
bound form takes to update a single field. Does a bound form have an
inherent advantage that allows it to bring up the current data
(currently edited field) from the BE faster than an unbound form in
either getting an index or retrieving data or is data on the form the
only thing cached?

4) For, say, 10 fields on a bound form, does the form might keep the
record locked for nearly 10 times as long as an unbound form if all 10
fields are edited? Is there any locking during propagation that would
make this even worse for bound forms?

The older software's page locking would, of course, exacerbate chances
for corruption. The lock time seems to provide more opportunities for
something to go wrong. Access wasn't endowed with enough healing
ability to recover gracefully from things like dropped connections.
Note that I wasn't totally careful with these questions so they aren't
purely independent of each other. Perhaps the comparison is as simple
as you suggest. I appreciate hearing from anyone who knows the answers
to these questions.

James A. Fortune
(e-mail address removed)

 

[Tony Toews [MVP]]

Tony,

Whoops, lost track of this thread.

Your comment has brought up some interesting questions. By our own
admission, we don't have lots of data points regarding corruption.
Since I can only surmise Access' internal workings perhaps you can help
me get some information so that we are not working from ignorance.

Note that all my replies are best guesses based on my observations.

1) If I have a bound form open and someone else on the network makes a
change, temporarily locking the record, does the lock stay on until the
data in the BE is changed, or until the change gets propagated to all
the users with the bound form open?

Given that Jet uses phantom locks on the .LDB file to control locking
I would say the lock would be on until the data is updated. IOW when
the pencil on a bound form is updated to a right triable.

2) Is there any performance overhead involved in propagating those changes?

Again because this is controlled by Jet phantom locks on the .LDB file
no performance overhead.

That said Access will look for changed data on a continuous form and,
for example, display #delete in a records forms after a few seconds.

3) With an unbound form, all the fields are updated at almost the same
time and I would guess that the update doesn't take much longer than a
bound form takes to update a single field. Does a bound form have an
inherent advantage that allows it to bring up the current data
(currently edited field) from the BE faster than an unbound form in
either getting an index or retrieving data or is data on the form the
only thing cached?

I can't see how an unbound form would have much difference with a
bound form. The fetching and updating of data over the network would
be by far the biggest bottle neck.

4) For, say, 10 fields on a bound form, does the form might keep the
record locked for nearly 10 times as long as an unbound form if all 10
fields are edited?

Not at all. Access/Jet would only do the update to the backend MDB
as soon as it is commanded. By you doing a me.dirty = false, or
similar, closing the form or moving to another record, etc, etc.

Is there any locking during propagation that would
make this even worse for bound forms?

See above comments about phantom locks on .LDB files.

As far as I'm concerned corruption does not happen while a bound form
is open to a record that is being updated. Corruption happens during
that brief moment in time when you save a record through whatever
means you save it. Furthermore that bit that says an MDB is corrupted
is only updated at the beginning of Access/Jet doing an
update/insert/delete and updated again at the end of the
update/insert/delete.

I've personally witnessed three or five power failures at a client
with 20-25 users in the MDB. I'm sure some of those were editing
records at the time. And there was no corruption.

No, I think there was once. But a compact/repair fixed it. Which is
what happens most of the time anyhow.

The older software's page locking would, of course, exacerbate chances
for corruption. The lock time seems to provide more opportunities for
something to go wrong. Access wasn't endowed with enough healing
ability to recover gracefully from things like dropped connections.

Make that Jet but yes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm