Microsoft SQL Server vs. MySQL

By:   |   Updated: 2010-01-13   |   Comments (7)   |   Related: > Other Database Platforms


Problem

Is Microsoft SQL Server superior to MySQL or not? What are the pros and cons of using SQL Server over MySQL? Is MySQL mature enough to compete with a big player like Microsoft and Oracle? Can we fairly compare both products?

Solution

In my modest opinion there is not a simple answer to this question, because we are not comparing apples to apples. Microsoft SQL Server is a feature rich database compared to MySQL however; MySQL's goal is not to keep up with MSSQL or Oracle instead; MySQL's priorities are: reliability, performance, and ease of use.

So, in my opinion, the answer to the question "Is SQL Server superior to MySQL or not" is: it depends. It depends on the kind of application, environment and budget we have to work with.

At the core level, MySQL and SQL Server have a lot in common. The following table highlights the main ones.

  • High-Availability Clustered Database
    • SQL Server relies on Microsoft clustering technology
    • MySQL has its own share-nothing cluster storage engine (NDBCLUSTER)
  • ANSI SQL, SubQueries, Joins, Cursors, Prepared Statements
  • Stored Procedures, Triggers, SQL and User-Defined Functions
  • Updateable Views
  • ACID Transactions with Commit, Rollback
  • Distributed Transactions
  • Row-level Locking
  • Snapshot/Consistent Repeatable Reads (readers don't block writers and vice-versa)
  • Server-enforced Referential Integrity
  • Strong Data type support (Numeric, VARCHAR, BLOB, etc)
  • High-Precision Numeric Data types
  • Indexing (clustered, b-tree, hash, full-text)
  • Dynamic Memory Caches
  • Cost-Based Optimizer
  • Unicode, UTF-8
  • XML, XPath
  • Geospatial support
  • Replication
  • Table and index Partitioning
  • VLDB (terabytes) capable
  • High-speed, data load utility
  • Online Backup with Point-in-Time Recovery
  • Automatic Restart/Crash Recovery
  • Automatic Storage Management (auto-expansion, rollback management)
  • Compressed and Archive Tables
  • Information Schema/Data Dictionary
  • Security (GRANT/REVOKE, SSL, fine grained object privileges)
  • Built-in data encryption and decryption
  • Built-in Task Scheduler
  • Drivers (ODBC, JDBC, .NET, PHP, etc)
  • GUI management and development tools

According to me, with the release of SQL Server 2008, Microsoft has introduced solid and valuable features such as parallel support for partitioned objects, transparent data encryption, resource governing, automated performance data collection, change data capture (CDC), and much that is not available on any version of MySQL.

If we look as some functionalities, we can notice that both MySQL and SQL Server support GIS however; SQL Server's is more robust than MySQL's. Microsoft's job scheduler is better and easier to use then MySQL.

On the other hand, I think that MySQL provides more options for various types of partitioning: MySQL offers range, hash, key, list, and composite partitioning whereas SQL Server only offers range. Moreover; I find MySQL's partitioning much easier to use as it's defined right with the table via DDL during creation time vs. creating partitioning objects in SQL Server (partition schemes and functions) that are then applied to tables. 

/* MySQL Sample of Range partition */ 
CREATE TABLE employees ( 
id INT NOT NULL, 
fname VARCHAR(30), 
lname VARCHAR(30), 
hired DATE NOT NULL DEFAULT '1970-01-01', 
separated DATE NOT NULL DEFAULT '9999-12-31', 
job_code INT NOT NULL, 
store_id INT NOT NULL 
) 
PARTITION BY RANGE (store_id) ( 
PARTITION p0 VALUES LESS THAN (6), 
PARTITION p1 VALUES LESS THAN (11), 
PARTITION p2 VALUES LESS THAN (16), 
PARTITION p3 VALUES LESS THAN (21)); 
/* SQL Server Sample of Range partition schema maps all partition to same filegroup */
CREATE PARTITION FUNCTION myRangePF3 (int) 
AS RANGE LEFT FOR VALUES (6, 11, 16, 21); 
GO 
CREATE PARTITION SCHEME myRangePS3 
AS PARTITION myRangePF3 
ALL TO (‘primary'); 
GO 
CREATE TABLE employees ( 
id INT NOT NULL, 
fname VARCHAR(30), 
lname VARCHAR(30), 
hired DATE NOT NULL DEFAULT '1970-01-01', 
separated DATE NOT NULL DEFAULT '9999-12-31', 
job_code INT NOT NULL, 
store_id INT NOT NULL 
) ON myRangePS3(store_id);

Below is an example of a List Partition (only available on MySQL). The main difference with Range Partition is that, in list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists, rather than in one of a set of contiguous ranges of values. 

/* MySQL List partition */ 
CREATE TABLE employees ( 
id INT NOT NULL, 
fname VARCHAR(30), 
lname VARCHAR(30), 
hired DATE NOT NULL DEFAULT '1970-01-01', 
separated DATE NOT NULL DEFAULT '9999-12-31', 
job_code INT, 
store_id INT 
) 
PARTITION BY LIST(store_id) ( 
PARTITION pNorth VALUES IN (3,5,6,9,17), 
PARTITION pEast VALUES IN (1,2,10,11,19,20), 
PARTITION pWest VALUES IN (4,12,13,14,18), 
PARTITION pCentral VALUES IN (7,8,15,16) 
);

SQL Server security is way superior and more robust than MySQL. With MySQL you cannot define roles or rely on external authentication (OS Authentication). You can only restrict access based on user name and source IP moreover; auditing abilities are quite weak compared to SQL Server..

At this point it seems clear that SQL Server has more features than MySQL however; it is important to pay attention to a research study done by Forrester Research, which states that 80% of the current database installations only make use of around 30% of the vendor's feature set. So, before we pick a database platform we should ask ourselves what feature and database characteristics are needed for the specific business.

I would like to conclude this tip, by listing the feature that I have used and that I like the most on both database platforms:

Microsoft SQL Server 2005/2008

  • DMV
  • Database Mirroring
  • Database snapshot
  • Extended Events
  • Auditing
  • Transparent data Encryption
  • Change data capture (CDC)
  • Resource Governor

MySQL 5.x and above

  • Ability to run on multiple OS
  • Share Nothing Cluster
  • Easy to Scale Out on commodity hardware
  • MySQL Proxy
  • Replication
  • Multiple Storage Engines
Next Steps

I think that both database technologies are ready and mature enough to be used in real world production environments. In my opinion, the adoption of one over the other technology depends on the available budget and, most of all, business needs and SLA requirements. I believe that, if we have a good general database design and implementation, we can take full advantage of both technologies and use them where they are more appropriate and suitable.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2010-01-13

Comments For This Article




Monday, October 3, 2011 - 3:54:09 PM - Mohamed Osam Back To Top (14767)

Although I tend to agree with some of the comments in regard to what MySQL was originally created for, I am more concerned now on how both servers (MS-SQL vs. MySQL) perform when compared head-to-head, doesn't really matter why they were created IMO.

 

Both servers gone through a lot of enhancements, I have been working with MS-SQL since it was first acquired by MS from Sybase, and feature wise, lol, don’t even get me started there, it was nothing compared to what it is now. The jump from SQL 2k to 2008 is also considerable; the notorious problem of table locking in MS-SQL 2k is enough to consider 2008 performance considerably superior to its predecessors.

On the MySQL side, Oracle would have never bought it if it wasn’t being threatened by its spread in the Internet community. I have to point out though that no matter what super-doper DB engine you use, a poor table index design on the DBA side can render your database worst of the worst compared to any other available engine.

I have recently conducted a MySQL vs. Ms-SQL performance test (check it out here), that is not by any means scientific, but at least it did the job for the case I had in hand. It shows that no matter what engine you use, performance can significantly vary depending on table structure, db size, etc.

 

Friday, January 29, 2010 - 5:34:33 AM - KeithRB Back To Top (4819)

Last time I looked at MySQL I was a bit surprised to find that it would discard data with no warning or error. This was using the InnoDB. Mind you it was a few months ago so things might have changed now.

Friday, January 29, 2010 - 3:18:02 AM - DavePoole Back To Top (4818)

 You have to separate the marketing bumpf from the reality.

 The guys who wrote MySQL will tell you that it was never designed for the purpose that a lot of people are now using it for. One engineer told me that anyone pushing beyond 200GB in a single database instance was going beyond the intended capacity for the system.  MySQL focuses on scale-out DB designs to achieve huge sizes with realistic performance.

 

MySQL Cluster is an in-memory share nothing engine that requires a specialised design approach.  If your query can satisfy data from the same node in the cluster then it is very fast.  If your data is spread across the nodes in the cluster then performance is poor.

You have to design for the shared-nothing architecture.  Nice work by Johann Anderson though to have built such a system.  It's brilliant for key value lookups and, having been written specifically for Ericson, it is extremely useful in the telecoms industry.

 MySQL was designed for small to medium web sites and it is very good at servicing these requirements.  Factor in the cost and for a small business startup it is a very attractive proposition.

 In terms of performance if you install MySQL and SQL 2008 on identical hardware and Windows2008 then SQL2008 wins hands down.  MySQL can get to about 90% of the performance only after a lot of tuning by people who know what they are doing.

That said do you really need the performance?  It is like comparing a Ford Focus with a Ferrari.  How many people can drive a Ford Focus to its limits, much less a Ferrari?

Stored procedures in MySQL are simply parameterised units of code.  There is no concept of a cached execution plan.

Foreign Key support in MySQL works in InnoDB but it has a performance hit due to the pluggable engine architecture and the layer in which FK support is executed.

 There are no check constraints in MySQL.

Without clever design I think you will outgrow MySQL and start looking at one of the big boys in the DB industry.  If you are a Windows shop then MS SQL is an obvious choice.

If you are one of the *nix guys then there are a plethora of choices.


Thursday, January 14, 2010 - 11:25:48 AM - --cranfield Back To Top (4714)

Microsoft have just released a mySQL migration assistant:

 

http://www.microsoft.com/downloads/details.aspx?FamilyID=0e6168b0-2d0c-4076-96c2-60bd25294a8e&displaylang=en

Wednesday, January 13, 2010 - 5:02:10 PM - sjt003 Back To Top (4709)

The article focused entirely on administrative functions, some of them not particularly important to me as part of a small company with relatively few transactions.  There are a lot of major programming and analytical features that weren't even touched on that I find very useful, such as CLR integration, Analysis Services, Reporting Services, etc.  Nor were differences between the latest T-SQL and whatever MySQL uses (hey, I know little about MySQL) discussed.

Wednesday, January 13, 2010 - 1:38:12 PM - rjgoulet Back To Top (4708)

One should also note that MySQL is only ACID compliant when the INNOUDB option is employed.  Otherwise it is not ACID compliant as any change made to the database is immediately visible to anyone else.  MySql's intent is to be fastest on data retrival thereby making inserts, updates, and deletes slower.  Even though I'm no fan of MSSQL I feel it is a superior product by far to MySQL.  And even MySQL's dual licensing model is confusing to beat the band.  Lets face it your either Open Source of Not.  In this case MSSQL presents a better defined model.  Actually if you want a product in the open source world to compare MSSQL with try PostGreSql.

Wednesday, January 13, 2010 - 10:36:10 AM - edudba Back To Top (4706)

Your equality of MSSQL and MySQL backups is misleading. MSSQL backups can be easily made and scheduled. They can also easily be compressed, have old copies removed after some rotation period. MSSQL has 3rd party plugins (Backup Exec, DPM...) for backup that can easily integrate with file system backups. Backups in MySQL have to be scripted and scheduled with another package.

Also MySQL's analytical capabilities are non-existant. Most of the other big players have an OLAP engine - MySQL has none. 

Having used all the big databases, (Sybase, Oracle, MSSQL, DB2, Informix, MySQL) I can say without a doubt that MySQL is very useable, but not at all comparable to the big guys. MySQL's best use is in a shop where you have infinite programmer skills and no DBA's. It is cheap, but you will pay for its cheapness when you have to have script every single administrative task.

Given MySQL's shakey future, I would not recommend it for large shops.














get free sql tips
agree to terms