SharePoint vs Azure SQL Backend

[leannemurphy]

Hi everyone,

My name is Leanne. I am an Access developer and about 10 years ago set up one of my business databases to have a Sharepoint backend. It was surprisingly fast and effective, but I'm also aware that in 10 years lots of things change. I have also set up my current business database to have an Azure SQL backend. To be honest, the SharePoint database was a heck of a lot faster than this Azure SQL Server database. However, the SQL database is stored in Australia (??) and I'm in the UK, so that might account for the slowness? Still have to figure out how to get it moved closer...

The point of this post is to ask you what you recommend for a new set of databases I'm doing for a customer of mine. They are a small team of 8 with plans to expand to 20 people over the year. A lot of the team work remotely part of the week, so having an Access split database isn't something I can do because they don't have a network drive that they can access from home and from work. Unless there is some solution I'm not aware of that allows you to have the backend on a Dropbox/OneDrive type location that allows multiple people to access the same data without it duplicating the database?

They are an analysis business so I will be dealing with large datasets that I am analysing using Tableau for them. So I believe SharePoint already falls over in this regard as I believe it is limited to 5000 records per table? Is that still the case?

I'm going to be building multiple Access frontends to cater for different team projects, and they will all point the same backend. I'm not a SQL Server developer, but I can get by with a bit of help now and then. Speed is going to be important though, so I'm still a bit hesitant going the Azure SQL Server route due to my current experience with it.

Also, if I go the route of Azure SQL then I'm going to have to figure out how to get each user to log in to the database and to sql because currently my SQL database is IP authorised and that won't be practical for the team as IP addresses tend to be auto-reassigned each day!

Can anyone give me some pointers?

Thanks,
Leanne

 

[Minty]

Hi, and welcome to the Forum.
We use Azure SQL for a number of clients, hosted on the UK servers, for UK clients and European customers, and it is perfectly acceptable.

If you look at the azure monitoring sites you will see the difference in performance between Austrialia and here:
https://azurespeedtest.azurewebsites.net/

South UK	27ms
West UK	31ms

Korea South	285ms
Australia Southeast	287ms
Australia East	292ms

The IP address issue is a more interesting problem to solve, we have developed a remote HTTPS process that can be used to rest an end-users IP Address with the aid of an existing database login and some specific additional security tokens.

 

[CJ_London]

if you are not hung up on using azure or sharepoint, you could use terminal server (or citrix which has a bit more functionality)- each user has their own login and profile, both FE and BE reside on the terminal server. Performance wise, very fast, all transactions take place on the server, the only thing that crosses the ethernet is keyboard and mouse moves one way and screen refreshes the other. Performance almost as if the BE is on your local machine. The BE does not have to be ACE, could be mysql or sql server for example.

I have a client based in the US who uses this method - when I connect from the UK, it's like I'm in their office.

Users can connect from anywhere that has an internet connection and any device that supports remote desktop (which includes iOS and Linux as well as windows, even some smart phones).

Another benefit is that if the connection is lost or intermittent, then nothing is lost, user just reconnects to carry on from where they left off

 

[leannemurphy]

Wow, okay thanks Minty! Any idea how I can migrate my current database from Australia to the UK??

Regarding permissions for a user to access the Azure SQL Server - Can I not just get users to log in to the Access database with credentials that have been set up on the Azure SQL Server? And I'm sure there is code that would then take that that info and log in to the SQL database?

 

[leannemurphy]

if you are not hung up on using azure or sharepoint, you could use terminal server (or citrix which has a bit more functionality)- each user has their own login and profile, both FE and BE reside on the terminal server. Performance wise, very fast, all transactions take place on the server, the only thing that crosses the ethernet is keyboard and mouse moves one way and screen refreshes the other. Performance almost as if the BE is on your local machine. The BE does not have to be ACE, could be mysql or sql server for example.

I have a client based in the US who uses this method - when I connect from the UK, it's like I'm in their office.

Users can connect from anywhere that has an internet connection and any device that supports remote desktop (which includes iOS and Linux as well as windows, even some smart phones).

Another benefit is that if the connection is lost or intermittent, then nothing is lost, user just reconnects to carry on from where they left off

Thanks CJ_London.... does this mean you're remoting into a physical PC at the office? Would it be their own pc at the office?

 

[Minty]

Wow, okay thanks Minty! Any idea how I can migrate my current database from Australia to the UK??

Regarding permissions for a user to access the Azure SQL Server - Can I not just get users to log in to the Access database with credentials that have been set up on the Azure SQL Server? And I'm sure there is code that would then take that that info and log in to the SQL database?

If you have an Azure active directory then yes I believe you can link that - have a read here:

Configure Microsoft Entra authentication - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics

Learn how to connect to Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics using the Microsoft Entra authentication.

learn.microsoft.com

That would alleviate the IP address issue to a large degree I think.

 

[Minty]

Sorry for the double post - Azure region mover:

What is Azure Resource Mover?

Learn about Azure Resource Mover

learn.microsoft.com