Advantages and Disadvantages to Using MySQL Vs MS SQL

Two of the most popular database systems used by web developers today are MySQL and Microsoft's MS SQL server. Both are similar in regards to being storage and retrieval systems. The two systems support primary keys, along with key indices which allow you to speed up queries and constrain input. Furthermore, both systems offer some form of support for XML.

Apart from price, which is the obvious difference, what distinguishes these two systems, and which one is right for you? We'll overview both products, point out the major differences and explain the advantages and disadvantages of using them for your business.

Open-source vs. Proprietary

When it comes to these two databases, the differences begin with the open-source nature of MySQL vs. the closed, proprietary structure of the SQL Server. MySQL is an extensible, open storage database engine, offering multiple variations such as Berkeley DB, InnoDB, Heap and MyISAM. On the other hand, with the Microsoft product, you are limited to a Sybase-derived engine through both the good and bad times.

When considering how MySQL integrates seamlessly with a number of programming languages and other web-based technologies, it certainly has the advantage over MS SQL in the way of compatibility, as the SQL Server is known to work better with other Microsoft products.

Licensing

Contrary to popular belief, the MySQL system isn't always free. On the other hand, it is always more affordable. In regard to both products, licensing fees are based on a two-tiered scheme. With MS SQL, the best way to obtain a developer's license is to buy a license for the Microsoft Developer or Microsoft Visual Studio suite. Both provide you with a free SQL Server license for development use. If you want to use the product in a commercial environment, you need to at least purchase the SQL Server Standard Edition - which could set you back over $1,000 for a few client connections.

Because MySQL is an open-source system under the GNU General Public License, developers can use it at no cost as long as the associated projects are also open-source. However, if you intend to sell your software as a proprietary product, you would need to purchase a commercial license, which costs about $400 for up to nine clients. Depending on the project and your funds, MySQL may have the advantage here.

Technical Differences

The open-source vs. proprietary battle alone is a leading cause why some users choose one system over the other. However, there are a few differences from a technical aspect as well.

For instance, MySQL doesn't offer full support for foreign keys, meaning it doesn't have all the relational features of MS SQL, which is considered a complete relational database. Some versions of MySQL also lack full support for stored procedures - the biggest disadvantage being the MyISAM system, which doesn't support transactions.

Performance

In the way of performance, MySQL is the clear leader, mainly due to the format of its default table, MyISAM. MyISAM databases leave a small footprint using little disk space, memory and CPU. While the system runs on the Windows platform without flaw, it tends to perform better on Linux and other UNIX-like systems. Because of its stability, many internet powerhouses such as Yahoo! use MySQL as their back-end database.

When it comes to performance, MS SQL's strength of being packed with more features than other systems is perhaps its biggest disadvantage. Although most of these features are designed for performance tuning, they tend to sacrifice other essential elements. The cost here is complexity and the hogging of resources in the way of storage and memory, which leads to poorer performance. If you lack the knowledge and sufficient hardware to support an SQL server, you would be better off with another database management system.

Security

These two database systems are pretty much deadlocked in regards to security. Both come with adequate security mechanisms by default, bearing you follow the directions and keep the software updated with security patches. Both operate over known IP ports which unfortunately attracts a wealth of intruders, a downside that can be attributed to both products. The good thing is that MySQL and MS SQL allow you to change ports just in case the default becomes too vulnerable.

Recovery

As far as recovery goes, the SQL Server has a definite advantage over MySQL, which tends to fall a little short with its MyISAM configuration. A UPS system is mandatory with MyISAM as it assumes uninterrupted operation. If a power outage should occur, it could result in the corruption and loss of critical data. With the SQL Server, data corruption is more unlikely. The data travels through various checkpoints while passing from your keyboard to the hard disk and through the monitor. Additionally, the SQL Server keeps track of the process, even if the system unexpectedly shuts down.

The Best Choice

As you can see, both systems have their advantages and disadvantages. From our perspective, any product that allows you to be efficient is a good database; anything other than that isn't worthy of your time and frustration. When it comes to MySQL and MS SQL, the decision all boils down to the situation and most importantly, what you're looking to accomplish.

 

Nice post, very informative

I've been using MySQL for some time now and think it's great - not having used MS SQL I wouldn't know one way or the other but it's a Microsoft product so that's enough of a reason for me!

 

You can use SQL Server Express editions which have pretty much 90% of the features of the paid for versions including full text search and reporting services and have some pretty generous limits, its main one is a max 4GB database size, stick to those and its completely free even for commercial use.

I've used both SQL Server (Express and full commercial) and MySQL and I would never choose MySQL ahead of SQL Server unless it had to run on Linux.

If your looking for an open source DB solution have a look at PostgreSQL or Firebird instead both are more enterprise level solutions than MySQL.

Jen

 

Is this your article? I also found it here: http://ezinearticles.com/?Advantages-and-Disadvantages-to-Using-MySQL-Vs-MS-SQL&id=1559158. Why no credit?

Your article is biased towards mysql. Here is the problem in your reasoning: You assume you need the developer license for every project you make and a stardard edition SQL Server for deployment. That is not true. SQL Server Express is FREE to use for both development and commercial environments. It has some limitations like a max 4Gb database, and no replication support but normally you don't need that (I hope you don't think the free mysql version does). If you do means you are working for a huge company that uses clusters of servers and you definitely have the money to pay for the license.

400$ for a mysql license sounds a lot, when I can use a free alternative that comes with high quality GUI sql client too.

 

I dumped all Windows products back in 2003 and couldn't be happier.

 

No-brainer, who uses MSSQL any more? Whoever uses windows products to create a website is asking for trouble.

>These two database systems are pretty much deadlocked in regards to security.

This is complete rubbish, microsoft's products have more holes than a t-bag. Only the other day a 0-day exploit was found on MsSQL.

MyISAM recovers fine, and is more resilent than MSSQL in my experience.

MSSQL is a lot slower than MySQL as well, as you stated BIG TIME.

Btw, this post looks like some article thats been posted to a forum.. hmm..

As for backups, mysqldump is excellent, trying to backup MSSQL is a nightmare.

 

Do you have any arguments to support this claim? Our company uses ASP.NET successfully and so are thousands others.

You actually made me curious so I've done some research. You are referring to a vulnerability made public on 17th dec regarding a heap overflow in sp_replwritetovarbin. I'd hate to tell you but it's only relevant for MSSQL 2000 with NO service packs and a windows version older then 2003. Anyone using such an old version with no updates is asking for trouble the same way if he was using mysql 2.0.

actually it's the other way around. MSSQL allways recovers right because of the journaling system (the very same system that make it slow). This is where the tradeoff was made. Safety for speed. MyISAM recovers terribly. You need to keep reading on this matter.

bigtime is an exaggeration. A bit slower, yes. I've explained you why above.

Wrong again. MSSQL doesn't need any 3rd party tools for backups. Running the "backup" command will do just file and can be scheduled. If you prefer to do it by GUI nothing stops you from using SQL Server Studio Management Express which is a free tool from Microsoft. Login, right click on the DB, Tasks, Backup. Simple as that. Where is the nightmare?

The only reason I'm defending MSSQL is because I work with it on a daily bases. I know what the weak points are and you haven't touched them. Next time if you decide to bash a technology come with arguments. "I hate M$" doesn't count as argument!