how to share the access back-end to multiple location (1 Viewer)

ubi

New member
Local time
Today, 01:30
Joined
May 28, 2021
Messages
22
Hello!
currently we have a split FE BE access database on the main location while on the second location we are using windows remote/anydesk to connect to a virtual machine on the main location to use access_fe. More or less we are 10 users working on the database but sometimes the second location experience huge lags/delays (i think net overload).
So i was thinking about using an sql server/azure server to lower the net load and by so improving the latency, but i read on the net that it not always improve the experience and sometimes it can be even slower. So i was wondering what would be a good solution to connect ms access to multiple location
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 00:30
Joined
Sep 12, 2006
Messages
14,684
Having split data back ends makes it difficult.
You may need to combine all the back ends into one for reporting purposes. However, it's hard to do that because you are likely to find that unique references are no longer unique, because each database will set references independently. Managing a distributed database is not easy at all.

Distributed database - Wikipedia
 

ubi

New member
Local time
Today, 01:30
Joined
May 28, 2021
Messages
22
no, its just one single back end file currently
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 18:30
Joined
Feb 28, 2001
Messages
21,149
How many folks are physically local and using Local Area Networking? How many are using RDP connections? What kind of remote connection are you using for those RDP folks?

no, its just one single back end file currently

If it is currently a single back-end, it would be best if it stays that way. While it is CERTAINLY possible to have a database with multiple backends, their contents cannot overlap very well. For instance, if you have back-end A and back-end B, a table in B cannot have a relationship with a table in A and they CERTAINLY cannot impose relational integrity with each other. The most common cases of split back-ends that I have seen personally involve using one of the splits ONLY for log files or as a place for temporary processing of bulky input files that will not be so bulky once processed (i.e. lots of fields that WON'T be stored in the main DB).

A properly managed SQL server might lower the net load, but only if all of the FE queries are adjusted in a way to prevent having to download the whole table to the FE for processing. I.e. the queries cannot "get in the way " of SQL using its power to do something useful. That would surely eat bandwidth if you were careless on query setup. There are others here who are more familiar with how to do that, so I'll just suggest that if your shop goes that way, ask that question then.

If you can log in to the VM and run Performance Monitor (or even just Task Manager) to see where your load is going, that would be the first step. Those tools are capable of confirming network load issues. But here's the other part of that question. To run a single VM on a system is kind of "gilding the lily." How many other VMs are on that host? Because it might not only be network load, it might be VM Server load that is slowing you down. I can't tell you how to tell that, but perhaps one of your VM managers can comment.
 

ubi

New member
Local time
Today, 01:30
Joined
May 28, 2021
Messages
22
we are 5 on LAN and 5 on RDP/anydesk, and in both location our net speed is like this (we cant upgrade to fiber unfortunatly :C ):
1633271866885.png


yea im not planning to split the BE, and querys shouldn't be a big problem because we keep our database pretty light by archiving the old data and using only the "recent" data only to work so the tables are at the best 50k rows but querys will be revised for sure for extra performance.

About VMs we are trying to replace them with normal PC because indeed we saw better performance in handling access, still on both connection (to PC or to VM) we experience spikes in latency. I dont know how i could possibly monitor the net load properly to confirm that it is a net load
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 18:30
Joined
Feb 28, 2001
Messages
21,149
On the machine running Windows that you suspect is having a network load issue, right-click in the task bar. Start Task Manager. Switch to the Performance tab. From the list of things being shown, you should see CPU, Memory, one or more Disk devices (USB devices, if empty, somehow are counted as disks.... go figure), and your active Network, all listed vertically on the left. Pick Network.

You will have to know your network speed, but that performance display should tell you the network capacity (at the top of the graph) and the instantaneous load (per snapshot) will be shown at the bottom left of the chart. If you have a 100 Kbps network and see CONTINUOUS loads of over roughly 33 Kbps, you may have a network overload. Very brief high loads with longer periods of less than 16 Kbps sustained load would not be cause for alarm.

Because probabilities involve both logarithmic/exponential functions and some square roots, it is a real bear to compute the exact point of saturation, but 33% load is a point where there is a significant probability of high network collisions - which causes fairly heavy re-try traffic. Networks that experience sudden slow-downs "out of the blue" are often experiencing high collision rates - where two potential network partners attempt to send messages at the same time, not necessarily to the same receiver - because Ethernet is a shared multi-drop network.

The mechanism gets very complicated, but basically when that collision occurs, BOTH parties have to re-transmit their messages. There is an algorithm used by Ethernet drivers to make the two senders wait different times before a second transmission attempt but if the network is busy enough that more than just two senders are active, several collisions in a row can be expected before things finally settle down.
 

ubi

New member
Local time
Today, 01:30
Joined
May 28, 2021
Messages
22
sorry i forget to mention that on both networks there are lots of devices connected like 20+ between phones, pc, laptop so my fear is that if some device start to download or even worse upload then it takes up most of the bandwidth. So more than monitor the single device i should somehow monitor the whole network.
 

moke123

AWF VIP
Local time
Yesterday, 19:30
Joined
Jan 11, 2013
Messages
2,602
sorry i forget to mention that on both networks there are lots of devices connected like 20+ between phones, pc, laptop so my fear is that if some device start to download or even worse upload then it takes up most of the bandwidth. So more than monitor the single device i should somehow monitor the whole network.
21.83 down and 2.08 up seems awfully slow for all those connections.

I just have basic cable and get great numbers.

Screenshot 2021-10-03 133326.jpg
 

Users who are viewing this thread

Top Bottom