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 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.
More on how SQL Server compares to the competition
Learn about the database battle Microsoft's SQL Server 2012 will face
Find out what Microsoft has to say about Amazon RDS
Read about the pros and cons of SQL Server 2012
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.
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.
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.
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