We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Moving Access to Azure

Medium Priority
78 Views
Last Modified: 2020-05-06
I'm considering setting up SQL Server for my MS Access application. A colleague suggested moving the data to MS Azure instead. Is it realistic and how much work would be involved?
Comment
Watch Question

Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
It is much simpler to move your data into Azure than prepare a whole SQL Server.
You can save:
  • Installing of windows on real or virtual machine
  • Intalling and configuring SQL Server
  • Pay for SQL Server and Windows licence

You will pay a fix amount in each month (couple dollar) and that's all.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Personally i don't think there is a real difference regarding the amount of work...i don't consider that the SQL server installation is nothing more than click next- click next...the real work would be on Access and the code rewrite.
If you have hosted SQL server then you can utilize the full wired (LAN) reaching rather easily to 10GB
The down side is that you have to pay for the SQL server (in hardware+software) and also you have the cost of administration...
Now the Azure is pretty much the same minus the fact that the SQL operations are done on a remote data center and the maximum speed is limited by your Internet connection speed/stability/availability.
Its up to you decide based on the technical/financial factors.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
By the way ...before everything just go and check Azure SQL calculator
https://azure.microsoft.com/en-us/pricing/calculator/
And because there is an amazing number of options....it can make your head spin....just visit this other calculator and try and simulate an existing SQL to an Azure equivalent...
https://dtucalculator.azurewebsites.net
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I see little difference.

SQL Server is a on-prem
Azure is in the cloud

What do you need?  

Azure is on MS cloud, with what that entails.
SQL Server is on your machine with everything that entails.

Unless you truly need the cloud aspect, I'd normally stick with SQL Server, but even that is debatable.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I've done both.  Clients tend to prefer to use the on-prem SQL Server if they already have an IT infrastructure or a contract with a good IT company which meets their needs.  Several of my clients use Azure for all or part of their operations and have been satisfied with it.  I found that even when the clients had a relatively fast internet connection (300Mbs), I needed to modify some of the forms and queries to take advantage of the server after we made the migration, but this really applies to SQL Server as well.  You won't necessarily see speed improvements (and may see degredation) until you actually rework some of your forms and reports to take advantage of server side processing.

Dale
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<< A colleague suggested moving the data to MS Azure instead. >>

The amount of work required to retro fit an Access app can be considerable.  It really depends on the app.   Most Access apps are not written with true client/server in mind.   In some cases, it may mean re-writing the app entirely for it to work well.  For example, in a client / server app, one would not bind a form directly to a table.  You'd use a lightweight cursor to give the user a list of records or a find box, then fetch one record at a time as needed.   Reports might need local tables, etc. The whole idea is you want the absolute minimum amount of data traveling over the network.  

Even moving to on-prem SQL can be an effort.  Again it's the client/server thing.  But it's not as bad as moving to Azure because you are operating over a LAN instead of a WAN, so you can get away with more.   Most see a pickup in performance when moving to on-prem SQL, but that's not always the case.

With SQL (on-prem or Azure), you need to push processing server side as much as possible by using pass-through queries, views, stored procedures, and triggers.

I would lean towards on-prem SQL unless you have a very lightweight app.

Jim.

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Well, can it be done? Sure, cause in the end is the same as an Access frontend using on-premises SQL Server.


BUT:

1) You need to make your application fit for it. Here you should read the J-Street papers.

2)Using Azure SQL Database or an Azure VM hosting your SQL Server means additional, serious latency even with dedicated lines to your hosting Azure data center.

From my experience 2) means you can only run small and simple applications that way. For larger applications with complex process steps using a RDP solution hosted on Azure (or somewhere else) and logging into a remote host where the application runs per user is the way to go. Latency would kill your user experience otherwise. The needed licenses for this is in many scenarios not cheaper than hosting your on-premises SQL Server. The only difference are the costs for maintaining that on-premises server and the associated DRM costs.

Author

Commented:
Thank you. There is a bit to consider there.

The reason I ask is that we have a new application that would have good use where different companies possibly in other countries could access a single data source.

Is this an access possibility?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
How many users?

Jim.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Yes, in such a context a Hybrid system (Azure back-end, Access front-end) could work.

That said, I personally would privilege a proper web solution, PHP, .Net.

Another avenue to explore, depending on your needs, would be PowerApps.  For relatively simple applications they can be put together very rapidly and easily deployed.

Everything come down to your project specifications.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Access with Azure performs significantly better when the Azure database is on a server that is close to the user.  I've seen latency issues when trying to hit a West coast (USA) server from the East Coast, but don't normally run into that same latency when I've remoted into a West coast computer and am running the application from there.

Just another data point in your decision making process.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
You can also benchmark the whole situation
I have a pre-alpha tool that does this
Just go to https://www.dropbox.com/s/bdki4ldnifb7hl2/BenchMark_Release.accde?dl=0
and download BenchMark_Release.accde
Open it..accept the Security Check and hit the button "Benchmark" on the Ribbon ..on the form that pops just hit the 1st little button next to the big empty "Quick Connect"..it will show a sample Connection String... (you can put what ever Connection String as it works...it is not restricted to Ms SQL)
i am afraid the Connection String i am providing isn't working because gearhost deleted my test database...so substitute your own connection info
and similarly hit the little button next to "SQL" for picking a sample SQL
Hit the button bottom left with the Thunder database and you should start getting readings.
Take note that is 32bit only
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
On this point that Dale made:

"Access with Azure performs significantly better when the Azure database is on a server that is close to the user. "

 Be aware that's true of any software as a service or with hosted servers.   If your users are on the opposite side of the country from whatever you are using, performance will be poor.

 This is one thing I find that all SaaS / hosting vendors gloss over.

Jim.
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Access with Azure Database S0 or S1 in Europe even using the nearest data center with dedicated line has terrible latencies, feels like in the 90's where I had once an Access database splitted over a 1MB network..
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
The two users that I have using Access with Azure are both located in the Seattle area and their data is in one of the west coast data centers, so they get relatively good response times, especially after modifications to the database to use good client/server techniques.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I've heard mixed reviews on using Access with Azure, even when the data center is close by.   That's not Azure.   What it boils down to is the app and how it works.

If you had client / server in mind when it was written, it works OK.   But if you did not then it doesn't and unfortunately the majority of Access apps I see are not written that way.

In fact most are so poorly written that they don't even work well on a LAN.

Jim.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
<most are so poorly written that they don't even work well on a LAN. >

ROTFL

Author

Commented:
possibly thousands of users. But reading Jim, John, Dale and Daniel it looks like a different solution is required.

Author

Commented:
For probably the first time in a long time I,m feeling way out of my depth here. I think I need to employ someone local. I can't even begin to guess why loading temp tables would help. How would they Keep up with changes. Surely if another user changes something then the temp table is out of date and would need re-loading or at least would need loading at report time?
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Of course Local vs Cloud SQL its a questionmark regarding performance but a carefully designed database can withstand even low speed high latency lines...
Here is a small video about pulling data from a $3/mo cloud VPS with a line of 14Mbps/50+ms latency
https://www.linkedin.com/posts/tsgiannis_a-small-demo-of-connecting-ms-access-fe-to-activity-6392696633531858944-dsuU
The worse it that the demo is based on a poorly based table...but again if time and effort is spend anything is possible

Author

Commented:
So how do you  connecting Ms Access FE to Cloud (VPS -MySQL). Do you already need to have SQL link to access data?
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
What do you mean by SQL Link ?
Just the ODBC  driver that matches your Office bitness and the correct info : IP Address/database/UserName/Password..

Author

Commented:
I mean you have to set up the link to the access .mdb file somehow using SQL instead of Jet?

You can see I need help
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I am working with Pass through queries no Linked tables..
Lets say i am not a big fan of them regarding performance.
Migrating an Access BE can be as simple as picking some kind of migrator like SSMA or Bullzip's solution and do it probably in a few hours or it can be a demanding process covering months of work in order to change the application's philosophy from something that you double click it and fetches all the data to a hard filtered subset of your data that covers your need

Author

Commented:
Thank you all I have a good starting point. Excellent!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.