Access databases in Azure

[Paul Le Sueur]

Dear All,

This is a question about the hosting of Access databases in either an Azure file server or in Azure File services. There doesn't seem to be much official information on this topic (at least not which I have found), so I am after some advice based on experience.

The project I am working on uses a split database architecture, so a front-end user interface database with "linked tables" to a back-end data database.

The project requires that the Access back-end database be moved into Azure, and the front-end accessible to PC based users.

The options I can see are as follows
The back-end:
1) hosted in Azure file services
2) hosted in a dedicated Azure file server virtual machine

The front-end:
1) hosted on the end users' PCs
2) hosted in Azure on remote desktop services
3) hosted in Azure virtual desktop (AVD)

Does anybody have any opinions or experience with any of these options? or can anybody offer any advice for a better option? At present, I am hoping to not move the back-end database to SQL Server.

Thank you,

Paul.

[#2 · Accepted solution]

@Paul Le Sueur You are right' there's isn't a lot of information out there...

I have only a couple of thoughts and a question.

• My understanding is that running an Access relational database application in an Azure VM is a good solution, performance wise.
• I would be cautious about Azure File Services if you want to put a back end there and a front end on a Windows desktop. I had no luck with a setup using Azure Storage Accounts because Windows Explorer could not reach them.

I don't know if there is a difference in architecture that would support the Azure File Services approach, but it would be good to know one way or the other upfront.

Finally, my question, out of curiosity, and not being argumentative. Why is SQL Azure off the table?

[#3]

Thank you very much for your insights @George Hepworth! 

I'm no Azure specialist, I have colleagues that do that  however, I gather that creating file shares and then connecting to Windows Explorer is possible, just a bit different to an on-premises setup. These articles look promising:

• https://docs.microsoft.com/en-us/azure/storage/files/files-smb-protocol
• https://docs.microsoft.com/en-us/azure/storage/files/storage-how-to-create-file-share?tabs=azure-por...
• https://docs.microsoft.com/en-us/azure/storage/files/storage-how-to-use-files-windows 

In short, to answer your questions:

• This Access database migration is for a customer and is part of a larger project to move everything to M365 and Azure. There are also pressures from other applications with file storage requirements, so at least at present, it does seem like running an Azure VM file server would be the better option.
• As far as Azure SQL goes, it's not completely off the table; but the database belongs to the customer, is under their control and development, and I see moving to SQL Server as being a step that should be avoided at present if possible. I will mention it as a possibility if Access hosted in Azure isn't performant, however, if the back-end database were to move to SQL, then the customer would need to spend out for SQL Server AND for an Azure VM file server; which would increase the budget...

Thanks again 

Paul.