Two of the most popular back-end data stores Web developers work with today are MySQL and SQL Server. They are fundamentally similar in that both are data storage and retrieval systems. You can use SQL to retrieve data with either because both claim support for ANSI-SQL. Both database systems support primary keys and key indices, so you can also create indices used simply to speed up queries and for constraining input. Further, both provide some form of XML support.

Aside from the obvious difference of price, what distinguishes these two products from each other, and how do you choose between them? Let’s take a look at the core differences between these two products, including licensing costs, performance, and security.

Core principles are the root differences
The differences begin with principles: open vs. proprietary. SQL Server with its closed, proprietary storage engine is fundamentally different from MySQL’s extensible, open storage engine. You’re stuck with the SQL Server’s Sybase-derived engine for better or worse, while MySQL provides multiple choices such as MyISAM, Heap, InnoDB, and Berkeley DB.

This open vs. closed difference is, by itself, enough reason for some folks to choose one over the other. But, there are some technical differences as well. To begin with, MySQL doesn’t fully support foreign keys, making it less of a relational database than SQL Server, which has full relational features. Also, MySQL has previously lacked support for stored procedures, and the default MyISAM engine doesn’t support transactions.

Licensing costs: MySQL isn’t always free, but it is cheap
When it comes to licensing costs, both products use a two-tiered scheme. With SQL Server, the most popular way to get a free developer license is to purchase a license for Microsoft Office Developer or Microsoft Visual Studio, both of which give you a free “development use only” SQL Server license. If, however, you want to use it in a commercial production environment, you’ll have to pay for at least the SQL Server Standard Edition, which will set you back around $1,400 for five client connections.

On the other hand, MySQL is open source and licensed through the GNU General Public License (GPL). For developers, this means that as long as the project you are working on is also open source, you don’t have to pay to use MySQL. If, however, you plan to sell your software as a closed-source product, you’ll need to pick up a commercial license, which currently costs $440 for up to nine clients. Schools and nonprofits are exempt from this commercial licensing requirement.

Performance: Advantage MySQL
In terms of pure performance, MySQL is the leader, mostly due to its default table format, MyISAM. MyISAM databases are very compact on disk and place little demand on CPU cycles and memory. MySQL can run on Windows without complaint but performs better on UNIX and UNIX-like systems. You can experience additional performance gains by using MySQL on a 64-bit processor (e.g., one of those sweet SPARCstations), because MySQL uses an abundance of 64-bit integers internally. Much of the very busy Yahoo! Finance portal uses MySQL as a back-end database.

As I mentioned, with MySQL, you have a choice of table formats, but generally, these nondefault choices exact a cost in increased resource usage over MyISAM. Typically, though, these alternative table formats provide some additional functionality. For example, Berkeley DB supports transactions and actually has better performance with indexed fields than MyISAM.

When it comes to performance, SQL Server’s strength—providing many more features than its competitors—is also its weakness. Granted, many of these features are geared toward performance tuning, but being a feature-rich environment means sacrificing something else. In this case, the cost is additional complexity, disk storage, memory requirements, and poorer performance. If you can’t afford to support SQL Server with powerful hardware and trained expertise, you should definitely look elsewhere for a DBMS because you likely won’t be happy with the results.

It’s worth noting that both systems will work well within either a .NET or J2EE architecture. Similarly, both will benefit from RAID, and both will perform best if the data store is on a hard drive or array that is solely dedicated to that purpose.

Replication and scalability: A dead heat
MySQL keeps a binary log of all SQL statements that change data. Because it’s binary, this log can be used to replicate data from the master to the storage on one or more slaves very quickly. Even if the server goes down, the binary record is still intact, and replication can take place. For query-heavy databases systems, MySQL scales easily into large data farms.

In SQL Server, you can also record every SQL statement, but doing so can be costly. I know of one development shop that had to do this because of other architectural issues, and the sheer volume of data that they were storing on tape was quite remarkable. Instead, SQL Server relies on elaborate mechanisms of record and transaction locking, cursor manipulation, and dynamic replication of data to keep database servers synchronized. If you’re skilled at juggling these mechanisms, replication is pretty easy.

Security: Also tough to call
Both products have perfectly acceptable default security mechanisms, as long as you follow the manufacturer’s directions and keep up with your security patches. Both products operate over well-known default IP ports, and, unfortunately, those ports draw intruders like flies to honey. My firewall logs are always chock-full of folks trying to contact nonexistent database instances on my machine over the default ports. Fortunately, both SQL Server and MySQL allow you to change that port should the default become too popular a destination for your taste.

Recovery: Advantage SQL Server
Failsafe and recovery is one area where MySQL, in its default MyISAM configuration, falls a little short. With MyISAM, a UPS is absolutely mandatory because MyISAM assumes uninterrupted operation. If it is shut down unexpectedly, the result could be corruption of the entire data store and loss of all your data. SQL Server, on the other hand, is far more resistant to data corruption. SQL Server’s data goes through multiple checkpoints as it passes from the keyboard to the hard disk and back out to the monitor. And SQL Server remembers where it was in the process even if it happens to be shut down without warning.

The best choice depends on the situation
If you were hoping to get an ironclad recommendation that one database is better than the other, I’m going to disappoint you. From my point of view, any database that helps you do your job is a good database; one that doesn’t is a bad database. I can tell you that to make a good decision about which of SQL Server and MySQL will help you most, you’ll need to look beyond politics and hype and instead look at function and mission. What do you want to accomplish?

If you’re trying to build a .NET services architecture, synchronizing data between multiple disparate platforms, or learning the loftier precepts of database management, SQL Server will help you most. If you’re building a third-party-hosted Web site, pushing a lot of data out to a lot of clients, or have a budget in the neighborhood of free, then MySQL will be your best bet.

Which do you use, and why?

Is your Web app or site powered by MySQL or SQL Server? What led you to make this decision, and why was it the right choice for you? Tell us about it in the discussion below.