Upgraded main site from Access to SQL

I make a living running a large retail website, which has just hit a product count of 4000. I use the VP-ASP package, which is an open source ASP application that can be used with an MS Access, SQL or MySQL database. It's a great package, being open source, I can change, add, or modify any part of the application to suit my needs. This is crucial for an online store.

I had been using the Access back-end for about a year, and with some updates over the weekend it started to slow down considerably. Right from the start, search and any other function which called a lot of data from the database was reasonably slow (but still acceptable). After the weekend it was obvious that we had outgrown Access. I had heard bad things about running a web application from an Access database and we were starting to experience them. It makes one realize how much work the back-end database of a large, busy web application actually does.

I made the decision to upgrade to an SQL database, which involved changing our hosting plan. I also changed us to a dedicated IP address. We are using a shared web server, connected to a separate SQL server.

The changeover could have been smoother, we had issues with incompatible datasets between the Access and SQL database, and the hosting company's ODBC fell over halfway through the migration due to them shifting our domain to another server at the wrong time. We had to wait overnight for that to come back up again. We now have everything running on the new server, and are just waiting for the DNS records to catch up and start pointing to the new IP address. It still feels wired having the site down for so long. It will have to be done better than this next time, as 24 hours downtime is unacceptable.

SQL is much better for this purpose than Access was. I get whole pages of records displayed instantaneously, and search is actually usable. SQL is running this application better than I can locally on a test server and MS Access. I have read in several places that Access is not well suited to web applications, and it's true. While such a database would be acceptable for 1000-2000 records and up to 10 tables, I would not want to push it much further than that.