Friday, September 18, 2009

SQL SERVER – Difference between MS SQL Server Express and MySQL

Both SQL Server express and MySQL are two of the Relational Database Systems (RDBMS) available today. Both are freely available and meant for running smaller or embedded databases, yet there are also significant differences between them.
SQL Server Express is a freely-available small-brother version of Microsoft’s enterprise system, SQL Server. It is the successor to MSDE (Microsoft Desktop Engine), but it is also a huge improvement over it, especially regarding the user interface design and the included feature-set. Its main features are:
Database size limit of 4GB. This excludes logfiles and can be overridden by using multiple databases.
Support for only one CPU (but multiple cores).
No SQL Server Agent.
SQL Server Express offers a number of advantages. The first and arguably most important is the complete scalability and integration with SQL Server. This allows you to seamlessly upgrade your database as your needs also grow (remember SQL Server Express is limited to a database size of 4GB). In fact the database upgrade is so smooth that even with no prior experience it can be done in about 10 minutes for a 3GB database.
The SQL Server Express interface is much improved over its MSDE predecessor, and offers such GUI tools like the SQL Server Management Studio Express, the SQL Configuration Manager, the SQL Business Intelligence Development Studio and SQL Server Reporting Services. All these work together to make the little RDBMS almost as rich in features as its SQL Server enterprise sibling.
SQL Server Express also has a strong family heritage. Being a Microsoft product, one can expect such features as excellent integration with Windows (for example domain authentication), support for dotNET, ODBC and XML, and comprehensive help and support from both the mother company and a large community of users on the web.
On the other hand, SQL Server Express’s most severe limitation is that it only runs on Windows. Given the large number of organizations and individuals running other operating systems especially Unix and Linux, it is locked out of an important market segment. Others are of course its aforementioned 4GB size and single-CPU handicaps. These, however, are not usually a problem for small databases.
MySQL, on the other hand, is a completely open-source RDBMS, as contrasted to SQL Server Express which is a free ‘lite’ version of a commercial database engine. Its largest arena is as the backend database for about 12 million websites around the world. Many of the world’s high-traffic websites like Yahoo Finance and Slashdot use MySQL as a backend database, a testament to its reliability. MySQL is open-source and its source code is freely available under the GPL (General Public License); so it can be argued that it is in fact a competitor to not only SQL Server Express, but the full-bodied SQL Server as well.
One unusual offering from MySQL is its ability to incorporate different database storage engines depending on the user’s primary need. If the DBA determines that the database will mainly be used for either quick data access, or transaction input speed, or accessing heavily partitioned tables, then he or she can choose the appropriate storage engine to suit this need. Of course, this also adds a new complexity- you must know the right engine to use and how to install and configure it. Examples of these engines are InnoDB, Memory, and NDB Cluster.
Unlike SQL Server Express, MySQL will happily run on almost all operating systems, although unofficial comparison tests run by several independent companies and individuals point to anecdotal evidence that MySQL performance is excellent on Unix and Linux, but not as good as either SQL Server Express on Windows platforms, much less the full SQL Server. This is partly attributable to SQL Server’s optimization within Windows, but also partly to MySQL’s reputedly weak Query Optimizer. In fact it is only recently that MySQL has incorporated advanced RDBMS features such as foreign key support and multiple-stage data commit.
Another important area in which MySQL comes up short is system stability- sudden power loss on a server hosting MySQL can very well cause severe data corruption. SQL Server and Express are more robust and tolerant to such outages because the data save process goes through multiple checkpoints.
I hope this has provided you with a good overview of the differences between SQL Server Express and MySQL. Even though both are free RDBMS systems and worthy competitors, they have areas of market overlap but also distinct market segments. Experience has taught many database administrators that each system has it strengths and weaknesses, and one would be advised to research thoroughly before settling on one or the other.

No comments:

Post a Comment