Reply to: Database rebuild question

[Yuri F]

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.

Don't forget that Express edition comes without "SQL Server Agent" for running scheduled jobs (but Windows Task Scheduler with SQL_Scripts/PowerShell still does an excellent work) and has a limitation of 10GB per database (doesn't stop you from having multiple databases though, e.g. 3 dbs x 9GB = 27GB) plus other limitations (memory usage, cores, etc.), therefore beefy server might not be used up to full potential.
Obviously even though Developer edition is fee for development - it can't be used for production purposes (illegal / license violation).

OS - SQL Server is supported on Linux too, if it's a question of budget, or Windows 10 Home Edition (is allowed for business use, but won't have ActiveDirectory/DomainController functionality, no Remote DeskTop, but works perfectly via remote Terminal or Remote PowerShell session), if AD/DomainController/StrictSecurity are part of requirement - then instead of Windows Server: Windows 10 Pro will do the job too.

For reporting front-end purposes - Excel/PowerBI/SSRS are still good enough, but for data input it will need different interface (Access with it's forms will do too, SQL Server linked tables are supported if I remember correctly), otherwise writing your own Input/Output interface is inevitable.

Another alternative is going into cloud, Azure SQL Database is cheap as chips (range of different pricing/performance/storage plans with transparent billing), so no your own server, hosting and software costs anymore.

[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.

[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

[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!

[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.

[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.

[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 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]

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]

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 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.

[wattaj]

The server its on its pretty beefy. 16GB RAM 4TB Hard drives in raid 1 Config. I haven't been out of IT for that long.

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.

-- edit --
Sorry, I should have added ",but you'll probably know this already" to above. Soz.
-- edit --

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

[wattaj]

MS Access is old sure, but likely just as SQL Server. I checked at Microsoft website and the lastest is Access 2019. That doesn't sound legacy at all.

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.

[elsergiovolador]

2 x 2TB SSD's

Ok, ignore me.

[courtg9000]

Are your drives NVMe? If you have spinning drives then anything will be a drag.

2 x 2TB SSD's