Access to Cloud migration

[AronG]

Hello everyone!

I am working for a small company and we have been discussing migrating our in-house Access based ERP solution to the cloud. What I am looking for is a professional to let us know if we are on the right track. I am no expert at Access or cloud tech (yet) so sorry if I make any mistakes. Thank you for any and all input!

0) Our front-end will still be MS Access distributed to all of the employees' computers.

1) We have a considerable amount of data (20+ years' worth) on a local server. We are looking to migrate this to a web-hosted MS SQL server. As I understand, Access to MS SQL server migration is relatively simple. We will have to pay a monthly fee for the hosting of the database.

2) We will link the each Front-End with the hosted database via ODBC.

3) We will have a trial period where we check how this connection functions. Naturally we are looking for a solution that stays performant and has no size limitations.

+1) We have been thinking about synchronizing the online SQL server to our own, local server for security and availability purposes. Is such a synchronization possible? Does the process take long? Does it slow down either party?

+2) I am assuming that this hosted MS SQL back-end will be able to be used by other applications as well. For example, we would use a tablet with a different front-end solution to communicate with and use the server from anywhere in the country. Is this assumption correct?

Finally, I would be extremely grateful if anyone with experience would be able to give me documentation or stuff to look out for. I will be looking at Google for everything that I don't understand .

 

[Gasman]

I cannot tell you the mechanics, but in my last place of work, we used to use these people. https://www.witsifa.co.uk/
That was an access DB that we used within a browser on their Terminal Server I believe?

By logging in, we got our DB with our data. I believe there were multiple client DBs stored there.

 

[Minty]

We do this type of work for clients all the time, using Azure based SQL Server (Not a managed instance).
If you don't use SQL server already as a back end you can find changes are required to make it work with your existing front end.

If you are looking to do this yourself, I would probably take the following route.

• Get SQL Server Express (it's free) and set up a test environment locally using the existing front end connected to the new SQL back end.
• Try it out - test everything, do not skimp on this bit.
• Once you are satisfied it is all working to your satisfaction, you can then get a trial Azure subscription, and get your data into the cloud.
• Try it out - test everything again, do not skimp on this bit.
• You will probably find a number of things run rather slowly and will need tweaking to take account of the difference in performance between Cloud and Locally based data sources. This can be simple or involved depending on the nature of your data and complexity of processing.
• Your assumption about other types of front end connection to the data is correct once it is in the Azure/Cloud Environment.

At this point or possibly beforehand work out the hosting costs and make sure it fits your budget requirements.
Because we can spread the cost across a number of databases hosting becomes very cheap. For a single database maybe not so.

Synchronisation is possible but quite involved and has an associated cost, depending on the type of replication/synchronisation your require.

 

[AronG]

We do this type of work for clients all the time, using Azure based SQL Server (Not a managed instance).
If you don't use SQL server already as a back end you can find changes are required to make it work with your existing front end.

If you are looking to do this yourself, I would probably take the following route.

• Get SQL Server Express (it's free) and set up a test environment locally using the existing front end connected to the new SQL back end.
• Try it out - test everything, do not skimp on this bit.
• Once you are satisfied it is all working to your satisfaction, you can then get a trial Azure subscription, and get your data into the cloud.
• Try it out - test everything again, do not skimp on this bit.
• You will probably find a number of things run rather slowly and will need tweaking to take account of the difference in performance between Cloud and Locally based data sources. This can be simple or involved depending on the nature of your data and complexity of processing.
• Your assumption about other types of front end connection to the data is correct once it is in the Azure/Cloud Environment.

At this point or possibly beforehand work out the hosting costs and make sure it fits your budget requirements.
Because we can spread the cost across a number of databases hosting becomes very cheap. For a single database maybe not so.

Synchronisation is possible but quite involved and has an associated cost, depending on the type of replication/synchronisation your require.

Thank you so much for your quick answer!

One question - I have been exploring Azure as an idea and while I understand that the platform could provide a host of other possibilities as well, we would prefer a solution without it at this time. There are smaller (cheaper) and more straightforward options for hosting SQL Server databases on the internet, are there not?

 

[Minty]

There probably are but I have no experience of them.

We have only had one issue with Microsoft's Azure platform in the last 5 years, and it was minor and only affected 2 clients out of about 30.
It's reliable, robust, scaleable and very well backed up with easy and quick to use tools.

Using the basic configuration it's not silly money for a business, and allows you to have multiple databases:

 

[theDBguy]

@AronG Welcome to AWF!

You might also consider comparing the costs between SQL Server hosting and Terminal Services. With TS, you wouldn't have to change anything in your existing Access database.

 

[The_Doc_Man]

Just for a bit of clarification...

In the "true" sense of a cloud file server, Access does not work because of protocol incompatibilities. Note that I specified "cloud FILE server."

Other external services exist - Azure certainly being one of them - that are a little different than simply storing a file in the cloud for pickup from any of many possible locations. Using Azure has obviously been tested and will work OK. Using an external SQL Server (via some variant of ODBC protocols) will also work. Using Terminal Services so that your "in the cloud" machine is acting as a Remote Desktop server is also viable.

You are getting good advice from my colleagues on "where to go with this project." I will give you the advice of where NOT to go. The standard Microsoft cloud that does automatic file backups for you, for example, does not work with Access because of the aforementioned protocol issues. Anything relatively close in function to the "standard cloud" will have the same issues.

 

[GPGeorge]

Hello everyone!

I am working for a small company and we have been discussing migrating our in-house Access based ERP solution to the cloud. What I am looking for is a professional to let us know if we are on the right track. I am no expert at Access or cloud tech (yet) so sorry if I make any mistakes. Thank you for any and all input!

0) Our front-end will still be MS Access distributed to all of the employees' computers.

1) We have a considerable amount of data (20+ years' worth) on a local server. We are looking to migrate this to a web-hosted MS SQL server. As I understand, Access to MS SQL server migration is relatively simple. We will have to pay a monthly fee for the hosting of the database.

2) We will link the each Front-End with the hosted database via ODBC.

3) We will have a trial period where we check how this connection functions. Naturally we are looking for a solution that stays performant and has no size limitations.

+1) We have been thinking about synchronizing the online SQL server to our own, local server for security and availability purposes. Is such a synchronization possible? Does the process take long? Does it slow down either party?

+2) I am assuming that this hosted MS SQL back-end will be able to be used by other applications as well. For example, we would use a tablet with a different front-end solution to communicate with and use the server from anywhere in the country. Is this assumption correct?

Finally, I would be extremely grateful if anyone with experience would be able to give me documentation or stuff to look out for. I will be looking at Google for everything that I don't understand .

First, I haven't read all of the other responses in detail, so I may repeat some things.

1. Migration from Access tables to SQL Server or SQL Azure is straightforward, although I would not use the word "simple". You have to know what to look out for. Some datatypes (I'm mostly thinking of dates with times) may require decision-making about how to configure them. Access is much more tolerant of "date-like" values in date fields than SQL Server, with 20+ years of data, there could be some surprises lurking in your data.

SSMA is a great tool, IMO, although it does require some familiarity to configure it appropriately.

2. Make sure you use the latest ODBC driver, which is driver 18 at this point.

3. Performance may fall off badly unless your Access interface is correctly designed for Client-Server work. Key example. Bind forms to parameterized queries, not to tables. Load only the record or records needed at any given point by using the correct parameters. A typical, legacy Access design that binds tables to forms and then applies a filter after the fact are generally not usable with a hosted SQL Server back end. There are other things to look for. This will be the major effort in the migration.

+1) Great idea. I used a similar process for an application I work on a dozen years ago. Depending on what has to synched and how, it's very doable.

+2) Another great idea. Hybrid apps are powerful. Look into PowerApps for the tablet-based application.

 

[Pat Hartman]

For starters, you need to clarify the WHY of this conversion. What is your objective? What problems are you having with your current configuration?

A cloud environment can easily be created without having to modify your Access application at all. Even if you might still want to convert the BE to SQL Server as a final step.

I haven't set anybody up for a while. In the past, my clients were all large enough to be able to host their own Citrix server. These days, I think the Azure platform is what my clients are using. This lets them run remote desktop from the cloud.

I believe that what Minty is talking about requires a conversion of the BE to SQL Server which, depending on how you built the application initially can be a lot of work to unwind. It also doesn't involve Citrix/remote desktop. It involves direct links from the Access FE to the SQL Server database hosted in Azure. So, my suggestion is don't start with the SQL conversion. Just try to host the Access FE/BE using Citrix/remote desktop on the Azure platform. Sorry, I cannot give you the details. Once that is working, you can convert the BE at a more leisurely pace. Then you can decide whether to use the hosted desktop or direct link method. But the direct link method will almost certainly require more tweaking of the FE than using Citrix/RD to host both the FE and BE.

 

[AronG]

For starters, you need to clarify the WHY of this conversion. What is your objective? What problems are you having with your current configuration?

A cloud environment can easily be created without having to modify your Access application at all. Even if you might still want to convert the BE to SQL Server as a final step.

I haven't set anybody up for a while. In the past, my clients were all large enough to be able to host their own Citrix server. These days, I think the Azure platform is what my clients are using. This lets them run remote desktop from the cloud.

I believe that what Minty is talking about requires a conversion of the BE to SQL Server which, depending on how you built the application initially can be a lot of work to unwind. It also doesn't involve Citrix/remote desktop. It involves direct links from the Access FE to the SQL Server database hosted in Azure. So, my suggestion is don't start with the SQL conversion. Just try to host the Access FE/BE using Citrix/remote desktop on the Azure platform. Sorry, I cannot give you the details. Once that is working, you can convert the BE at a more leisurely pace. Then you can decide whether to use the hosted desktop or direct link method. But the direct link method will almost certainly require more tweaking of the FE than using Citrix/RD to host both the FE and BE.

Thank you for your input!

We have pretty clear (and perhaps overdue) reasons for the conversion - (1) our database is becoming large, it is only a matter of time before Access BE will reach its limit (2GB if I am correct); (2) we are a smaller company, but we have employees who would benefit greatly from being able to work from home or while on business trips (which by the nature of our work is frequent); (3) we have some ideas about how we could develop and improve our general processes that are only possible with a seperate BE available through an internet connection - the tablet example in the original post being one of them.

We might explore the Citrix/remote desktop possibility. As I've seen it recommended multiple times, I assume it is a stable and performant solution. Is there any downside or caveat we should look out for if we decide to adopt this option? And thank you again!

 

[The_Doc_Man]

Citrix isn't free (has a per-seat licensing scheme) and there are configuration issues that sometimes crop up if your IT does not pay close attention to your needs. If you have a good IT staff, however, they can set up Remote Desktop Protocol (RDP) on your individual laptops or workstations and set up an RDP server to be the central, internet-accessible way to make that remote connection.

The short answer to "correct configuration" is the same for Citrix and for home-grown RDP (because Citrix uses RDP behind-the-scenes). Your RDP/Citrix server needs to have individual user folders for each authorized user. NO FOLDER SHARING for the FE files!!!! If you have a front-end file, copies of that FE go in the individual (and private) user folders. Each user needs a licensed copy of Access for the server. There IS such a thing as a multi-user license but I've never used one of those myself. The Navy network had its own RDP managers and didn't use Citrix in-house. Our forum member Pat Hartman has more experience with Citrix than I do. She can certainly offer relevant help. Others among our members have also described their experiences with Citrix.

Your remote machines technically don't need a resident copy of Access if they are going to use ONLY the RDP pathways to get to Access apps. However, if you have database apps resident on the remote machines, you will need resident Access there as well. Your remote machines establish a windowed session on the RDP server first, and from that session, they can use Access remotely. The RDP connection makes it LOOK LIKE your session is on the remote machine but in fact it is not. It is running on the RDP server and - with respect to that session - they are running a local copy of Access and the back-end file that they share is local to them. Response-wise, this kind of connection is relatively fast since all file locking is managed on the RDP server rather than via the network. Data exchanges between FE and BE file are also local to the server (at worst through the Loopback connection at internal address 127.0.0.1), so you never put database traffic on the network. The server, if it is robust enough (or has enough cores to support a large number of active threads) will usually be able to keep up. The speed of the network between the remote machines and the server also affects response speed if your output tends to be done in big clumps. Most Access apps are "bursty" rather than continuous in their load profile. You won't see blazing fast access during peak loads because you ARE sharing a bus architecture, but it should still be very fast.

 

[Pat Hartman]

Let me be a little clearer on the technology. Citrix and RDP both run on a server. Either the same physical server or separate servers on a LAN.
That/those server(s) can be hosted on your own corporate network or at a 3rd party including an Azure cloud. The benefit of Citrix/RDP is that the FE/BE (whether ACE or SQL Server) can be on the same physical server or at least on the same network so your responsiveness will be at least as good as your current L:AN setup and possibly faster. NO data is transported over the internet. Keystrokes are sent from the user PC to the server and images of the forms/reports are sent from the server to the user PC.

The Access FE/Azure BE setup uses the internet to transfer data between the BE and the FE and is extremely sensitive to slow network connections, especially using WiFi and will almost certainly require potentially significant modifications to the FE to get satisfactory performance. You must be absolutely on top of query efficiency, and you never want to transfer a single row more than the user needs at that moment. Another variation of this is the Access FE physically installed on the client PC and a VPN used to connect to the BE back at the office. This is very slow with an ACE BE but usually tolerable with SQL Server BE.

With Citrix/RDP, the server and desktop are on the same LAN and are using a wired connection, never WiFi. So, the FE/BE connection is always hard wired. Your user to Citrix/RDP will probably be WiFi. WiFi is slow and has frequent interruptions, but the Citrix/RDP servers are fault tolerant and can usually recover. Even if they don't, the connection between the FE and BE is still hardwired and stable and if you can reconnect to your Citrix session, your Access app will probably still be open and waiting for you. However, with the Access FE/Azure BE setup, Access is not fault tolerant and if your WiFi connection is interrupted, Access will lose its connection to the BE and it is possible that data corruption will occur.

 

[The_Doc_Man]

A further comment on a fine point in Pat's excellent discussion:

if you can reconnect to your Citrix session, your Access app will probably still be open and waiting for you.

Absolutely true, but depending on just how "tight" the IT department wants to be, a "reconnect" might not be possible due to imposed rules. This "reconnect" depends, not on a technical issue, but on a policy issue. On military networks dealing with AT LEAST Privacy Act data, a reconnect of a dropped session will not be permitted. On commercial networks, there is no telling how it is set up until you try.