Results 1 to 12 of 12

Thread: Choosing between MS-Access, MSSQL or MySQL databases !

  1. #1
    Rock's Avatar
    Rock is offline Technical Support (eUKhost.com)
    Join Date
    Oct 2006
    Location
    localhost
    Posts
    3,771

    Lightbulb 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
    eUK Windows Hosting || Windows Reseller Hosting
    Cloud Hosting 100% UPTIME! || Powerful Dedicated Servers
    Follow eUKhost on Twitter || Join eUKhost Community on Facebook

    For complaints, grievances or suggestions kindly email our FeedBack Dept.
    Proper action will be taken accordingly & instantaneously!

  2. #2
    PHyndman's Avatar
    PHyndman is offline Forum Member
    Join Date
    Feb 2008
    Posts
    45

    Default

    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. #3
    WelshTom is offline Forum Member
    Join Date
    May 2007
    Location
    Newport, Wales
    Posts
    998

    Default

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

  4. #4
    PHyndman's Avatar
    PHyndman is offline Forum Member
    Join Date
    Feb 2008
    Posts
    45

    Default

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


  5. #5
    TrevorL's Avatar
    TrevorL is offline Forum Member
    Join Date
    Feb 2008
    Location
    Northampton, England
    Posts
    28

    Default

    Thanks Rock - a useful summary (I'm just asking myself this question). I'm setting up a website hosting (PreservedTanks.com) 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 at 17:44.

  6. #6
    WelshTom is offline Forum Member
    Join Date
    May 2007
    Location
    Newport, Wales
    Posts
    998

    Default

    Quote 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. #7
    danwednesday is offline Forum Member
    Join Date
    Nov 2007
    Posts
    48

    Default

    Hi,

    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. #8
    PHyndman's Avatar
    PHyndman is offline Forum Member
    Join Date
    Feb 2008
    Posts
    45

    Default

    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. #9
    nohabadr is offline Forum Member
    Join Date
    Oct 2011
    Posts
    3

    Default Help!

    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. #10
    euk-paul is offline Employee
    Join Date
    Nov 2008
    Posts
    62

    Smile

    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.

    Euk-Paul
    System Administrator.

  11. #11
    nohabadr is offline Forum Member
    Join Date
    Oct 2011
    Posts
    3

    Default :)

    okay, thank you!

  12. #12
    nohabadr is offline Forum Member
    Join Date
    Oct 2011
    Posts
    3

    Default help!

    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?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About eUKhost
We're a Leeds-based web hosting provider specialising in Linux- and Windows-based hosting solutions now serving over 35,000 customers worldwide. Across our network, we host over 2 million websites in our state-of-the-art data centres in Reading, Maidenhead and Milton Keynes. Find out more.
we're social

Copyright © 2014. eUKhost Ltd. All rights are reserved.

The opinions or views of users on the forum are those of the author and not of eUKhost Ltd.