Link to home
Start Free TrialLog in
Avatar of Tricia McKeating
Tricia McKeatingFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Moving a MS Access Database to the Cloud

I am looking to move an Access Database, which has the backend and Frontend on a local file server, into the cloud so that staff can access while at home. Does anyone have any advice on the best solution for this ? Azure SQL Server AWS?  I would still like to maintain the frontend using Access. Just looking for a steer in the right direction so that I can hire in the correct experience.

Avatar of ste5an
ste5an
Flag of Germany image

It will work, but the user will experience latency issues. To reduce theses, you normally need to rewrite parts of your application.
The more common way is to host an RDP server and let your users run the application on that server.

To start your project, you need an budget and impact analysis before deciding the direction to go.
I'm not sure what the best solution is but I have a client that uses Windows Terminal Server to run the Access front end (with SQL Server back end) in a distributed solution of over 70 sites.  It seems to work quite well although Terminal Server takes a while to start up and periodically 'forgets' users' Office credentials so they are having to re-log in to Office intermittently when starting the app.
Either RDP that's easy and straightforward or BE using a cloud aware server like all the usual SQL engines (MSSQL,MySQL,PostGre,Oracle) but needs some extra work to make it efficient.
One option is to move the full setup to a hosted (in Azure or AWS) Windows Terminal Server.
That method requires zero changes to your Access files, only the setup of the server and the costs for the licenses (server and CALs).
I use RemoteAppHosting.net
818-995-4634
I moved a system with 5 users onto this platform. Cost per user is 25 per month
Each user is located in a different state.
Both frontend and backend are MSAccess. Each user has their own setup and copy of frontend software.
The service is located in California. Works perfectly (up over a year) and speed is fine.
Highly recommend. Setup time including uploading software and data took one hour! I couldn't believe how easy it was.

<<That method requires zero changes to your Access files, only the setup of the server and the costs for the licenses (server and CALs). >>

 That may not be true if the App is not already running on a TS/RDP setup.   Many apps will have issues because:

1. Everyone is running on the C drive
2. Everyone is sharing printers.

 Nothing that can't be handled of course if you have access to the source, but moving to TS/RDP is not always a slam dunk.

Jim.
I'm not sure what those issue could be, Jim.
User profiles are isolated, users run separate frontends, and printers may, of course, be shared, but most often redirected printers are used.

The modern setup of Terminal Server and it many services with Windows Server 2012R2-2022 is not trivial, but once, when done, everything run smoothly. We haven't seen a single issue.
gustav,

<<User profiles are isolated, users run separate frontends, and printers may, of course, be shared, but most often redirected printers are used. >>

 While that is all true, like I said, it's not always a slam dunk and an app may need some adjustments.

 For example, some apps will place things in C:\Temp using that as a scratch work area.    With printers, a report may be spooled to disk and then some additional processing takes place, like converting to PDF, or copying the printer file to a remote system for printing. Then there are references.   Even with separate front ends, references  can end up getting shared.  99% of the time, that's not an issue, but it can be.

 I've run into things like this quite often.   Yes, there are some apps that just drop right in, but then there are times when they do not.

Jim.


Avatar of Tricia McKeating

ASKER

I have just over 100 schools 80 of the schools will have 2 or 3 users max and the other 20 being High schools will have approx 100 users in each. Each school would need its own backend and connection. Cost if 25 per month per user would be too much. Lots for me to look into though.
Then its time for work
Rent a decent VPS (e.g. 25€+VAT/mo will provide quad core CPU/8Gb memory/160 Gb SSD 25TB bandwidth) ...install free MySQL server and just Access FE +MySQL BE...
Since you have quite a few users (although I doubt if you have total than 50 simultaneously active) probably some serious work would be needed to make it swift...but in the end you will have Cloud Access 
I have just over 100 schools 80 of the schools will have 2 or 3 users max and the other 20 being High schools will have approx 100 users in each.
This makes 80 * 3 + 20 * 100 = 2,240 users. How big is your annual administration budget? How big is the budget for going cloud?

I am looking to move an Access Database, which has the backend and Frontend on a local file server, into the cloud so that staff can access while at home.
Assuming that you need to minify licenses costs at all will, this means you need to do some change in architecture.

Thus I would look into replication. Especially using an Azure SQL Managed Instance as publisher and SQL Server Express as local database. This would eliminate latency and connectivity issues.

E.g. Transactional replication with Azure SQL Managed Instance

I've been running small applications in on Azure SQL S0 with up to 10 users for specialized tasks. The latency was there, but tolerable. I cannot tell what kind of database tier would be needed for 100 users.

So I would guess, the 80 percent of affected entities would work, but the least 20 makes changes of the one or other kind in your application necessary.
I am looking to move an Access Database, which has the backend and Frontend on a local file server
So a few more comments so you understand the situation.

"Access" is really three things; the Access UI and objects (Queries, Forms, Reports, etc), VBA for coding, and the ACE (aka JET) database engine.

 The ACE/JET database engine (.accdb, .mdb) was originally designed as a department level engine meant to be used over a LAN.  There is no server side process with it.    Each client that touches a shared BE actually carries out its own database processing.   The backend on the server is simply a shared file.

While people have stretched it further as network speeds have increased, running it over any type of WAN is problematic because of this design.  ACE/JET needs 100mb/sec at the very least, but more importantly wants latency of <30ms.   Even running it on wireless connection on a LAN can cause issues.  With no server side process, a client disconnecting abnormally can cause the database to corrupt, or be in an inconsistent state.

But with Access, the database portion can be swapped out, and you can use something like mySQL or SQL Server as a backend.   That moves you to true client / server processing as there is a server side process that handles all the database processing.   That means if a client disconnects abnormally, the database is now protected.

That type of setup will work over a WAN, and you could put the BE in the cloud.    However most Access apps are never written with true client / server in mind.   They can be, but most are not.  For example, with true client /server, you'd never think of binding a form directly to a table and leaving it at that.  

For client / server to work well, everything needs to be pushed server side as much as possible.   Access can make use of pass-through queries, views, and stored procedures, but doing so is a totally different style of development, and not what your typical Access development looks like.

With all that said, I would assume these schools are using Access as a teaching tool?   or are they actually making us of developed apps for school functions?

Depending on the answer to that, there may be some other alternatives (such as using a local server as a back end and the runtime rather than the full product).

Jim.
Thanks for all this. I have had a few inconsistent state messages over the years. The schools use the Access database I have developed as a tracking and monitoring tool so at reporting periods there could be easily 100+ simultaneous users. I was really just trying to get a steer on what expert I would need to help try and move this web based without having to re-design the whole thing. It does have a fair amount of VB within it loads of forms queries and reports. I mean it works so why fix it...well COVID meant that they could not use the database as it was in school servers. School management are now looking for a web based solution that can be used from home. As usual with Education there is never any money available...budgets are tight. So I think I need a guru in SQL server as a starting point.

ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial