Database rebuild question

[courtg9000]

The server is unlikely to be the issue when it comes to performance IMV. Access will have bottlenecks at the client side and on the network... particularly where there are concurrent users accessing the database across the same network.

The server specs sound perfectly sufficient for running as a small business file/print server and hosting your SQL Server instance.

Its definitely not the server.. Its everything else around Access that you state, definitely.

That box is running the joint business activites of both myself and my wife. The properties, my business - brokerage, consultancy, the magazine etc and her investment stuff. There is some trading software on that box but the load is low.
I will happily put the new system on a dedicated box, There is one available doing nothing.

Email is handled by a commercial host as are the main websites. I spent a shed load of cash and seriously upgraded all of our hardware (business and personal) just over a year ago when I ran out of space in a couple of places during a hospital stay. That server got upgraded as did our laptops, tablets, my webservers and 2 x 16 TB Nas devices! There is tons of bandwidth and spare space. The network here is pretty good too.

[LondonManc]

The server is unlikely to be the issue when it comes to performance IMV. Access will have bottlenecks at the client side and on the network... particularly where there are concurrent users accessing the database across the same network.

The server specs sound perfectly sufficient for running as a small business file/print server and hosting your SQL Server instance.

PS: happy to be corrected if there's a proper dev about; it's been some time since I cared about Access DBs.

The interesting thing (ish) about Access is that you can still use any forms with the database tables that you've ported over to SQL Server. Potential migration route is therefore upgrade to Access 2019 if it's not already on that, then to SQL Server 2019 developer edition. Look at the licencing though - longer term you may want to move to Express once it's production-ready if Developer edition doesn't support it. Nothing to stop you doing dev locally on dev edition then pushing changes on to Express for example.

Consider also getting a git repository or similar version management tool to sort your code versions out. If you get stuck with anything on the SQL side, just google Brent Ozar - his website is a fantastic resource, especially when you're only doing part time DBA tasks.

Power BI is also free if the users want to play with the data and draw pictures with it.

[courtg9000]

The interesting thing (ish) about Access is that you can still use any forms with the database tables that you've ported over to SQL Server. Potential migration route is therefore upgrade to Access 2019 if it's not already on that, then to SQL Server 2019 developer edition. Look at the licencing though - longer term you may want to move to Express once it's production-ready if Developer edition doesn't support it. Nothing to stop you doing dev locally on dev edition then pushing changes on to Express for example.

Consider also getting a git repository or similar version management tool to sort your code versions out. If you get stuck with anything on the SQL side, just google Brent Ozar - his website is a fantastic resource, especially when you're only doing part time DBA tasks.

Power BI is also free if the users want to play with the data and draw pictures with it.

Yes this would be the line of thinking if SQL server is what I choose.
I have quite an "intranet" going here and have GitLab installed on a webserver for source code management. I use it for my HTML stuff - I have a lot of HTML and CSS there. I have also got tons of retro games in HTML5 and stuff that I have purloined from GitHub (great for when i'm in hospital bored witless). Its linked to my personal NAS too so plenty of space!

Thank you for the suggestion of Brent Ozar I have just looked at his site - its great stuff!

[LondonManc]

Yes this would be the line of thinking if SQL server is what I choose.
I have quite an "intranet" going here and have GitLab installed on a webserver for source code management. I use it for my HTML stuff - I have a lot of HTML and CSS there. I have also got tons of retro games in HTML5 and stuff that I have purloined from GitHub (great for when i'm in hospital bored witless). Its linked to my personal NAS too so plenty of space!

Thank you for the suggestion of Brent Ozar I have just looked at his site - its great stuff!

You're welcome.

Seriously, it's really easy to import data.

Create a SQL Server database, right click on its name in Management Studio, choose Tasks, then Import Data. Pick Access from the list of data providers, find your database in the explorer and choose which tables you want to import. Grab a coffee and try and look busy!

[courtg9000]

You're welcome.

Seriously, it's really easy to import data.

Create a SQL Server database, right click on its name in Management Studio, choose Tasks, then Import Data. Pick Access from the list of data providers, find your database in the explorer and choose which tables you want to import. Grab a coffee and try and look busy!

Yes. This now the plan. SQL Server.
I was thinking I might get some varied and interesting responses here instead of a non valid arguement about hardware but hey ho!

In theory I can do the migration this weekend. I will build the new box for it, get it up and running and get some use of it and review in 3-6 months.

With between 2 & 5 residential evictions (tons of paperwork and tracking) coming up and possibly as many 100 pubs, hotels and restaurants (might also be some other businesses as well!) coming on to the system along with other stuff its probably best I do upgrade immediately.

Thank You again and thanks also to @wattaj

[LondonManc]

Yes. This now the plan. SQL Server.
I was thinking I might get some varied and interesting responses here instead of a non valid arguement about hardware but hey ho!

In theory I can do the migration this weekend. I will build the new box for it, get it up and running and get some use of it and review in 3-6 months.

With between 2 & 5 residential evictions (tons of paperwork and tracking) coming up and possibly as many 100 pubs, hotels and restaurants (might also be some other businesses as well!) coming on to the system along with other stuff its probably best I do upgrade immediately.

Thank You again and thanks also to @wattaj

No problem. As I said, really easy to import the data as long as the SQL Server box can see the Access database.

Just shout on here if you need any assistance.

[vetran]

Thank you for the suggestion of Brent Ozar I have just looked at his site - its great stuff!

+1 for Brent , also very easy to listen to. Recommend his video explanation of pages, it was the first time I really got it.

[DimPrawn]

You missed the bit where I pointed out that Access queries run on the client: all data for a query is sucked from the DB and then Access will discard what it does not need to return to the user. That's a lot of local load and network traffic. SQL Server is proper server-side processing.

Access may have changed since I last looked, but I doubt that it's changed that much.

^ This.

People forget the fact that Access is not a client-server database.

You will need the fastest network possible and the fastest disks. Forget RAM and CPU on the "server".

The biggest risk is the whole file getting corrupted!

[courtg9000]

^ This.

People forget the fact that Access is not a client-server database.

You will need the fastest network possible and the fastest disks. Forget RAM and CPU on the "server".

The biggest risk is the whole file getting corrupted!

Yep corruption is a worry. Thankfully I have very regular backups

[wattaj]

Yes. This now the plan. SQL Server.
I was thinking I might get some varied and interesting responses here instead of a non valid arguement about hardware but hey ho!

In theory I can do the migration this weekend. I will build the new box for it, get it up and running and get some use of it and review in 3-6 months.

With between 2 & 5 residential evictions (tons of paperwork and tracking) coming up and possibly as many 100 pubs, hotels and restaurants (might also be some other businesses as well!) coming on to the system along with other stuff its probably best I do upgrade immediately.

Thank You again and thanks also to @wattaj

Remember to do a proper DB back-up plan and schedule. This will be a bit more involved than simply backing up the Access database file that you may be used to. Good luck. Happy to help.