To move or not to move to a Terminal Server

[doyle60]

I have a split database that is used by many people in three different
states. Users occassionally get errors, all sorts of errors, such as
"Disk error" or any such thing. The usual thing to do is just to get
them to hit "End" and get out of one of the main forms and just go
back in. A user who works on it all day could get anywhere between 0
and 5 such errors a day.

I asked my tech to spell out our situation, and who also has a
question himself. Here is what he writes:
_____________________________________

We have an Access 97 database split in a front end and a back end.
Generally, the front end sits on a user's PC and the back end sits on
a server.
There are some users on a Terminal Server and each has a copy of the
front end in their profile.
The Terminal Server is a Windows 2003 R2 Enterprise Ed. Dual Xeon 5160
@ 3 Ghz with 8GB RAM
Both servers are on the same Gb switch.

We are wondering if we should move the back end to the Terminal Server
so that the RDP Users would have both front end and back end on the
same server.
For the "local users" (ones that have Access running on their PC) the
back end would still be on a remote server.
Is there a possible issue with having the back end on a Terminal
Server as opposed to a file server?

The Terminal Server is set to enforce the following user session
limits. "Idle session limit" is 1 hour. "End a disconnected session"
is set to 2 hours.
This was done in an attempt to keep people off Access if they are not
actively using and get them in the habit of closing Access properly.
Of course some users still "forget." Could enforced session limits
cause issues with Access? Is that a graceful termination of Access?

It is our impression that Terminal Server users experience more errors
in Access than "local users".
I would say that on average there are 4 RDP sessions in our location
and 6 RDP sessions from the remote location.
At the same time, there would be about 15-20 local users so the total
concurrent users would average 25-30 people.
___________________________________

Here are some stats:

Database: 1997
The number of users: 30
The number of users on remote: 15
The number of users in different US States on remote: 10 remote and 5
local Terminal Server users
The size of the database: 329 MB (backend), front end varies from 58
MB to a couple hundred with use.
Individual Computer ages: Age varies but some are on W2K and some XP
(anywhere from P4 to Core 2 Duo with over 1GB RAM, Terminal Server is
Windows 2003 R2 Enterprise Ed Dual Xeon 5160 @ 3 Ghz with 8GB RAM
Main Table Header Records: 41,000
Main Table Detail Records: 200,000
Number of Years of Data: 10.

There are more large tables, of course, but those are the biggest and
most active and important. I could delete or move out 5 years of data
which would probably by 40% of the number of records. Should I?

Thanks,

Matt

 

[Albert D. Kallal]

The assumptions you are making are generally correct there's only a few
things I will add here:

Access 97 is not particularly good for releasing the processor and "idle
time" correctly. Access 97 is 10+ years old now and it is one of those
versions that was caught up in the transition between 16 bit computers and
32 bit computers. when running access 97 on a user's desktop, the fact that
it's a little worse and releasing the title processor time is not a big
deal, but when you're running multiple users on the same box such as
terminal services, and this can become a significant issue. I would suggest
that you use access 2003, or even the access 2003 run time.

The other issue that is significantly important is that even when you run
the system on terminal services, each individual user needs to get their own
copy of the individual front end. for some reason often when moving to
terminal services because everybody's theoretically to on the same computer,
this rule is changed, and access does not take well to what we call an
industry reentrant code.

so in terms of eliminating network problems, data corruptions, and a host of
a number of the issues, terminal services is gently of very good solution to
these type of problems and you'll usually get better performance as a bonus.

You likely can continue using access 97, but just remember it's going to run
the server processor a at little bit of a higher temperature range because
it does not "idle" the processor very well at all. This is also the case
when using access 97 on laptop, you find your legs getting very hot because
a97 doesn't always idle the processor.

There are other possible solutions to this scenario, such as moving the the
database to SQL server, but in terms of the least amount, the least amount
of reprogramming and least amount of changing your architecture, clearly
terminal services is you best choice especially since you're running an
already in you have it in house expertise.

using TS for all users means you'll never have to install or update
ms-access on the client side computers....

 

[Albert D. Kallal]

The assumptions you are making are generally correct there's only a few
things I will add here:

Access 97 is not particularly good for releasing the processor and "idle
time" correctly. Access 97 is 10+ years old now and it is one of those
versions that was caught up in the transition between 16 bit computers and
32 bit computers. when running access 97 on a user's desktop, the fact that
it's a little worse and releasing the title processor time is not a big
deal, but when you're running multiple users on the same box such as
terminal services, and this can become a significant issue. I would suggest
that you use access 2003, or even the access 2003 run time.

The other issue that is significantly important is that even when you run
the system on terminal services, each individual user needs to get their own
copy of the individual front end. for some reason often when moving to
terminal services because everybody's theoretically to on the same computer,
this rule is changed, and access does not take well to what we call an
industry reentrant code.

so in terms of eliminating network problems, data corruptions, and a host of
a number of the issues, terminal services is gently of very good solution to
these type of problems and you'll usually get better performance as a bonus.

You likely can continue using access 97, but just remember it's going to run
the server processor a at little bit of a higher temperature range because
it does not "idle" the processor very well at all. This is also the case
when using access 97 on laptop, you find your legs getting very hot because
a97 doesn't always idle the processor.

There are other possible solutions to this scenario, such as moving the the
database to SQL server, but in terms of the least amount, the least amount
of reprogramming and least amount of changing your architecture, clearly
terminal services is you best choice especially since you're running an
already in you have it in house expertise.

using TS for all users means you'll never have to install or update
ms-access on the client side computers....

 

[Albert D. Kallal]

as an addendum to the above post of mine, I have an article on this whole
issue of using wide area networks with MS access, and you might want to
print this out and let your IT people take a read of this article of mine:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[Albert D. Kallal]

as an addendum to the above post of mine, I have an article on this whole
issue of using wide area networks with MS access, and you might want to
print this out and let your IT people take a read of this article of mine:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[Tony Toews [MVP]]

as long as users have reasonable link speeds
performance should be fine.

While TS is sluggish on a 28.8 kpbs modem connection it does work. So
any kind of higher speed line, so long as it's not saturated with
traffic should work well. Unless it's a satellite connection.

Tony

 

[Tony Toews [MVP]]

as long as users have reasonable link speeds
performance should be fine.

While TS is sluggish on a 28.8 kpbs modem connection it does work. So
any kind of higher speed line, so long as it's not saturated with
traffic should work well. Unless it's a satellite connection.

Tony

 

[Tony Toews [MVP]]

I have a split database that is used by many people in three different

The Terminal Server is set to enforce the following user session
limits. "Idle session limit" is 1 hour. "End a disconnected session"
is set to 2 hours.
This was done in an attempt to keep people off Access if they are not
actively using and get them in the habit of closing Access properly.
Of course some users still "forget." Could enforced session limits
cause issues with Access? Is that a graceful termination of Access?

It's very likely usually a graceful shutdown similar to hitting the
tiny x aka close button on the upper right hand corner of Access.
That said I'd shut down Access from within the app after a half hour
of inactivity.

HOW TO: Detect User Idle Time or Inactivity in Access 2000 (Q210297)
http://support.microsoft.com/?kbid=210297
ACC: How to Detect User Idle Time or Inactivity (Q128814)
http://support.microsoft.com/?kbid=128814

However we found that the code which runs on the timer event must be
disabled for the programmers. Otherwise weird things start happening
when you're editing code.

Also print preview would sometimes not allow the users to run a menu
item to export the report to Excel or others. So you had to right
click on the Previewed report to get some type of internal focus back
on the report so they could then export it. This was also helped by
extending the timer to five minutes.

The downside to extending the timer to five minutes was if a person
stays in the same form and at the same control for considerable parts
of the day, ie someone doing the same inquiries, the routine didn't
realize that they had actually done something. I'll be putting in
some logic sometime to reset this timer whenever they do something in
the program.

There are more large tables, of course, but those are the biggest and
most active and important. I could delete or move out 5 years of data
which would probably by 40% of the number of records. Should I?

No, don't bother. Performance won't be a lot different for 100K or
200K records.

Tony

 

[Tony Toews [MVP]]

I have a split database that is used by many people in three different

The Terminal Server is set to enforce the following user session
limits. "Idle session limit" is 1 hour. "End a disconnected session"
is set to 2 hours.
This was done in an attempt to keep people off Access if they are not
actively using and get them in the habit of closing Access properly.
Of course some users still "forget." Could enforced session limits
cause issues with Access? Is that a graceful termination of Access?

It's very likely usually a graceful shutdown similar to hitting the
tiny x aka close button on the upper right hand corner of Access.
That said I'd shut down Access from within the app after a half hour
of inactivity.

HOW TO: Detect User Idle Time or Inactivity in Access 2000 (Q210297)
http://support.microsoft.com/?kbid=210297
ACC: How to Detect User Idle Time or Inactivity (Q128814)
http://support.microsoft.com/?kbid=128814

However we found that the code which runs on the timer event must be
disabled for the programmers. Otherwise weird things start happening
when you're editing code.

Also print preview would sometimes not allow the users to run a menu
item to export the report to Excel or others. So you had to right
click on the Previewed report to get some type of internal focus back
on the report so they could then export it. This was also helped by
extending the timer to five minutes.

The downside to extending the timer to five minutes was if a person
stays in the same form and at the same control for considerable parts
of the day, ie someone doing the same inquiries, the routine didn't
realize that they had actually done something. I'll be putting in
some logic sometime to reset this timer whenever they do something in
the program.

There are more large tables, of course, but those are the biggest and
most active and important. I could delete or move out 5 years of data
which would probably by 40% of the number of records. Should I?

No, don't bother. Performance won't be a lot different for 100K or
200K records.

Tony

 

[Tony Toews [MVP]]

We have an Access 97 database split in a front end and a back end.

As Albert states A97 doesn't play well with TS. I'd suggest updating
to Access 2003 at least. I'm surprised you haven't noticed too many
oddities with it.

Note that the A2007 runtime is free and could be installed on the TS.
There are some quirks of course.

Tony

 

[Tony Toews [MVP]]

We have an Access 97 database split in a front end and a back end.

As Albert states A97 doesn't play well with TS. I'd suggest updating
to Access 2003 at least. I'm surprised you haven't noticed too many
oddities with it.

Note that the A2007 runtime is free and could be installed on the TS.
There are some quirks of course.

Tony

 

[David W. Fenton]

While TS is sluggish on a 28.8 kpbs modem connection it does work.
So any kind of higher speed line, so long as it's not saturated
with traffic should work well. Unless it's a satellite
connection.

Isn't dialup one of the things that is greatly improved with the
Citrix extensions?

 

[David W. Fenton]

While TS is sluggish on a 28.8 kpbs modem connection it does work.
So any kind of higher speed line, so long as it's not saturated
with traffic should work well. Unless it's a satellite
connection.

Isn't dialup one of the things that is greatly improved with the
Citrix extensions?

 

[David W. Fenton]

It's very likely usually a graceful shutdown similar to hitting
the tiny x aka close button on the upper right hand corner of
Access. That said I'd shut down Access from within the app after a
half hour of inactivity.

Or, alternatively, you could have your forms periodically save data
if they are dirty. It's only a dirty form that could possibly cause
problems with an OS forced quit (i.e., when the session terminates).

 

[David W. Fenton]

It's very likely usually a graceful shutdown similar to hitting
the tiny x aka close button on the upper right hand corner of
Access. That said I'd shut down Access from within the app after a
half hour of inactivity.

Or, alternatively, you could have your forms periodically save data
if they are dirty. It's only a dirty form that could possibly cause
problems with an OS forced quit (i.e., when the session terminates).

 

[Tony Toews [MVP]]

Isn't dialup one of the things that is greatly improved with the
Citrix extensions?

Don't recall. I never had any reason to test that. I fired the
client running Citrix. <smile> Turns out the person who I was having
difficulties with was later escorted out by security guards. hehehe

Tony

 

[Tony Toews [MVP]]

Isn't dialup one of the things that is greatly improved with the
Citrix extensions?

Don't recall. I never had any reason to test that. I fired the
client running Citrix. <smile> Turns out the person who I was having
difficulties with was later escorted out by security guards. hehehe

Tony

 

[Tony Toews [MVP]]

Or, alternatively, you could have your forms periodically save data
if they are dirty. It's only a dirty form that could possibly cause
problems with an OS forced quit (i.e., when the session terminates).

So long as the OS is shutting down the app I don't see a problem
either way. Either the unsaved data will get saved or it won't.

Now if the server is powered off using the power switch then yes that
could be a more severe problem.

Tony

 

[Tony Toews [MVP]]

Or, alternatively, you could have your forms periodically save data
if they are dirty. It's only a dirty form that could possibly cause
problems with an OS forced quit (i.e., when the session terminates).

So long as the OS is shutting down the app I don't see a problem
either way. Either the unsaved data will get saved or it won't.

Now if the server is powered off using the power switch then yes that
could be a more severe problem.

Tony

 

[doyle60]

Thanks for all the replies. My tech is reviewing and may have a few
follow up questions.

Matt