![]() |
|
|
|
By: Matteo Lorini | Read Comments (7) | Related Tips: More > 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.
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
MySQL 5.x and above
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.
| Wednesday, January 13, 2010 - 10:36:10 AM - edudba | Read The Tip |
|
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. |
|
| Wednesday, January 13, 2010 - 1:38:12 PM - rjgoulet | Read The Tip |
|
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 - 5:02:10 PM - sjt003 | Read The Tip |
|
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. |
|
| Thursday, January 14, 2010 - 11:25:48 AM - --cranfield | Read The Tip |
|
Microsoft have just released a mySQL migration assistant:
|
|
| Friday, January 29, 2010 - 3:18:02 AM - DavePoole | Read The Tip |
|
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. |
|
| Friday, January 29, 2010 - 5:34:33 AM - KeithRB | Read The Tip |
|
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. |
|
| Monday, October 03, 2011 - 3:54:09 PM - Mohamed Osam | Read The Tip |
|
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.
|
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |