Database rebuild question

[elsergiovolador]

How much more time does one need to identify that one is running one's business on legacy technology? Your solution, such as it is, does not resolve the problems that have already been identified. Whilst I understand your expedient position, it is not one upon which I would run a business.

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.
Problem seems to be that the database is "too big". If you move it to another system it is still going to be perceived as big.
The problem seems to be the fact that the hardware it is running on makes it being perceived as big. 6GB is nothing in 2020.
Update your hardware and then reassess whether it is still an issue.

[LondonManc]

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.
Problem seems to be that the database is "too big". If you move it to another system it is still going to be perceived as big.
The problem seems to be the fact that the hardware it is running on makes it being perceived as big. 6GB is nothing in 2020.
Update your hardware and then reassess whether it is still an issue.

What's more worrying is that you checked the website after giving your first lot of advice.

[courtg9000]

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.
Problem seems to be that the database is "too big". If you move it to another system it is still going to be perceived as big.
The problem seems to be the fact that the hardware it is running on makes it being perceived as big. 6GB is nothing in 2020.
Update your hardware and then reassess whether it is still an issue.

Trust me this database has reached the end of its time on Access. It was developed for one user it now has six regular users and this may increase. It is creaky at the moment and no end of upping RAM is going to solve the problem that it has outgrown the platform it sits on.

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.

I have been using this database pretty hard this morning. 4 new partner brokers, assigning deals to each of them. Updating some other stuff adding 6 x cut price Spanish bars and running some reports. It is clunking.
I did some analysis just now, I am looking at probably 2GB per year increase in DB size around 4GB per year in attached files such as PDF's of fire certificates, photographs etc

Bottom line it needs a replacement of some description and if that is a move to SQL server than thats fine.

As said previously I am looking at all options. I can still do some IT stuff myself and there is a good few quid available if needed to pay a contractor to sort it. There is good hardware sitting waiting again as I have previously said.

[vwdan]

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.
Problem seems to be that the database is "too big". If you move it to another system it is still going to be perceived as big.
The problem seems to be the fact that the hardware it is running on makes it being perceived as big. 6GB is nothing in 2020.
Update your hardware and then reassess whether it is still an issue.

It's big for MS Access. Until very recently MS recommended 32 bit access, and I suspect lots of bits of the database code are very limited. Certainly in terms of multi-threading and RAM usage.

Access was never designed to run large databases, and it has a laundry list of issues related to it.

[elsergiovolador]

What's more worrying is that you checked the website after giving your first lot of advice.

I live in a nonlinear space-time.

[elsergiovolador]

Trust me this database has reached the end of its time on Access. It was developed for one user it now has six regular users and this may increase. It is creaky at the moment and no end of upping RAM is going to solve the problem that it has outgrown the platform it sits on.

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.

I have been using this database pretty hard this morning. 4 new partner brokers, assigning deals to each of them. Updating some other stuff adding 6 x cut price Spanish bars and running some reports. It is clunking.
I did some analysis just now, I am looking at probably 2GB per year increase in DB size around 4GB per year in attached files such as PDF's of fire certificates, photographs etc

Bottom line it needs a replacement of some description and if that is a move to SQL server than thats fine.

As said previously I am looking at all options. I can still do some IT stuff myself and there is a good few quid available if needed to pay a contractor to sort it. There is good hardware sitting waiting again as I have previously said.

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

[courtg9000]

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

2 x 2TB SSD's

[elsergiovolador]

2 x 2TB SSD's

Ok, ignore me.

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

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