No announcement yet.

Choosing between MS-Access, MSSQL or MySQL databases !

  • Filter
  • Time
  • Show
Clear All
new posts

  • Choosing between MS-Access, MSSQL or MySQL databases !

    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. MS Access database is more suitable for desktop use with a small number of users accessing it simultaneously. One reason you might choose to use Microsoft 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 MS Access instead of SQL Server is money. You might already have installed Microsoft 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, MS Access is limited to security in terms of username/password on the database. It also is subject to Windows Server security on the file itself (as well as the folder it resides in). Typically, ASP applications must allow the anonymous Internet guest account (IUSR_<machine_Name>) to have read/write permissions on file and folder. Username/Password access to the database cannot be controlled with any more granularity.

    Microsoft 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 retrieval process takes longer & longer...

    MS SQL Server:
    MS 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 websites hosting. You should never use MS Access for a database driven website - 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 allow companies 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 Microsoft 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 &quot;clone&quot; 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 MS Access 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 the most 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 Database Server Hosting 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...
    Rock _a.k.a._ Jack Daniel

    Follow eUKhost on Twitter || Join eUKhost Community on Facebook

  • #2
    Great summary information Rock.

    I would just add that with Microsoft producing the SQL Server Express edition as a free download/install there are even fewer reasons to use an Access DB (especially on a server) on anything other than a single-user system.

    The performance, security and occasional corruption issues associated with Access DBs can cause major headaches!


    • #3
      I'd rather walk 10 full circuits of the Brekon Beacons than use MS Access.


      • #4
        Ah ... but when you've come across someone using Excel to run a customer invoicing system Access is revolutionary!!!!


        • #5
          Thanks Rock - a useful summary (I'm just asking myself this question). I'm setting up a website hosting ( that gives information on preserved tanks around the world with information, photographs and (where available) a Google position of each tank - e.g. in a museum, a relic on a battlefield, a monument in a town centre, etc. I'm thinking a database is the way to go, so I'm currently looking to learn the basics.
          Last edited by TrevorL; 20-03-2008, 17:44.


          • #6
            Originally posted by PHyndman View Post
            Ah ... but when you've come across someone using Excel to run a customer invoicing system Access is revolutionary!!!!

            Yes, there is nothing wrong with Excel, it's great software, but it's certainly not intended to be used as a database

            MS ACCESS Database Hosting is what you should be looking for if you are using ms excel spreadsheet right now.


            • #7

              I'd be interested in hearing peoples experiences with memory usage between MSSQL and MySQL. Which would run the same database with the least server resource usage?

              At the moment I have big issues with MSSQL database hosting and it's resource intensive nature, but would love to hear if that could be resolved by migrating to MySQL database.


              • #8
                Hi Dan ... I've only run MySQL on Linux servers so it's a bit difficult to compare the two!

                My situation is that the ease of use of MSSQL database with .NET and LINQ etc.. make it my preferred option - however, if I'm doing PHP programming I'll always choose MySQL database for my hosting requirements.

                In my experience MSSQL database is more resource hungry ... but then it's doing more ... and working with an MSSQL DB using the Management Studio is sooooo much more friendly/flexible/easy etc.. than the MySQL web management options (but that's personal opinion).

                I've got a semi-dedicated Windows server specifically because the types of sites/apps I'm hosting use MSSQL and I want the resources (I've also written a work/time management/invoicing system for a local company which runs as a Intranet web with MSSQL backend on their PentiumD Small Business Server... in this situation MSSQL database consumes everything it can get it's hands on!!).

                If you're using MSSQL database then make sure you've got the resources for it, if you're programming in .NET then it's the best option (IMHO) ... if you want to use LINQ it's (currently) your only option.

                However, don't use the free version for any sizeable sites/DBs ... if cost is an issue then MySQL may be the best option (although working with it tends to be more manual ... akin to Windows vs Linux).

                A bit of a rambling rant ... hope there is some info. in there that is useful! - would be interesting to hear what other people's experiences are.


                • #9

                  Great job, but i have a small question.. for a graduation project, which do you think will be more appropriate, access or Mysql?? , taking into consideration that we are running out of time!


                  • #10
                    Considering that you are running out of time MySql database would always be better than Acesss database . The Performance of MySql database is many times better than access database.

                    System Administrator.


                    • #11

                      okay, thank you!


                      • #12

                        I'm working now on xampp , i made a database of 3 tables , now i need to link between them for example i have the user name common in the 3 of them so i wanna link them together , how can i do that?