Why Move to MySQL from Microsoft SQL Server?

Before we get started, let me say that I always liked being a SQL Server DBA. My database experience started with DB2, then Teradata, followed by Oracle, and then SQL Server (and then a little bit of Sybase after that, followed by MySQL). Coming from the other databases, I found SQL Server the easiest and quickest to learn at the time, but of course, a lot of that had to do with the fact that Microsoft was really the only database vendor around then (I started with version 4.2 of SQL Server) that shipped any decent GUI management tools with their server. Take the tools away, and you basically had Sybase on Windows with the ISQL command line tool, which was not pretty by any means.

I started managing a lot more database servers with SQL Server 6.0, 6.5, and then SQL Server 2000. I was doing a lot of Oracle at the time too, but still felt that SQL Server had an edge on Oracle in terms of ease-of use. Oracle’s done a lot to improve their tools and overall manageability over time and today they are way ahead of where they were in the version 8/9i days with their 10/11g tools, but Microsoft has kept moving forward too. SQL Server 2005 came along with a little more help in the area of management, but it wasn’t as much as in prior versions. The same can be said (in my opinion) of SQL Server 2008 in terms of additional management help. But don’t misunderstand me, in my DBA days, SQL Server was still the easiest to use over all its competitors and I never thought I’d find another database server that could equal it in terms of having the total package of ease-of-use, reliability, and good performance.

But then I found MySQL.

Of course, you can say I’m biased because I oversee product management for MySQL inside of Sun, so guilty as charged there. But I do think MySQL has SQL Server beat in many respects, even when it comes to running on the Windows platform. Moreover, a lot of other folks seem to agree. When it comes to migrating away from another database to MySQL – or using another database for new applications – the #1 database platform is Microsoft, three years running according to our year-end surveys.

So why is this the case? Why should you, if you are using SQL Server today, consider moving your current applications to MySQL or use MySQL instead of SQL Server for new applications? In an article of this size, I can’t possibly cover every factor or consideration in detail, but I can try and hit the high points which hopefully will be enough. Let’s begin…

MySQL on Windows? Absolutely!

Almost two years ago, I wrote an article entitled “MySQL on Windows? Absolutely!”, where I talked about how prevalent MySQL is on Windows and why Microsoft is a great platform for our database. To date, that article has been the one I have received the most comments/emails on, and every single one was positive – all those who wrote me confirmed the reliability and high-performance characteristics of the MySQL/Windows combination.

Well, two years later, the same is still true. In terms of downloads, Windows far exceeds any other platform for the MySQL Server. For example, here are some download statistics from April 2008 to April 2009 (which includes all server versions from 5.0 on up):

Now some argue and say that the Windows platform sees such a huge increase over our other supported platforms because developers/DBAs download and develop on Windows and then move to Linux or another platform for production. And this is certainly a valid point. However, in our most recent global survey, we asked the question what production platform people use for their MySQL database, and for MySQL’s enterprise paying customers, 54% said they used MySQL/Windows for development purposes, but 32% said they deploy production MySQL databases on the Microsoft platform – the 2nd most popular platform (RHEL was #1). Perhaps more surprising, the MySQL Community stated they use Windows for development 65% of the time and deploy MySQL production databases on Windows in 44% of their roll-outs, making it the number one platform for both development and production. You can see a general online quick poll we did on this subject here: http://dev.mysql.com/tech-resources/quickpolls/primary-os.html.

The point is, if you’re sold on the Microsoft platform for your data center or various stand-alone systems, then you’ve got plenty of company in using MySQL on Windows. And, of course, unlike SQL Server, if you want to move to Linux, Solaris, or other operating systems with MySQL, you certainly can.

Installing and Configuring MySQL vs. SQL Server

Although installing software isn’t a major factor when looking at databases, it can still come into play if you have many servers that you commission and upgrade. The last SQL Server 2008 Enterprise download I utilized was 1.6GB in size with another download required for a new .NET framework install, which ironically, was the same size as the full MySQL 5.1 GA install for Windows – 150MB. I can install MySQL on my WIN boxes and be at a MySQL command utility prompt in under 5 minutes, but a SQL Server install takes much longer (with your mileage varying depending on the server you use). Just the .NET framework install took more than 5 times the installation time of the MySQL Server on one of my test machines.

But as I said above, installation isn’t a big consideration with databases. And some may think that they small size of the MySQL WIN download is indicative of the fact that the database server isn’t very feature rich and therefore that’s why it’s so tiny. We’ll more fully dispel that myth later in this article, but suffice it to say that such an assertion is false. Good things do come in small packages, as they say. One of my favorite examples to illustrate this point is one of our partner storage engines – InfoBright. Their MySQL-based data warehouse engine is only a 17MB download, installs in about 3 minutes, and can mange up to 30TB of data on a single server with incredibly fast response times.

Another install and configuration consideration is having multiple instances of a database server on one machine. This is another area where I find MySQL easier to use/manage than SQL Server. For me, it’s a cakewalk to have multiple instances of either the same version of MySQL or different versions of MySQL on the same box. All that’s basically required from a ‘tweaking’ standpoint is that you (a) install different versions of MySQL in different directories and, (b) use a different connection port (MySQL’s normal default port is 3306) for each MySQL install if and only if you want to run multiple instances of the database server at the same time.

I also like the fact that I can have a full-featured MySQL Server install on my WIN XP machines without having to use Windows Server like is sometimes required for various versions of SQL Server Enterprise.

Lastly, when it comes to configuration, most SQL Server DBAs set their config parms via the SQL Server Management Studio whereas MySQL DBAs manually edit the my.cnf file. Note that, like SQL Server, most of the config parms for MySQL are dynamic and can be set/immediately changed via a SET GLOBAL statement at a MySQL command line prompt.

Comparing Core SQL Server and MySQL Features

To me, the release of SQL Server 2008 was more impressive from a new feature introduction standpoint than SQL 2005, although 2005 did have some very welcome new enhancements such as Microsoft’s first cut of table and index partitioning. But SQL 2008 brought out some solid features I find very compelling such as parallel support for partitioned objects, transparent data encryption, resource governing, automated performance data collection, change data capture (CDC), and more.

With MySQL, we never engage in database feature wars because that’s not our goal – adding features and becoming as large as Microsoft or Oracle in that regard is not what we’re about. Our three priorities are reliability, performance, and ease-of use, with features only being added when we believe they will strengthen those main concerns. Now that said, there certainly are times when various esoteric database features are necessary for an application, but those times are often the exception rather than the rule with even Forrester Research stating that 80% of the current database installations only make use of around 30% of the vendor’s feature set. I can echo that conclusion from my personal experience in using SQL Server – rare was it that I actually used some of the more boutique features of the database server; most times it was the core RDBMS feature set that powered my applications.

Now you may think I’m setting you up for a letdown with the previous paragraph – that MySQL isn’t going to deliver when it comes to the features you’ll need to move from SQL Server, but you’d be mostly wrong. When I first began transitioning some work from SQL Server to MySQL a number of years ago, I was impressed with the feature set I found then in MySQL, and it has only improved since that time. If you’re unfamiliar with the MySQL feature set, I can’t possibly list everything included in MySQL in an article of this size, but here’s just a sample of how MySQL stacks up to SQL Server (latest versions of both) from a feature standpoint:

Database Feature	MySQL	SQL Server
Open Source	X
Available on two-dozen platforms (32 and 64 bit) including Windows: (RedHat, SuSE, Fedora, Solaris, HPUS, AIX, SCO, FreeBSD, Mac OS, Windows)	X
Pluggable Storage Engine Architecture (MyISAM, InnoDB, Merge, Memory, Archive, Cluster)	X
High-Availability Clustered Database	X	X
ANSI SQL, SubQueries, Joins, Cursors, Prepared Statements	X	X
Stored Procedures, Triggers, SQL and User-Defined Functions	X	X
Updateable Views	X	X
ACID Transactions with Commit, Rollback	X	X
Distributed Transactions	X	X
Row-level Locking	X	X
Snapshot/Consistent Repeatable Reads (readers don’t block writers and vice-versa)	X	X
Server-enforced Referential Integrity	X	X
Strong Data type support (Numeric, VARCHAR, BLOB, etc)	X	X
High-Precision Numeric Data types	X	X
Indexing (clustered, b-tree, hash, full-text)	X	X
Dynamic Memory Caches	X	X
Unique Query Cache (stores query and result set)	X
Cost-Based Optimizer	X	X
Unicode, UTF-8	X	X
XML, XPath	X	X
Geospatial support	X	X
Replication	X	X
Table and index Partitioning	X	X
VLDB (terabytes) capable	X	X
High-speed, data load utility	X	X
Online Backup with Point-in-Time Recovery	X	X
Automatic Restart/Crash Recovery	X	X
Automatic Storage Management (auto-expansion, rollback management)	X	X
Compressed and Archive Tables	X	X
Information Schema/Data Dictionary	X	X
Security (GRANT/REVOKE, SSL, fine grained object privileges)	X	X
Built-in data encryption and decryption	X	X
Built-in Task Scheduler	X	X
Drivers (ODBC, JDBC, .NET, PHP, etc)	X	X
GUI management and development tools	X	X

Again, I don’t want to mislead you into thinking MySQL has more features than SQL Server because the truth is it doesn’t. But the features and capabilities MySQL does possess is usually more than enough to handle systems that need a strong OLTP or analytical database. But also understand that, although both SQL Server and MySQL have complementary features in many areas, there are sections where the depth of what SQL Server offers is better than MySQL. For example, both MySQL and SQL Server have GIS features, but SQL Server’s is more robust than MySQL’s. Ditto when it comes to their job scheduler vs. ours. But conversely, there are cases when MySQL rises above SQL Server in some ways, such as partitioning – SQL Server does have parallel support for partitioning, but MySQL provides more options for various types of partitioning: MySQL offers range, hash, key, list, and composite partitioning whereas SQL Server only offers range. And continuing with the example of partitioning, 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.

Of course, there are features in SQL Server MySQL has no complement for. Security is one particular area where MySQL trails Microsoft – unlike SQL Server, MySQL has no concept of roles or external authentication, and its data auditing abilities are quite weak compared to Microsoft. I could list many more features that SQL Server has over MySQL (e.g. better query optimizations and methods, transparent data encryption, etc.), but as I said earlier, MySQL isn’t about going all out with Microsoft or anyone else in the features arena.

Storage Engines

One advantage you’ll have with MySQL over SQL Server is in the area of MySQL storage engines. I can’t tell you how many customers have told me they use MySQL over other databases like SQL Server because of its pluggable storage engine architecture. When I first began looking at storage engines, I was a bit skeptical, but not anymore. As a DBA, what I like is the flexibility storage engines offer. In one database, I can have tables that are transactional or not, main memory or not, compress data or not, and on. And each storage engine supplies performance and usage advantages over using just traditional tables like you find in SQL Server.

In addition, I like that not only do we at MySQL/Sun develop storage engines, but other vendors do as well, which helps us innovate faster. We have third-party vendors that have created column-oriented data warehouse engines, OLAP engines, transactional engines, and more. Using and switching between storage engines is very easy as well. In the end, what storage engines give you over SQL Server is more choice, opportunities for higher performance, and better adaptability for your applications.

Other Brief Server Feature Comparisons

Although I won’t go through all the various major feature differences between MySQL and SQL Server, there are a couple that I do want to bring to your attention. The first has to do with replication. For me, I find MySQL’s replication easier to use and run than Microsoft’s, although you won’t find any GUI tools to help manage the setup and operations of MySQL replication (yet). MySQL uses a master/slave paradigm whereas SQL Server uses a publish and subscribe model. MySQL also has the option of using statement-based replication (that sends the actual SQL statements from a master to a slave for execution) or row-based replication (that sends the actual changed data values to one or more slave servers). One main thing you won’t find in MySQL that you will in SQL Server is conflict detection for peer replication topologies, but many MySQL shops the heavily rely on replication don’t find this omission much of a problem.

When it comes to high availability, many MySQL shops utilize replication to accomplish various fail-over scenarios. Although not exactly like SQL Server log shipping, it fills the bill pretty well. For other active/passive scenarios, MySQL users sometimes utilize DRBD which performs synchronous replication at the block level. And for scenarios needing the highest possible availability, MySQL Cluster can be used, although it does differ from SQL Server clustering quite a bit. MySQL Cluster is a shared-nothing architecture that employs a synchronous replication and heartbeat mechanism between multiple data nodes to provide high availability. Data is redundantly populated among the various nodes, and should a node go down, traffic is rerouted to surviving nodes until the down node is fixed and rejoins the cluster (at which point it rebuilds itself). MySQL Cluster also has a geographic replication option so you can replicate data from one cluster to another cluster that is physically located at a different site than the primary setup.

Going from the high to the low end, what about embedded database capabilities? SQL Server offers some nice options here with its Compact and Express editions, although each have limits in terms of how many CPU’s you can utilize and how large your database can be. MySQL doesn’t have such restrictions so you don’t have to worry there, and MySQL also offers an embedded library which can shrink the MySQL footprint down to around 3-4MB in size if you need to put a database on a device or something similar.

Lastly, if you’re using Microsoft’s integration services for ETL, BI, or similar functions, you’ll have to go outside MySQL for complementary capabilities. Fortunately, you have some nice options available with the Pentaho and Talend BI offerings, much of which you can use at no cost, but each do have pay-for options that you can move to if your applications need their pay-for only additions.

Development Capabilities

In version 5.0 of MySQL, stored procedures, triggers, views, and cursors became a part of the database server. If you’re used to SQL Server T-SQL, you won’t have much of a learning curve at all in switching to MySQL’s programming objects, but you unfortunately won’t find as rich a feature set in terms of development functions and capabilities. MySQL’s stored code objects are close to ANSI standards, but again, they don’t have the breadth and depth of T-SQL. One thing I particularly miss quite a lot from SQL Server is that in MySQL there is no SQL debugger, so you can’t easily debug complex stored procedures or functions.

In terms of connectors, MySQL delivers C, C++, .NET, ODBC, JDBC, Perl, Python, Ruby, and PHP drivers, so you shouldn’t have a problem on that front.

I won’t go into much more detail here, primarily because MySQL has a solid developer and community following for the very reason that it does supply what developers need in most cases to create applications that deliver all that you can typically get done with SQL Server.

What about Performance and Scalability?

At MySQL, we’ve stopped getting many questions about performance and scalability primarily because there are so many sites using MySQL for major applications that produce heavy workloads (e.g. Google, Yahoo, Facebook, etc.) Plus we’ve published a number of recent benchmarks that show MySQL leading the pack in various application scenarios (see our benchmark page at http://www.mysql.com/why-mysql/benchmarks/). And it’s not just the core MySQL Server showing such high performance – various storage engine partners have something to crow about in this area as well. For example, the Kickfire/MySQL data warehouse storage engine currently holds the world record in the TPC-H benchmarks at the 100 and 300GB levels (see http://www.kickfire.com/images/press_releases/tpc-h%20numbers%20300gb%20final%20may%208.pdf for more info).

When it comes to scalability, you can either scale up on a single machine or out across multiple servers. For many years, because of expensive hardware prices, the MySQL community and customer base used a scale-out approach and utilized either dual or quad boxes coupled with MySQL replication to create a dynamic and highly scalable architecture. But now that hardware prices have fallen and getting a machine with many CPU’s/cores doesn’t break the bank, some are now beginning to use scale up for their database systems. MySQL is keeping in step with these moves and released a new version of the MySQL Server (5.4, now in beta) with scalability patches for the InnoDB storage engine that allows it to address many more CPU’s/cores than it has in the past – up to 16-way generic x86 servers and 64-way Sun CMT servers.

The bottom line when it comes to comparing SQL Server and MySQL performance is that you should not encounter many (if any) roadblocks when it comes down to having fast and scalable MySQL-driven applications over what you’re used to with SQL Server.

Management, Monitoring, and Tuning

Most people won’t argue that, for years, Microsoft dominated the area of ease-of-use for managing database servers. The SQL Server Enterprise Manager (now SQL Server Management Studio) had really no peers among the major database vendors and the coupling of the SQL Server Agent job scheduling/notification capabilities with wizard-driven aids like database maintenance plans made it very easy to administer many SQL Servers (at least I always thought so).

With the MySQL Server, there are several GUI management tools provided by MySQL you can download and use and, in fact, they are the second most downloaded software package from our web site. You have a strong modeling tool in MySQL Workbench that helps you visually design databases; a SQL IDE for creating queries, stored procedures and more; a server administrator tool; and a migration tool for moving schema and data to MySQL. Further, there are a number of both free and pay-for products on the market that can help with MySQL management such as those provided by Quest Software and others.

Although I’ve always believed SQL Server was easy to manage, I would, however, argue that SQL Server wasn’t always that easy to monitor or tune, especially from a global standpoint. As a DBA, I either built my own cross-server monitoring solutions or turned to third parties for help. And while at Embarcadero Technologies, I helped design and build a number of SQL Server monitoring and tuning aids that did pretty well in the market.

Microsoft has introduced a few new/improved tools since my SQL Server DBA days such as a better SQL Profiler, fairly helpful monitoring reports, better performance diagnostic objects and collection ability, and a database engine tuning advisor. If you’re accustomed to using these things you may wonder if there are any complements on the MySQL side.

Starting at the most basic level, there are a number of command line monitoring options that you can run to get a handle on general server operations (memory, IO, etc). These are accomplished through a variety of MySQL SHOW commands and through using the small monitor that’s built into the MySQL Administrator GUI tool.

Moving up quite a bit from command line monitoring, if you subscribe to MySQL Enterprise, you’ll be able to use the MySQL Enterprise Monitor, which I think bests some of what Microsoft offers in a couple of ways. We’ve architected MySQL’s Enterprise Monitor to be global in nature, so from one web-based console, you can see and monitor all your servers and understand what’s up, what’s down, and which servers require your attention from a tuning standpoint.

Click for larger image.

What I like about the MySQL Enterprise Monitor from a DBA standpoint is the global aspect I mentioned above: the heat chart (on the right) shows you what servers need help and the alerts in the middle give you more details on best practice deviations that have occurred across your servers that need correction. The Monitor is powered by a number of best practice advisors that you can have monitor one or all of your servers, and you can add your own customer advisors if you’d like. The advisors also come with expert tuning advice on how to make things right from any errors they find on your server.

Many SQL Server DBAs including myself make heavy use of the SQL Server Profiler to catch and analyze SQL traffic. On the MySQL side, you have a couple of different options available. For interactive SQL analysis, there is a SQL profiler you can use at the MySQL command line prompt that gives a lot of diagnostic information (far above a normal EXPLAIN) on queries you execute. You can read an article I wrote about our interactive profiler on the MySQL dev zone at: http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html. For a more robust SQL capture tool, you can use the Query Analyzer that’s part of the MySQL Enterprise Monitor. Again, what I like with the Query Analyzer is its global characteristic: you can capture and roll up the worst queries across all your MySQL Servers (if you wish) vs. just a single server at a time like you have with SQL Server. The latest version (now in beta) also allows you to visually identify performance/usage spikes on your servers, highlight the spike in the graph, and immediately see all the SQL code that was running at the time.

So when it comes to management, monitoring, and tuning, you’ve got plenty on the MySQL side to help you ensure your servers are running as best they can.

How to Transition from SQL Server to MySQL

So if you liked what you’ve read so far, you’ll naturally have the question “So how can I move to MySQL from SQL Server?” First, realize you can certainly institute a co-existence strategy with SQL Server and MySQL – in fact, many do. Use MySQL where it makes sense, and if you need features or capabilities that are absent in MySQL you can go with SQL Server.

But if you want to migrate existing SQL Server databases to MySQL, you can do so in a couple of different ways. First, you can download and use the MySQL Migration Toolkit, which is a graphical wizard-driven tool that will migrate your databases from SQL Server to MySQL. I’ve used it a number of times and the product works like a champ, although there are some limitations you should keep in mind. First, it will only do tables, indexes, and data (and views where possible). So if you want to migrate your T-SQL procedures, triggers, and functions, you’ll need to use one of our partner tools that I’ll describe in a minute. Second, realize it is a client-side only tool, so if you’re going to be dragging half a terabyte or so into a MySQL Server, it’s probably not your best bet. A better plan may be to use the Migration Toolkit to move the schemas and then unload the data via SQL Server’s BCP tool and load it into MySQL with our load utility.

Alternatively you can use an ETL tool like Pentaho or Talend to move your databases to MySQL, although it’s a little more involved. These tools have the advantage of being able to do transformations of data during the migration, which the migration toolkit doesn’t have.

If you need more industrial-strength firepower to migrate your SQL Server databases to MySQL, you can look into the Ispirer SQLWays tool, which is sometimes used by the MySQL Professional Services folks in the field. SQLWays will preserve any large investment you’ve made in T-SQL stored procedures, functions, triggers, and more by translating them to MySQL syntax. It will also do all the schema and data migrations as well, plus it will even go so far as to analyze and replace code in Visual Basic, C, Java, and other like programs so that any calls are made to MySQL instead of SQL Server.

Something else to keep in mind when moving from SQL Server to MySQL is there are some objects you won’t have a complement for. Things like synonyms, global-partitioned indexes, any OLAP objects, DDL and statement-based triggers, and a few other things can’t be migrated so you’ll have to find some workaround or other similar object in MySQL for those types of SQL Server features.

Conclusion

As I said in the beginning, I thoroughly enjoyed my stint as a SQL Server DBA and if you’re looking for any harsh words aimed at Microsoft’s database, you’ll have to look elsewhere. But that said, I will also follow it up and say I find MySQL a very worthy alternative to SQL Server, especially in the departmental applications area and Small-Medium Business (SMB) market segment where SQL Server is routinely found. Of course, MySQL excels in the Web, embedded, SaaS, Telco, and Enterprise 2.0 areas as well and has the premier customer rolodex to prove it.

So why should you consider a move from SQL Server to MySQL? Before we answer that question, visit http://www.microsoft.com/sqlserver/2008/en/us/compare-mysql.aspx and see why Microsoft thinks you should switch from us to them. I think they have some valid points there (e.g. advanced security features, which we’ve already covered above, built-in BI tools, etc.), but I think a number of their other arguments are incorrect.

Now, here are a few reasons we’ve discussed in this article as to why you should consider moving from SQL Server to MySQL:

• MySQL runs great on the Microsoft OS platform, is extremely popular as evidenced by many developing and running production MySQL databases on Windows, but MySQL can be ported to other operating systems if desired, whereas SQL Server cannot.
• Regarding installation and configuration, MySQL installs faster, has a smaller footprint while still being able to manage fairly large databases, and has less configuration knobs that need turning than SQL Server.
• There are no size restrictions (CPU, RAM, database size, etc.) in any MySQL Server offering unlike Microsoft’s constraints that are placed on their standard, workgroup, compact, and express editions.
• MySQL storage engines provide more flexibility and offer more performance and custom application options over SQL Server’s standard RDBMS table type. Plus, the growing storage engine ecosystem gives MySQL great opportunity to quickly develop and innovate.
• MySQL’s feature set can handle the vast majority of RDBMS use cases (e.g. OLTP, warehousing, etc.) and has simpler implementation models than SQL Server in some areas (e.g. partitioning, replication).
• In the area of high availability, MySQL has a number of proven solutions including replication, SANs, DRBD, and MySQL Cluster, which equal or best SQL Server depending on the scenario.
• Although MySQL lacks some of SQL Server’s optimizer sophistication and parallel features, MySQL’s performance has been proven to deliver under heavy OLTP and web-styled workloads and can scale both up and out very well.
• MySQL’s monitoring and query analysis methodology is global in nature and is better suited to more easily monitor and tune many servers at one time over SQL Server’s server-at-a-time performance analysis paradigm.
• The ubiquity of MySQL, the Open Source nature of the product, and its great Community provide many benefits including a great developer and DBA network of everyone working together to help ensure a high-quality product and each other’s success.
• MySQL’s cost is typically much lower than Microsoft’s.

Developing a SQL Server/MySQL co-existence strategy and/or moving from SQL Server to MySQL is a pretty painless procedure and provides many technical and financial benefits in the process. If you want to learn more about transitioning from Microsoft to MySQL, visit our migration central web page at http://www.mysql.com/why-mysql/migration/ where you’ll find case studies, migration white papers, and more on making the move to MySQL.

As always, thanks for your support of MySQL and Sun!