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.
You can read the complete reference over here SQL SERVER – SQL Server Express – A Complete Reference Guide.
Please contribute here with your comments and opinion.
Reference : Pinal Dave (https://blog.sqlauthority.com)
20 Comments. Leave new
I think of MySQL as data storage with a SQL interface. I would never use it where advanced database capabilities are required.
Nice explanation Sir.
Thanks for taking the time to write this article.
I am currently taking a entry level database course which first utilizes Access 2008 then moving onto a more complete enterprised based DBMS. Can you merge/convert Access 2008 front end with SQL Server Management Studio express? I’ve read you can migrate the data to SQL Microsoft SQL server, I just wasn’t sure if you could use the SQL Server Management Express edition and have the same functionality.
Thanks for any replies in advance.
Patrick
I cannot think of any situation where I would pick SQL Server Express over MySQL.
For the last several months I have been working on a project with MySQL as a requirement. The longer I work on this, the more I say to myself. If given a choice, I will never pick MySQL over SQL Server Express, no matter what the project is.
Hi,
Would you please talk about the weak MySQL Query Optimizer in comparison with that of SQL Server?
As someone who’s used both, but am a fan of Mysql (as you can see from my weblink), I feel that there are two distinct areas where one database makes sense over the other. If you’re using a Microsoft environment and other Microsoft tools in your business, then SQL*Server is the optimum solution. If however you’re hosting a website on a Linux server and using PHP as your web programming language then Mysql would be your preference. In between there are a lot of considerations to which to pay attention. It’s not always a “night and day” issue. For example if you’re into Data Warehousing, Olap cubes, data mining etc then there are a lot more options available if you follow the SQL*Server route; yet there are now opensource tools such as Pentaho that are being developed in the Mysql arena. Luckily in both directions there are lots of useful resources (both on the net and in print) that will give you valuable help and guidance in utilising your database to the maximum – such as this blog. Thank you Pinal Dave!
Hello,
Firstly thanks for the great article it has given a very clear explanation and is very helpful.
Secondly in answer to Patrick, if you want to move you data to SQL express and then use access as the front end this is definelty possible, and I believe quite common, the easiest way i Know of to do this is load the data into SQL using whatever method you fancy, and then once you have merged the tables you can then just link to the SQL tables from your access database, then all your code etc should just work as usual.
Hope this helps
Simon
Nice explanation…expect more on enterprise level database settings..
hi
article is nice but there is less description about Mysql comparitive to the sqlserver
thanks and regards
bhanuprakash
This is a very interesting article. The difference between database servers is not always apparent. Is there a similar study that compares SQL Server 2005 or 2008 to PostgreSQL 8+ ?
Pinal,
FYI that SQL Express db size limit now raised to 10GB. Single CPU limitation remains, but can be used on multi-core single CPU.
Great article. Thank you very much.
What i can tell from my experience is that if you are working with .Net you should use MSSQL in any cases, cause it’s terrible and dangerous working with MySql and the MySql Connectors. I am sorry to say that, cause i like working with MySql too. The connectors are never uptodate and free of bugs.
MySql causes many problems using third part tools, like Telerik or authority tools like those if the IIS. So Everything counted, nothing speeks against using MySql as long as you do not work in a Microsoft environmenet.
Cheers.
Awesome….. from this article my concepts are very clear about sql express and mysql.. Thank you very much.. Can you please tell me the difference among sql, mysql and oracle?..
nice one
What is my MYSQL database size limit?
what is the maximum size of mysql database, for general, standards or enterprises
My company is developing commercial software with an SQL back end, and there is an important difference between SQL Express and MySQL – MySQL cannot be used for free under these circumstances. The trick in the licence is the use of the connector (MySQL.Data.Dll) – using this connector in software sold commercially requires a licence from Oracle, and these cost $6,000 per CPU socket in Australia, although there is an 80% discount if you’re using it OEM – but that only works for hardware manufacturers.
SQL Express can be used for free in commercial software, and the cost’s I’ve been quoted for SQL standard edition from Microsoft are actually less than the cost of MySQL OEM (with the 80% discount). So much for MySQL being marketed as a ‘cheap’ alternative to SQL Server.
Oh, and the MySQL sales team are extremely slow and take weeks to get back to you.
But you could use MariaDB in that case.
I have been working on a project where MySQL was a requirement. I ran into a problem where I finally found that the database was allowing my software to delete records in a table that had related records in another table. Come to find out the database engine used for some of the tables still does not support Foreign Keys. The author of this article says “In fact it is only recently that MySQL has incorporated advanced RDBMS features such as foreign key support.” But foreign keys are what establish the relationship between entities. How can anyone call MySQL a **Relational** Database Management System if only some of the available engines are just now implementing such a defining, core feature of what it means to be a relational database?