Two of the most all time popular database systems used by web developers today are MySQL and Microsoft’s MS SQL server. Both are similar in regards to being able to retrieve and store data on the respectful system. The two systems support primary keys along with key indices which allows you to speed up queries and constrain input. Both systems also offer a form of support with XML.
Apart from the price which is the major obvious difference what distinguishes these two popular systems? Which one is suitable for who? In this post I’ll explain the major differences and the advantages of using them.
The main difference with MySQL and MS SQL is that MySQL is open source where as MS SQL has a proprietary structure. MySQL is an extensible open source storage database engine and offers multiple variations of itself by its developers and its large community. With Microsoft’s SQL product you are limited to a Sybase-Derived engine. Considering how MySQL integrates seamlessly with a number of programming languages and other web-based sources it certainly has the major advantage over MS SQL in the way of compatibility and deployment. SQL Server (MS SQL) is only really beneficial with Microsoft based products.
Contrary to popular belief MySQL isn’t always free though on the other hand, it is more affordable. In regard to both products licensing fees are based on a two-tier system. With SQL Server the best way to obtain a developer license is to purchase it with Microsoft Developer or Microsoft Visual Studio suite. Both provide you with a free SQL server license for development use. If you want to use the product in a commercial environment you are required to at least purchase SQL Server Standard Edition which you set you back over $1,000 (USD).
Because MySQL is a open source based system under a GNU General Public License, developers can use it at no cost as long as the associated projects are also open source. However if you intend to sell your software as a proprietary product you would be required to purchase a commercial license which can cost about $450 (USD) for up to nine clients. Depending on your product and funds MySQL may have your major advantage.
The online battle of open source vs proprietary battle is a leading cause for some users to choose one system over the other. But there are a few differences with a technical aspect as well. MySQL does not offer a full support for foreign keys which means it doesn’t have the features of SQL Server which is mainly considered as a complete relational database. Several version of MySQL also lack full support for several stored procedures which is the biggest disadvantage for the MyISAM system which doesn’t support transactions.
In terms of performance (In my view and from experience) MySQL is a clear leader mainly with its format of default tables and MyISAM. MyISAM databases leave a small footprint with resources such as disk space, memory and CPU usage. While the system runs on the Windows platform without any flaw, it tens to preform better on Linux and some UNIX like based systems.
When it comes to performance with SQL server its strength of being packed full with incredible features more than any other is perhaps its most biggest disadvantage. Although most of these features are designed with performance in mind, they tend to be a resource hog. The cost here is really complex with its hogging resources in the way of memory and storage which sadly leads to poorer performance for both the system and end-users. If you lack the knowledge and sufficient hardware to support an SQL server you would better off with another database management system.
Both systems are pretty much completely secure in terms of security. They both come with adequate security mechanisms by default, bearing you follow the directions and keep them up to date with security patches and service packs. They both work over known IP ports which sadly attract lots of potential intruders. Both MySQL and SQL server allow you to change ports in the events the default ones come too vulnerable.
Recovery and Corruption Protection
As far as system recovery and Server Data Recovery goes SQL Server amazingly has the biggest advantage over MySQL. MySQL tens to fall short with its MyISAM configuration. A UPS system is mandatory to assume a uninterrupted operation. If a power outage should occur it could result in the corruption of critical data. With SQL Server data loss and interruption is far less likely, the data travels through various checkpoints while passing from your keyword to your hard disk though to your monitor. SQL server also keeps track of process even if the system unexpectedly shuts down.
Which is best?
As per which is the best system both have their difference advantages and disadvantages. Form my point of view I wouldn’t recommend which is best as it depends on your environment, budget and more importantly what your looking to accomplish with your infrastructure.