Compare Access, SQL, MySQL, etc...

Can anyone refer me to a comparison chart or list of features for Access, MS-SQL, MySQL and MSDE so I can determine which database type is most appropriate for each web application? I know Access has a limit of concurrent users and is slower to serve data that Access but I know nothing more… and even less about MySQL and MSDE.

Also, any links to good beginners tutorials for SQL, MySQL or MSDE to get me started? I always find Microsoft sites difficult to follow (maybe it's just me?).

-----------------------------------------
Bit more info…

I’m not a confident ASP programmer so I’ve just developed a website based on an Access database with the intention to convert to SQL at a later stage – never having developed an SQL database before.

I’m running Server 2003 and I’ve only just installed MSDE – however I have no idea how to even get started so I’m now considering installing SQL Server 2000 because I’ve used enterprise manager a few times before, so I thought it would at least be a start!

My web server also runs on Server 2003 but there is a limit to how many SQL databases I can host. Whilst some web applications might require SQL, for others it’s probably a bit of overkill anyway.

I’m most interested in SQL so I can schedule automatic backups and ensure plenty of concurrent users can access the site.

Any help would be much appreciated!!!

Here is my demo site that runs on Access: www.websharp.com.au/demo
Pretty much all of the content is database driven.

Access may work for a small site that gets little traffic, but it is designed for desktop use. To say the least, I don't recommend it for Internet use. MSDE may work for you, but it's harder to manage and setup tables. You have to do everything through command-line. MSDE is a scaled down version of SQL Server. SQL Server and mySQL will work fine for Internet sites. However, I have found that SQL Server is the easiest to setup and manage, especially since you want to schedule jobs and that sort of thing.

Hope this helps.
D.

Access is a file-based database and works quite well for low traffic websites. I have a small access-driven asp website that has run without a single hiccup for over 3 years now.

MSDE has the identical engine as SQL Server, but there is some intentional limiting to the number of simultaneous transactions it will support (otherwise why would anyone buy sql server?). MSDE does not include any client administration or other tools, so you need to use a command-line administration tool that's included or find some other gui tools to administer your db.

MySQL is similar to MySQL server, but not quite as full-featured. MS SQL Server and MySQL Server are suitable for heavy traffic websites. MSDE and Access are more suitable to low traffic websites.

The web server and what's available there will have a lot to do with deciding what db you should use.

Thanks guys!

This is probably a stupid question, but how do I setup a MySQL database? Do I just use Enterprise Manager as I would with SQL? I've never setup a SQL database from scratch - the most I've done is played around with an existing database in EM, so forgive me for asking naive questions.

I think I'm going to look at MySQL because there is no limit to how many database I can run on my webserver (unlike SQL). The database isn't huge (a dozen tables?) and the sites are only expected to have light to moderate traffic - are there any resource concerns with MySQL in this case? What is the real dif between SQL and MySQL, ie: max no of consecutive users, max no of records per table, etc.. ???

Cheers,
Penny

You can't use Microsoft's enterprise manager to setup a MySQL database.

If you are dead-set on using a gui interface to administer your db system, you should look at SQL 2005 Express from Microsoft.

It is MS SQL Server 2005, stripped down much like MSDE was in comparison to 2000. The big difference between it and MSDE is that it includes a management gui. It still limits its concurrent connections, but the storage and table/constraint/storedproc complexity is all there if you want or need it. You can even partition individual tables horizontally to separate file groups, which is a fantastic feature for anyone who runs heavily-hit, constant-append, monotonous transactions like a web storefront.

Chances are, your website is not going to have 10 people on it simultaneously try to post to your database. I'm not saying you won't have 10 simultaneous viewers of your website, but 10 simultaneous DB transactions. SQL 2005 Express is perfect for you, and you'll even be on the bleeding edge of what's new.

Basically, your differences you want are:

MSSQL 2000/2005: Great management tools, very powerful, scalable, fault tolerant, distributable, but expensive.

MySQL: Almost as powerful as MSSQL to a reasonably studious not-quite-newbie (like me), perhaps more powerful to a full-fledged software designer (not like me), somewhat scalable and distributable, and free.

MSDE/SQL Express: Free, microsoft compatible if you need to upgrade later on (and don't want to change your front-end code to target a different language for your DBMS), SQL Express comes with decent management tools, but will bog down if hit with more than 10 transactions at a time, to where it can become unreasonably slow to commit transactions at a certain point.

visit www.mysql.com and tour around. All the instructions for working with mysql db servers can be found there, along with their equivalent to enterprise manager and query analyzer (look in the developer zone).