April 19, 2021 at 10:50 pm
Background: We have MS Access database split with backend tables that we want to convert to SQL Server and still use MS Access front end. We currently only have OneDrive and I know we can’t put SQL on OneDrive. We do not have a network set up. So we are looking for the best way to install SQL Server (where to put it). Our users work remotely from home and can access OneDrive from home. But need a way for users to access SQL Server with MS Access as the front end. Not sure where MS Access should reside. I’m thinking that the front end can reside on their desktop with ODBC connection to SQL Server. But we still need to install it somewhere where the ODBC connection is all they need. Not sure if a NAS, or Window Server, or other network server is needed to set up SQL Server. We have a junior database person that can migrate the MS Access tables to SQL Server, but the real question is where to put it and do we need to create a place for it since all we have is OneDrive.
The question is: What’s the best way to set up SQL Server in our environment described above with some pros and cons?
Thank You. JP.
April 20, 2021 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 21, 2021 at 10:34 am
This sounds like a case for an Azure SQL Database something which is publicly facing without the need to setup an enterprise style network and allow access in via VPN etc. Azure will probably be the better place to put it over AWS as if your using M365 already you will have a tenancy in Azure and can integrate your AzureAD into the platform to give you the security access into the Azure SQL DB.
April 21, 2021 at 10:42 am
You're in a bit of a tough spot, not having a network. However, probably, your best bet would be to use Azure SQL Database (or AWS RDS SQL Server, your call, I'd lean towards Azure pretty heavily). This lets you put your database on the cloud in a Platform as a Service offering. This means, most of the administrative tasks are handled for you, from backups to consistency checks. It removes tons of headache from your life since you won't have a full time administrator to take care of your SQL Server instance.
Connecting to it through ODBC is easy enough to set up. You can still secure it so only certain people can get through. You'll still be able to use the Access front-end. It does require internet connectivity, but you're in a situation where that's just going to be part of the deal. You simply can't have distributed people accessing a central database without connectivity being part of the game.
Transition from Access to Azure SQL Database should be relatively painless (it won't be perfectly painless though, plan for a period of testing & validation).
You could also do this with cloud-hosted virtual machines, but the set up, installation and maintenance of the system this way puts a lot more knowledge and skill requirements on your organization. Azure (or AWS), offers the best way around the majority of those skill & knowledge requirements. Not all of 'em though. You are moving to a brave new world, so plan accordingly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2021 at 10:44 am
Ha! Clearly I type slowly and/or too much. Agreement with @Ant-Green. +1
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2021 at 3:46 pm
Oh, Okay. I did a quick research on Azure SQL and I can get it for a 30 day trial and then pay as you go monthly. Our database is not large so I guess we will see how expensive the monthly rates will be. We have about 8-9 concurrent users and the backend data is about 1GB for now. We are expecting some growth.
So, my assignment is a little more research into Azure pricing. We are small so I hope it doesn't hit us with a large monthly rate.
Ant-Green and Grant, thanks so much for your advice and help.
JP
April 21, 2021 at 4:31 pm
if you go from Access to SQL and with 8 users you most likely will be able to work well with a 2 vcore and 8-10 GB ram - won't be much per month/year specially if you go for 3 years reserved license model
+ storage costs
biggest advantage of cloud is that if you need more power you can get it (at a cost) - you can even raise it up for only parts of the year if required and then lower it again
April 21, 2021 at 5:42 pm
Probably even a DTU model or serverless if you want to really save on costs, depends on the performance you need
April 21, 2021 at 6:12 pm
Agreement with what everyone else added.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2021 at 6:28 pm
Great. I found this link: “https://azure.microsoft.com/en-us/pricing/details/sql-database/single/”
It shows 2 vCore and DTU, serverless that Ant-Green mentioned. This page calculates the cost if I choose basic or standard tier on the DTU model. This is great. Hopefully, they will be able to give me a quote for both 2 vCore and DTU purchase models.
Thank you.
JP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply