Is sql azure viable for fairly large data sets with MS Access 2013 or 2016 front ends via odbc?

Question

It's been a good number of years since I tested sql azure as a data provider for MS Access forms and reports via odbc. I'm sure it's viable for smaller databases but is anyone working with it using databases that have more than 100k rows? If so how does it perform.

The app I am thinking of using sql azure with is all access at this time. I'd port it to sql server first and then sql azure (at least that's my expectation). I am positive that I'd need to thin down some of the huge data sets that get returned by the current application. I am good with pass through queries and stored procedures. I would expect to keep using bound forms, but could adjust much of the way the current bound forms work.

I'd have it on a local sql server but the IT dept is apparently not very keen on adding new databases. Taking it off site would be a better option if performance doesn't suffer too much.

So if anyone has direct experience with MS Access and sql azure and good sized databases, how were they configured, how did you make it work, or did you give up on it? I will probably do some testing on my own but any input here might help me decide on the whether the effort is worthwhile.

Answer 1

If you use SSMA you can migrate directly to Azure.

There is nothing like trying this with YOUR application, YOUR queries, YOUR record counts, YOUR internet connection.

Where things are too slow, it sounds like you already understand how to mitigate that: load less data (e.g. by first asking user what they want to see), use sprocs and passthrough queries.

---

-Tom. Microsoft Access MVP

Answer 2

So I can derive from what you wrote that sql azure is a viable backend for larger (on the Access scale) databases.

Reports could be a concern. Those often need a lot of data to be passed to Access. With sql azure the network transmission will be slower, and that may be inescapable. But also part of what I'm trying to get a sense of is how responsive sql azure is in general for simple operations. Do you notice a different in performance with basic operations?

Example, with the Access Web Apps, which used sql azure plus a ton of other azure stuff, everything was very laggy. I am hoping that sql azure by itself is not laggy; and I accept the fact that if large datasets do have to be sent over the wire, there will be a performance cost vs local sql server.

A reason to stage it through sql server is that this database is in such disorder and needs to be reorganized on the way to sql-anything; and I'm pretty sure that'd be easier with a local copy of sql server.

Answer 3

I"m still interested in anyone who has direct experience with this. Has anyone used an Access front end against a sql azure backend for a non-trivial application, and what is your experieince, upside/downside? So far the only feedback I have is to try it myself...but surely someone 'out there' has gone this route and can report on the outcome. I have run small Access projects against sql azure without issue but those where more like tests.

Answer 4

Well, the only real issue is to keep in mind that your internet connect is about 100 times slower than your el-cheapo office network.

So what this means that if your connection is high speed internet and OVER the internet then MUCH attention is required for good performance.

In other words, often you seen someone create a form in Access and then just bind it to the table or linked table. If the dataset is large, then you BETTER ensure that you open the form with a “where” clause to restrict the rows pulled.

So often Access gets a bad rap in terms of performance, but that’s not really access’s fault, but oh so easy how in access one can make poor design decisions in terms of the data you pull into a form.

So just keep in mind that really slow connection speed – it often 10 or 100 times slower. So if you test and build using a local instance of SQL server, you only notice the HUGE slowdown when you migrate to SQL azure and start using the internet as your connection.

You can read about this speed issue here:

http://www.kallal.ca//Wan/Wans.html

I will not be a broken record, but if you think the above 10+ year old article does not apply to you, then you are doomed! Absolute doomed. The above article applies 100% to you – if you have ANY kind of doubt that the above does not apply to you, then your project will fail.

So have I used hosted SQL with Access over the internet? Yes (not done it with SQL azure, but the results and process are the same). So much effort and testing is required. You do NOT want to develop with a local copy of SQL server, since such a process will MASK and fool you in terms of performance.

So the setup can work fine, but you need some good skills in terms of making Access applications play nice with SQL server.

It not that access is slow, but you don’t want to spend lots of time developing with a very high speed local connection, and then out of the blue deploy to a system in which you have factors of 100x slower. That means a 4 second delay can become 400 seconds!!

So you do want to have worked with Access + SQL server quite a bit and from that experience you learn what works slow, and what works fast, and what to do to improve performance in these cases.

The simple most important tip here is to ensure that you run + test and use SQL Azure during the development process, else you wind up with an application that runs rather slow, because you developed with a VERY different kind of connection and speed to SQL server.

And the better the original application was designed to limit records pulled, then the better the results when using SQL server in the cloud.

So yes, this can work for complex applications, but that assumes you have good experience using Access + SQL server. So I would be hesitant if this is your first go around – you need over time to learn what works, and how to keep performance high.

So do keep in mind how much slower the connection is that you be using once you move the database to SQL Azure.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 5

Thanks for that Albert. I am aware of the issues when pulling data over the internet. It used to be impractical due to the speed differential but these days with the kind of broadband we have available, it's like to work well as long as the factors you and Tom have brought up. I'm highly experienced with Access and SQL Server.

With a "new" technology component like sql azure (aka "sql database"), I do have cautions that it will work just like an instance of straight up sql server hosted remotely. I've been around long enough to know that there are characteristics that might or might not bite with a new approach. Thus, I don't have full confidence that sql azure will behave just like hosted remote sql server. It might be just about the same. But I was hoping that someone would be able to say been there, done that, no special issues.

I have "been there, done that", but only on a limited basis. Limited basis is not equal to a production database that is in action for an extended time period.

Still interested to hear how it's gone from anyone that has an application in this configuration.

Answer 6

As noted, I never used SQL Auzre, but I did take some complex applications and use ODBC to a web based (hosted) version of SQL server. In this regards, I would be hard pressed to think of any real difference in the setup I used as compared to using SQL Azure. In other words using web hosted SQL server and SQL azure should not perform or behave really much different in any way that I can think of. The key part was the "over the internet" connection part.

And while "many" have high speed internet - it still a ton slower then your local LAN.

However, I will say that internet speeds have improved a lot - but often up-load speeds are still rather slow.  So say you have a 15mbit internet. That still about 10x slower then your 100baseT connection, and most LANs today are 1000baseT.

I do find that if the application is optimized well, then things tend to work quite well with any internet connection - even Wi-Fi at a coffee shop.

So I not used SQL Azure in production, but ODBC to a web hosted instance of SQL server is a rather "close" example of how such applications will run (doubt any difference would exist between the two setups). At the end of the day, this still going to center on having good SQL skills  that work well with Access. Such applications are more work because of the extra efforts required to keep performance high.

Regards

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 7

Once again thank you. I don't see a barrier if the app is well designed, so if the app I'm working on takes this fork in the road, should be ok.