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.

• Different strategies for removing duplicate records in SQL Server
• Determine Free Disk Space in SQL Server with T-SQL Code
• Determine Free Space, Consumed Space and Total Space Allocated for Microsoft SQL Server databases
• Simple way to find errors in SQL Server error log
• Capacity Planning for SQL Server 2000 Database Storage
• More...

• Building a Data Mart with Integration Services

• Discuss this tip, post comments and feedback