SQL Server vs. MySQL
Serdar Yegulalp, Contributor
Microsoft SQL Server is far from being the only database management system for Windows. Among
SQL Server's contenders are commercial applications like Oracle and freebies like MySQL. But
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in June 2006
free availability has left many people questioning how
well an open source system stacks up against its commercial counterparts, and they're often
surprised to find out how solid it is in production.
In this tip, I'll examine in detail MySQL (now in its 5.0.22 revision) and compare its features
to Microsoft SQL Server 2000 and 2005. Both are powerful DBMSs, widely used, with aspects that make
them attractive to different audiences.
Cost and licensing
The most obvious and enduring difference between MySQL and SQL Server is the price tag. MySQL
and its attendant client libraries are free under the GNU Public License, though the system is also
released under a more proprietary license when needed. Because of its low cost and broad support,
many commercial Web hosts use MySQL as the default database application. (My own personal Web site,
Windows Power Users Newsletter, uses a
content-management system that runs on MySQL.) Also, there are no arbitrary restrictions on the
product's usage, such as the number of seats you can apply it to in a single installation.
MySQL's licensing has occasionally led to problems. The open source project Asterisk PBX, for instance, eventually found that the licensing
it used was incompatible with MySQL's licensing, and MySQL support had to be reduced. However, for
the most part, many people are willing to hew to MySQL's licensing in order to use it, and they
have contributed a broad range of add-ons and features to the product.
In contrast, SQL Server is an avowedly commercial and proprietary application. The cost for its
licensing is based on how many physical processors ("per socket") you plan to run the software on,
and different editions of the program are available depending on the scope of your needs. For
instance, support for more than four physical processors is only available in the SQL Server
Enterprise Edition.
Microsoft is sensing the strong competition from products like MySQL. The software giant
recently made available SQL Server 2005 Express Edition, a free -- though not open source --
edition of SQL Server that can be deployed on a server or packaged with an application. Express has
some limitations: It can only use one physical CPU; it cannot address more than 1 GB of system
memory for its use; it has less effective processing for more than eight concurrent connections;
and it can only support a maximum database size of 4 GB. That said, it supports many of the most
useful features of SQL Server at large , such as full-text indexing (which MySQL also supports out
of the box), internal data encryption, native XML support and integration with Microsoft Update for
patches and revisions.
SQL Server is available in a number of different packages. SQL Server 2000 Standard Edition
(four-processor support, no database or memory limitations) is included as part of Windows Small
Business Server 2003. This makes SQL Server an appealing choice for those already making an
investment in SBS2003. You can download a full version of SQL Server 2005 and run it in a trial
fashion for 180 days.
Another key point of comparison is that MySQL is multi-platform, which includes Windows, while
SQL Server is Windows-only and will most likely remain that way.
Functionality
For a long time MySQL came under fire for not supporting some of the most common features of
other relational databases, such as transactions or stored procedures. These features and a host of
other important ones were introduced in the 5.0 revision, silencing a good deal of criticism.
Despite this , MySQL has enjoyed broad adoption by a number of high-profile users -- Craigslist,
Digg, Friendster, LiveJournal, Slashdot, Travelocity and Wikipedia all use MySQL extensively and
exhaustively: .
SQL Server's feature set was built for and has been adopted largely by small to medium-sized
businesses, and it has been expanding into larger applications over time. Barclays Capital, Hilton
Hotels, Hyundai Motors, JetBlue, NASDAQ and the Tesco grocery chain are some of the outfits with
SQL Server deployments.
MySQL has the ability to use multiple storage engines for individual tables, so you can choose
the most effective one for a given table. One such engine is InnoDB (now the property of Oracle), designed
specifically for high reliability -- sometimes at the expense of speed. SQL Server uses its own
proprietary storage system for everything, but it maintains multiple safeguards against data loss.
Both SQL Server and MySQL can run in clusters for high availability.
One of the greatest advantages SQL Server provides is its broad range of native data analysis
and reporting tools. SQL Server Reporting Services is one of the most prominent and widely used,
and it's available with SQL Server Express Edition as a free download. Third parties have written
similar tools for MySQL, such as Crystal
Reports XI and Actuate BIRT, both
commercial products.
Implementation
Another key practical difference between SQL Server and MySQL is how the two programs interpret
the SQL-92
standard. SQL Server uses an implementation of SQL-92 called T-SQL,
which has some additional proprietary syntax to handle transactions and stored procedures. Other
proprietary elements in T-SQL are designed to make certain computations easier. For instance, you
can use the TOP keyword to restrict a given query to the first x elements returned.
MySQL uses ANSI SQL 99, but it supports many of the same functional behaviors as SQL Server --
triggers, cursors, updatable views, stored procedures, nested selects, Unicode support and so on.
Consequently, an application written specifically for MySQL or SQL Server cannot be switched from
one platform to the other without a certain amount of rewriting.
Here is a good independent comparison of SQL language
implementations. It compares SQL Server and MySQL and many others.
Conclusions
The single biggest reason to choose one given database product over another is suitability to
needs -- and both MySQL and SQL Server are written to address very different sets of needs. If you
are going to write or support applications designed for SQL Server, Microsoft-specific features or
primarily for private use, and the cost is not as much of a concern, SQL Server makes a lot of
sense. If you're working on a tight budget, or you're creating something that may well be ported to
multiple platforms, MySQL is a good place to start.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the
latest advice and musings on the world of Windows network administrators -- and please share your
thoughts as well!
More information from SearchSQLServer.com
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation