Switching from Access DB to alternative

impr0v

I'm relatively new to creating database driven sites and have done a few now using access for the backend. I've seen quite a lot of criticism of it on these pages of late, and know from reading elsewhere that it cannot handle any more than a few simultaneous connections. On most of these sites it isn't really an issue, as the sites were really just to get into using ASP and SQL and aren't really expected to have high levels of users..

There is one site however, that I'd like to switch over to an alterative db to ensure that it can handle increased user levels without any trouble, and would like to know what my alternatives are. I don't want to switch to PHP, as I've taken the time to learn the basics of ASP and quite like using it. Also, my hosting is on windows machines.

Is SQL server my best option? I know this is fairly expensive, and will it involve much changing in the site coding, or simply a change on the server side to the DSN and drivers? What other options are there? And will it be hard to migrate the existing data in the Access db onto an sql server database?

Thanks in advance for any help.

steve-hosting36

Access is fine for 10/15 concurrent users. You can 'upsize' to MS SQL from access very simply (ther eis an MS wizard to do it). So my recommendation would be run with access and monitor usage. Once you feel performance is degrading, or your business/traffic justifies it, move to SQL.

mneylon

You could also consider MySQL.
A number of our clients have migrated from MS SQL to MySQL for quite complex sites without any issues.

RicardoSmith

blacknight wrote:

You could also consider MySQL.
A number of our clients have migrated from MS SQL to MySQL for quite complex sites without any issues.

Why would you migrate to MySQL from MS SQL, which has considerably less features and functionality. If you were already using Ms SQL you must have paid the licence for it anyway. I could understand why you'd use MySQL instead of MS SQL but not downgrade to it.

impr0v

steve-hosting36 wrote:

Access is fine for 10/15 concurrent users. You can 'upsize' to MS SQL from access very simply (ther eis an MS wizard to do it). So my recommendation would be run with access and monitor usage. Once you feel performance is degrading, or your business/traffic justifies it, move to SQL.

Thanks for that. When you say 10/15 concurrent users, do you mean user sessions, or does each recordset created count as a user. I suspect it's the latter.

cgarvey

RicardoSmith wrote:

Why would you migrate to MySQL from MS SQL

Speed .. for simple read-only type websites, MySQL wins hands down. I don't have URLs to prove that, but I've read it lots of places (of reasonable authority), so Google will help you get proof.. and is my experience as well.

Cost .. any host I've seen charges a premium (of varying degrees) for a MSSQL DSN

Integration .. almost all opensource web applications use mySQL for backend .. certainly any useful ones I've tinkered with (CMS, Media, Admin, Email apps).

Like BK says, people do it .. and anyone I know that has done so, have not been ill-informed IMO.

.cg

RicardoSmith

Maybe on low end websites/databases and limited hardware. Any reports I've read that Ms SQL is faster with complex databases with the right hardware. Then you have triggers, stored procs, views, transaction servers, etc to consider. I can't see hosting costs being a big issue on anything other than small systems.

But ultimately MS SQL is a much "bigger" product than MySQL. Its a different scale of a product. Perhaps people can buy it, realise its too much for their needs and then downsize accordingly. Theres logic to that.

pork99

How about PostgreSQL as an intermediate step between mySQL and MS SQLServer? Any experiences with that anyone?

http://www.postgresql.org/

I've no experience of using it myself btw but it is open source and seems to be a bit more enterprise level than mySQL.

RicardoSmith

Never worked with it myself.

jmcc

pork99 wrote:

How about PostgreSQL as an intermediate step between mySQL and MS SQLServer? Any experiences with that anyone?

http://www.postgresql.org/

I've no experience of using it myself btw but it is open source and seems to be a bit more enterprise level than mySQL.

PostgreSQL is real database management software unlike MySQL. It passes the ACID criteria (unlike MySQL) and is well supported. I think that Hosting365.ie offers it as an option. I am not going to get into a Holy War about MySQL vs all other software about this though. Postgres is not an intermediate step between MySQL and SQL Server but an alternative to both of them.

Regards...jmcc