The Differences Between Microsoft SQL Server and MYSQL

If you are thinking about a relational database management system (RDBMS) for your business needs, or are developing an enterprise system, you have to take Microsoft SQL Server or MySQL into consideration.

Why is the database a crucial upfront decision?

MS SQL and MySQL are both at the forefront of enterprise relational database management systems in the marketplace at the present time.

Oracle has a large percentage of the market with its Database 12c offering (Oracle also owns MySQL), there is also IBM DB2 and there are free alternatives such as PostgreSQL, SQLite, MariaDB etc.

The choice of relational database management system (RDBMS) is a crucial upfront decision to make because it lies at the heart of your system. All your creation, storage, manipulation, security and management of data will depend on the RDBMS. A careful choice is needed between Microsoft SQL Server and MySQL, because once you have chosen one it is difficult to change to the other.

What is SQL?

Both Microsoft SQL Server and MySQL have a commonality of SQL. SQL (structured query language), created in the 1970s, is the language used to communicate with a database and to undertake various operations on the data in that database. Therefore when relational databases emerged in the late 1970s and early 1980s SQL became the programming language of choice for them. A number of database management systems were built around SQL including Microsoft SQL Server and MySQL. Many of these DBMSs have their own extensions to standard SQL which are not necessarily compatible with each other. When developing applications, programmers will use a RDMS to manipulate back end data using SQL.

Both Microsoft SQL Server and MySQL are Relational Database Management Systems (RDBMs).

How this effects your Database choice

A Relational Database

is a database that stores its data in the form of tables, so that there are rows and columns of data. The data in the rows and columns relate to each other within the table and perhaps to other tables in other databases. That is why it is called relational. This arrangement allows for interrogation across each table and across multiple tables as well.

A Relational Database Management System

(RDBMS) is a the software programme that allows the data to be created, managed and queried in the database, and the results of queries to be displayed in a useable way.

OK, so let’s look at the two RDBMSs in more detail.

Microsoft SQL Server

is obviously a Microsoft product for Windows platforms, developed in the 1980s and, with the Microsoft attribution, is a trusted product. It was Microsoft’s answer to the enterprise market. It also has had frequent updates and a number of releases, and is both reliable and scalable. It can be used for building and managing applications on premises or in the cloud

MySQL

is an open source RDBMS, developed in the mid 1990s, but now owned by Oracle. Being open source and free makes it attractive to developers who may want to modify its code. It has proven its ability over time to compete with commercial products, and it is a reliable product with good features. It can support a wide range of applications including web-based and online publishing.

Superficially Microsoft SQL Server and MySQL may appear to be similar in many ways:- They both store data in relational database tables and both can host a number of databases on one server, both support desktop and web applications, the syntax of both is similar and both use many common programming languages, you can easily connect to both platforms, they are both scalable, both use primary and foreign keys in their relationships between tables and both can work at high performance speeds using indexes to sort data.

So we know that Microsoft SQL Server and MySQL have a lot in common, what are the differences?

Differences

Many of the differences are in background operations, and the casual user will probably not notice them. However, right upfront, price may be a concern:

Price

The first choice to make is whether you are willing to pay for the database system or not. Price may or may not be a consideration, but if it is the following might be taken into consideration: Microsoft SQL Server is not particularly cheap, but does bring the brand name with all the ‘big player’ advantages. There are, however, free editions of Microsoft SQL 2017 for Windows: there is a Developer full-featured free edition which is licensed as a development and test database, but in a non-production environment. There is also a free Express edition which can be used for development and production for desktop, web, and small server applications. Both use Linux operating system and Docker.

MySQL is open source, and is therefore a good option for start-up businesses, that haven’t got a lot of money to spend, a free database solution is a very attractive option. There is a MySQL version which is not free; this is the Enterprise Edition and needs an annual subscription, but the free edition is usually enough.

Useability - it matters that the RDBMS is user-friendly

Microsoft SQL Server is easy to set up and, because it’s Microsoft, it has lots of documentation and online support.

MySQL is also not very hard to set up, and there is a lot of helpful information available to make it easy to work with. There are also graphical interface user tools available to help as well as third party tools and applications. However if something goes wrong you will only get an error message and code which you will have to look up, although with a wide on-line community there is a lot of support.

In running performance MySQL is better; it runs on Windows and UNIX, though it performs better on UNIX. Microsoft SQL Server has to compromise on performance in order to provide the wider range of features that it has over MySQL.

Microsoft SQL Server allows you to cancel a query mid-process before execution, but MySQL does not. Microsoft SQL Server supports XML, offers stored procedures and full join facilities, but MySQL does not.

Although both use many of the same programing languages, MySQL has several additional ones like Perl and Haskel which can be an aid to developers.

When filtering Microsoft SQL Server allows filtering in a row-based way, database by database, and the filtered data is stored in a separate database, making it easier for developers. With MySQL developers have to filter individual databases by running a number of queries.

Backing up with MySQL involves extracting the data as SQL statements, but this blocks the database. Although it lessens the chance of corruption when switching between MySQL versions, it slows the process up. Microsoft SQL Server doesn’t block the database, making backing up quicker and easier.

Platforms & Environment - let’s look at the basics

Microsoft SQL Server is mainly Windows based, however it now supports Linux environments and Mac OS X, although they will lack certain features compared to MySQL. Microsoft SQL Server is very appropriate for developers using .NET framework as a infrastructure for building apps on a Microsoft platform. Also with .NET you can start straight into LINQ queries.

MySQL is supported on a number of platforms including Windows, Linux and iOS. It is usually part of a LAMP environment. It can be paired with many languages, but is most appropriate for those using PHP. If you want to use .NET for LINQ queries you have to use third-party provider tools.

Syntax - which uses what

The two programs interpret the ANSI SQL-92 standard differently:

Microsoft SQL Server uses Transact SQL (T-SQL) as its native language. T-SQL is a set of programming extension of SQL from Sybase and Microsoft; it is a powerful programming language designed to make common database queries quicker and easier.

MySQL uses ANSI SQL 99 which is the standard ANSI release representing the year it was released, and a generic combination of things. So although it has the same broad functions and characteristics of T-SQL, it does not support to the same extent as T-SQL.

Security - Loss, damage or hacking of sensitive information is one of the primary concerns for businesses

Both systems are E2 compliant and therefore secure, but Microsoft SQL Server has additional Microsoft features such as Microsoft Baseline Security Analyzer.

Reporting - a crucial requirement

Microsoft SQL Server has its own suite of native tools for reports and analysis which will ensure frictionless compatibility. With Microsoft SQL Server Express Edition you can get SQL Server Reporting Services (SSRS) as a free download. It also has AI built in so that you can process the data where it is, rather than moving and refreshing it. With MySQL you will need third party tools such as Crystal Reports XI and Actuate BIRT. It is, however, possible to create a SSRS report using a MySQL data source.

Storage Engines - using the database

Microsoft SQL Server uses a single storage engine, whereas MySQL 5.7 supports ten. This allows a certain flexibility to MySQL since different storage engines can be used depending on the developer’s requirements. These include InnoDB, MyISAM, Memory, CSV, Archive, Blackhole etc. Microsoft SQL Server embodies data integrity, MySQL is simpler and faster. The multiple storage engine availability means that MySQL is more flexible than Microsoft SQL Server.

Conclusion

There is no absolute correct solution as to which is best. The choice of RDBMS will ultimately come down to the individual requirements and preference of the client user, business case or application development issues. The choice is not only between Microsoft SQL Server and MySQL but also between the various versions of each available.

The choice might depend on the hosting environment, or if security is the major issue for you then you might think that Microsoft SQL Server is the choice. If you are developing small to medium size applications using PHP then MySQL might seem the way to go, but if large scale is needed it could sway you towards Microsoft SQL Server. If flexibility of storage engine support is the issue then MySQL wins. There are many things to consider, but only you, perhaps with advice from a selected software developer, will be able to pick the best fit for your specific business needs.

If you have any concerns about which database management system your company may require, please don’t hesitate to get in touch with us.