Friday, 4 September 2015

What are the advantages or drawbacks in choosing Microsoft SQL server over MySQL?

Microsoft SQL Server (version 2008 R2 & 2012)

Pros:

  1. It comes with lots of excellent tools like Sql Server Profiler, SQL Server Management Studio, BI tools and Database Tuning adviser. all will save you a lot of time in development and troubleshooting.
  2. You will find setting up almost everything exceptionally easy.
  3. Its one of Microsoft's premier products so it is well supported with ton of documentation and you will easily find help from MVPs
  4. SQL server has been evolving rapidly in multiple technologies, in Sql Server 2012 they introduced column based indexing which in a way is introduction to NoSQL in sql server.
  5. Sql server 2014 will be coming with memory optimized table (It should not be compared to MEMORY engine of MySQL)
  6. T-SQL remains consistent across new versions of sql server.
  7. Best XML support
  8. SQL Express edition is free and includes almost all functionality of full featured sql server with the limitation of supporting on 10 GB of Database.
  9. There are connection drivers for almost all platform to connect with it.
  10. These are some of the highlights but it has ton of features and list is too long. for further reading Data Center and Data Warehouse Capabilities

Cons:
  1. Its freaking costly. Its priced at $27,494 per processor + $8,592.00 for server + $164.00 for CAL (Client Access Licence)
  2. You have to be in Microsoft's Ecosystem to use sql server. meaning buying their Server products which would again be costly.
  3. You depend on Microsoft for any new feature and Microsoft generally ships updates in 2 years cycle.
  4. Due to now rapid development from Microsoft to get new and latest features you again have to make big investment.

MySQL ( I would be targeting 5.6)

Pros:
  1. Its Free. Though they have Enterprise Edition which is not free and is available at Yearly subscription. In most cases free edition is more than enough.
  2. You have lots of choice. Since MySQL is open source you can get many forks of MySQL. Among the popular is MariaDB which is Drop-in replacement for MySQL.
  3. Tooling support with Workbench is good
  4. phpMyAdmin is absolutely wonderful tool available for it.
  5. Version 5.6 came with Support of NoSQL
  6. Connection drivers available for most of the platforms
  7. Facebook, Google and Oracle are only some of the names among millions of others using MySQL
  8. Since its open source you can get bug fixed by yourself or community will help you get there.
  9. Oracle MySQL has very rapid production cycle
  10. Used in powering 50%+ Web sites in the world.
  11. Biggest community to help you for almost anything in Database

Cons:
  1. One of the Pros is Con also. Too many choice at times. hard to select at times.
  2. Extensive tooling support only available in Enterprise Edition
  3. Sql can have some in-consistency between different versions.
  4. Features are a bit less compared to SQL server
  5. Money saved in cost but sometimes errors are hard to troubleshoot due to lack of proper tools and more time consumed.
  6. MySQL has XML support but lacks lots of features to query over the xml column.

Overall, Both DBMS are great to work with. I have used both in production application and found sql server quite easy to work with in some scenarios.