Choosing between MS-Access, MSSQL or MySQL databases !

[Rock]

What database is better for your website hosting: MS-Access, MSSQL or MySQL ?

Now that you've designed a database driven website hosting, you'd need a database to save data or query/fetch certain records.
How flexible do you need your data to be? How secure must your data be? Do you need support in utilizing your data management tools? These are all questions that you will have to answer for yourself. And in answering them, you will know which data management tool will be best suited for your personal, business or corporate needs. Here's a bit of most common differences or rather a check-guide on whether to use Microsoft Access, MySQL or MSSQL database Server for a website hosting:

MS Access:
______________________________
There are some very good reasons why you wouldn't use this type of database in some cases. Access is more suited for desktop use with a small number of users accessing it simultaneously. One reason you might choose to use Access over SQL Server is for compatibility/sharing. You might need to email someone a copy of your database. People are more likely to have Access on their desktop computer than SQL Server. You'll generally only find SQL Server on developers' computers/servers or on production server machines. Another reason you might use Access instead of SQL Server is money. You might already have installed Access as part of the Microsoft Office suite. Purchasing SQL Server would be an extra expense that may not be necessary - depending on your situation. SQL Server can also be quite expensive. To add futher, Access is used as local database & can't be accessed through other servers or rather from any remote location. In short it has a front-end GUI system [MS-Office] to design applications quickly but locally. You can also design/modify the Access database on your local machine & upload it to the web server where the web site hosting exists, but this becomes a tiresome job when the DB size increases gradually.

When it comes to security, Access is limited to security in terms of username/password on the database. It also is subject to Windows security on the file itself (as well as the folder it resides in). Typically, ASP applications must allow the anonymous Internet guest account (IUSR_) to have read/write permissions on file and folder. Username/Password access to the database cannot be controlled with any more granularity.

Access is mostly used & rather made for novices in DB programming. MySQL/MSSQL are for non-novice, more professional, kind of people. Actually MSAccess is introduced by Microsoft for the small offices who want to have a small DB of employees, etc. As you put more & more data into the access file, the performance of the application reduces hence making the data retrival process take longer & longer...

MS SQL Server:
______________________________
SQL Server is a server based database & is a more robust database management system. It was designed to have many hundreds, or even thousands of users accessing it at any point in time. Microsoft Access on the other hand, doesn't handle this type of load very well. This makes SQL Server perfectly suited for database driven website hostings. You should never use Access for a database driven website hosting - unless it has a very small amount of traffic. Even then, you may find yourself getting errors due to multiple users trying to access the database at the same time. There is no front-end GUI system so it will require other development tools (Visual Studios, .NET, VB, C++, etc..etc...)

SQL Server also contains some advanced database administration tools that enable organisations to schedule tasks, receive alerts, optimize databases, configure security accounts/roles, transfer data between other dissimilar sources, and much more.

SQL Server has two authentication modes, and neither are much like Access security at all. You can use Windows Authentication, which allows you direct access to domain Users and Groups from within the interface. You can also use Mixed Mode, which allows MSSQL Server to maintain usernames and passwords (thereby negating the need for a domain or other Windows user/group maintenance).
Once you have determined an authentication mode, users have three different levels of access into the database: login (at the server level), user (at the database level), and object permissions within each database (for tables, views, stored procedures, etc). Just to add a layer of complexity, MSSQL Server makes it easy to "clone" users by defining server-wide roles, and adding users to that role. This is much like a Group in a Windows domain; in MSSQL Server, you can use the built-in definitions (and customize them), or create your own. Alterations to a role's permissions affect all users that are members of that role.

If you are using MSAccess database, you won't be able to connect to it from another machine/server without mapping the drive. But in the case of MySQL or MSSQL you can have the database server in HongKong & the application using it being run from the UK & that is the major difference. The main draw back of the MSAccess DB is that it is a single file & if the file is corrupted, you'd be in serious trouble & as the file cannot hold enormous amounts of data, its not possible to take differential backups of the same.


MySQL: Finally
______________________________
The following features are implemented by MySQL but not by some other RDBMS software:

MySQL has multiple storage engines, which allows you to choose the one which is most effective for each table in the application:
In MySQL 5.0 or earlier, storage engines used to be compiled in while in MySQL 5.1, storage engines can be dynamically loaded at run time:

MySQL is famous or rather more loved amongst the professional folks because of it's multiple choice in custom storage engines. You can have custom storage engines developed in MySQL based on your requirements or choose amongst the inbuilt ones.
The most commom native storage engines include MyISAM, Falcon, Merge, Memory (heap), Federated, Archive, CSV, Blackhole, Cluster, BDB, EXAMPLE & Maria which are developed by MYSQL developers themselves. While InnoDB, solidDB, NitroEDB, BrightHouse are examples of partner-developed storage engines.
There are several communities all over the world developing into MySQL to create their customized storage engines, such storage engines are termed as community-developed storage engines, examples of their works include memcached, httpd & PBXT.
MySQL allows to to commit grouping, gathering multiple transactions from multiple connections together to increase the number of commits per second.

Comparing between MSSQL & MySQL for performance is a bit difficult task The database provider doesn't really play a key role in the performance factor in between these two database types but it's the experience of the database developers & DBA which make it perform better than each other. You can use both of these RDBMS to build a stable & an efficient system.

However, it is possible to define the typical transactions, which used in inventory control systems, airline reservation systems & banking systems. After defining these typical transactions, it is possible to run them under the different database management systems working on the different hardware & software platforms.

From a database developer's point-of-view, the choice is very clear. MSSQL Server or MySQL are the most sensible choices because of its rich features in manipulating, securing and managing data. Also, from a developer's stand point, MySQL's lack of support for the basic database features mean that development of an application to interface with the database will be both more costly and take longer to finalize. Too much code must be written in the user interface to manipulate the data first, before sending that data to the database. All this extra code costs time and money to develop and maintain.
You need to make the decision in choosing the appropriate database based on your website hosting's requirement. If you do think you need to upgrade to SQL Server, there are several tools available all over the internet to get this transformation done quickly & flawlessly. Finally the decision is yours...