Database slow on terminal server running Access Runtime

[Clem Fandango]

Hi there,

I have done an enormous amount of testing of different scenarios, and my issue boils down to what could be making a database (not split) run slow on a terminal server running Access Runtime when the same database is running lightning fast on all local machines. Let me clarify a couple of things:

1. This is not my actual use-case scenario. The real database is a split database with BE on the terminal server and FE also on the terminal server, so that users can RDP into the TS (using individual FEs) to access the database. I have been doing this for years with much success, and some recent upgrade works by our external IT guys to the server has dramatically slowed it down. It is still very fast if I put the FE on a local machine on the network within the office, but as soon as anybody tries to use it from outside the office over a VPN it is prohibitively slow, which is why I came to the RDP solution a long time ago.

2. The reason why I have phrased the issue like this, is because I wanted to illustrate the simplest scenario that exhibits the problem (and where I believe the problem must lie). So if I take a version of my database that is not split and open it up with 1 user (me) on my local machine (or anybody else's local machine), it runs very fast. If I then take that very same file and put it on the locel c: drive of the terminal server and do the same thing (i.e. non-split, 1 user) and open it (in Runtime now because that's what the TS has on it), then it slows down dramatically. So there can be no question of the issue being due to something in the linking of the tables etc. etc.

3. I have tried many things, in concert with the external IT guys to work out the problem. It is somewhat intermittent, which makes it more frustrating and sometimes makes it momentarily look like something they did may have improved the situation, and then it reverts to the same slowness again. We have tried removing the virus checking, playing around with the ram and processor allocations etc., to no avail. I read somewhere that the issue could be to do with Windows opportunistic locking but the IT guys groaned and claimed they looked into it and it was a historical problem that doesn't apply any more on the modern servers these days.

4. Again to clarify, if I do split the database and link it to a BE on the server with the FE on a local machine, it is fast. I feel like i must be something to do with the performance of the Access Runtime processing on the TS, but the IT guys can't offer any suggestion. But I need to work out what the problem is on the server, because everyone needs to access by RDP (lots of remote works, and workers that travel a lot). As I say, it was a lot faster before the recent server upgrade.

I would massively appreciate any ideas about this. Thanks!

 

[AHeyne]

What does it look like when you use a Runtime locally on a client? Maybe it's the runtime in general?

Have you compared the patch level (Microsoft Access version/build) of your Access full version with that of the runtime used?

 

[Clem Fandango]

What does it look like when you use a Runtime locally on a client? Maybe it's the runtime in general?

Have you compared the patch level (Microsoft Access version/build) of your Access full version with that of the runtime used?

Thanks so much for your response. I haven't, mainly because we don't have a spare client and all of them have full Access on them, so I've been reluctant to muck around with them. But your suggestion makes me realise I need to get hold of a machine somehow to test that. I had thought of getting the IT guys to reinstall the Runtime on the server (also a palava because timing etc.), so I might have to push them to try that also. Cheers.

 

[Minty]

Trusted locations can have an impact on performance. Do the RDP machines think the database is in a trusted location?
Anti-virus is also a possible culprit, if the Terminal Server has it's own scans running, try excluding the folder where the database sits.

 

[Clem Fandango]

Trusted locations can have an impact on performance. Do the RDP machines think the database is in a trusted location?
Anti-virus is also a possible culprit, if the Terminal Server has it's own scans running, try excluding the folder where the database sits.

Yes we did try both those things in our testing, but thanks for your suggestions!

 

[The_Doc_Man]

You said your IT staff recently upgraded some things. I hate to say it, but to find this gremlin you might need to look at recent patches in detail to see if they tie back to a government Computer Vulnerability and Exposure (CVE) report or Microsoft Knowledge-Base (KB) report or other formal report of a discovered problem. Such articles usually give details of required changes. You want to look at any CVE or KB that has a general network effect or more specifically, effects on RDP and the Windows File Share protocol Server Message Block (SMB). Also, check for any new products that do various types of network monitoring.

When dealing with elusive reports such as this, the problem is usually some innocuous issue that affects network traffic by re-routing it. However, by reducing the problem to a monolithic DB, you removed issues such as "persistent connection" - because monolithic DBs are the embodiment of persistent connections - which in turn eliminates repeated network arbitration delays caused by having to open and close connections that weren't persistent. That leaves this as a driver problem or a network configuration problem or a really ugly permissions problem. And I can't believe it to be permissions because of the inherent persistence of monolithic DB connections.

In the slow-down case, you have an RDP connection to a DB. It doesn't matter if they are on the same machine or not, RDP creates two sessions. One of them is the session that "owns" the display, mouse, and keyboard - i.e. the "local" session. The other of them (the "remote" session) owns the DB file, the .ACCDB. But the question is, in which session is MSAccess.EXE running? Because from your description it SOUNDS like the "Access-to-DB" traffic ISN'T local to the DB session, which makes no sense.

With your IT group's assistance, I would also suggest determining the route being used from your RDB FE session to the "shared" DB. There are some simple CMD-prompt network tools like TRACEROUTE to see if you are routing everything all over creation. Also, PING can test timing of each step in a multi-hop route. You should not have a multi-hop route, but if you DO have one, something is dreadfully wrong. In any of my systems before I retired, we ALWAYS allowed use of the LOOPBACK connection (127.0.0.1) for things that were using a network connection from point A to point B on the same machine, even when they were partitioned virtual machines.

 

[Clem Fandango]

You said your IT staff recently upgraded some things. I hate to say it, but to find this gremlin you might need to look at recent patches in detail to see if they tie back to a government Computer Vulnerability and Exposure (CVE) report or Microsoft Knowledge-Base (KB) report or other formal report of a discovered problem. Such articles usually give details of required changes. You want to look at any CVE or KB that has a general network effect or more specifically, effects on RDP and the Windows File Share protocol Server Message Block (SMB). Also, check for any new products that do various types of network monitoring.

When dealing with elusive reports such as this, the problem is usually some innocuous issue that affects network traffic by re-routing it. However, by reducing the problem to a monolithic DB, you removed issues such as "persistent connection" - because monolithic DBs are the embodiment of persistent connections - which in turn eliminates repeated network arbitration delays caused by having to open and close connections that weren't persistent. That leaves this as a driver problem or a network configuration problem or a really ugly permissions problem. And I can't believe it to be permissions because of the inherent persistence of monolithic DB connections.

In the slow-down case, you have an RDP connection to a DB. It doesn't matter if they are on the same machine or not, RDP creates two sessions. One of them is the session that "owns" the display, mouse, and keyboard - i.e. the "local" session. The other of them (the "remote" session) owns the DB file, the .ACCDB. But the question is, in which session is MSAccess.EXE running? Because from your description it SOUNDS like the "Access-to-DB" traffic ISN'T local to the DB session, which makes no sense.

With your IT group's assistance, I would also suggest determining the route being used from your RDB FE session to the "shared" DB. There are some simple CMD-prompt network tools like TRACEROUTE to see if you are routing everything all over creation. Also, PING can test timing of each step in a multi-hop route. You should not have a multi-hop route, but if you DO have one, something is dreadfully wrong. In any of my systems before I retired, we ALWAYS allowed use of the LOOPBACK connection (127.0.0.1) for things that were using a network connection from point A to point B on the same machine, even when they were partitioned virtual machines.

Thanks heaps Doc Man, there is a lot to digest there. I will take it up with my IT guys and see what we can do. Very grateful to you for taking the time. cheers.

 

[Clem Fandango]

Thanks heaps Doc Man, there is a lot to digest there. I will take it up with my IT guys and see what we can do. Very grateful to you for taking the time. cheers.

PS. I perhaps should have clarified in my original post that the only access anyone has to the Terminal Server is by RDP (even inside the office on ethernet via the local network), and so even in the case where I have a single-file (no FE/BE) with only 1 user (me) on the TS it is still via RDP (albeit ethernet/local network). Do I understand correctly that in this case I do also have 2 RDP sessions, and MS Access may be running in the wrong one?

 

[Clem Fandango]

PS. I perhaps should have clarified in my original post that the only access anyone has to the Terminal Server is by RDP (even inside the office on ethernet via the local network), and so even in the case where I have a single-file (no FE/BE) with only 1 user (me) on the TS it is still via RDP (albeit ethernet/local network). Do I understand correctly that in this case I do also have 2 RDP sessions, and MS Access may be running in the wrong one?

And it still exhibits all the slowness like that.

 

[Clem Fandango]

And just to be super clear, I can RDP into my office local machine from home and run the "single file" db on it and it is super fast. If I RDP into the Terminal server from home and run a copy of the same file on it, I get the slowness.

 

[ComputerVersteher]

Is the DB slow from startup or does it slowdown when used? If so it may be caused by the OpenForm bug (accessforever.org/post/performance-issues-and-crashes-when-loading-forms-in-version-2405).

Is the performance of other DB's as expected?

Is the performance off other apps on TS as expected?

Was performance monitoring done?

What is slow? Everything or do some things work as fast as expected (e.g. queries, open forms, scrolling)? If the slowness differs, that may point to the problem.

Also please show full OS and Access version of Server and your test machine.

 

[The_Doc_Man]

Do I understand correctly that in this case I do also have 2 RDP sessions, and MS Access may be running in the wrong one?

Only if there is a configuration error of some kind. Which, IF it exists, is a good thing to find. (Because if found, it can be fixed.) I'm going by the behavior pattern, nothing else, so if there is another source of slowdowns, then Access could be running in the right place. My suggestion about "where Access is running" is something that your IT guys could check pretty quickly.

 

[CJ_London]

One other thought is memory allocation - how many users v how much memory? Perhaps IT changes have reduced the available memory? Perhaps more users?

 

[Clem Fandango]

Thanks guys everyone for all your help. Sorry, I've been totally overloaded last few days with project work, and even though this affects everyone in the company every day it had to take a back seat for a bit. You have all provided another great tranche of investigative work for me to follow up on, and I will get to it in the next few days (as I can get the attention of the IT guys also). If/ when I find anything I'll post here, or also if I can gather some more useful info to give you some more clues. Thanks so much again!