Brian Jackson - Fotolia
SQL Server and MySQL are two of the most popular relational database management systems on the market, both based on SQL. While one isn't clearly superior to the other, there are certain uses in which Microsoft's SQL Server may be a better choice than Oracle's MySQL, and vice versa. It all depends on what your business requires.
Here's a look at SQL Server vs. MySQL, including important features, pricing, compliance and other relational database management system (RDBMS) considerations to help you make the best choice for your data processing workloads.
Microsoft SQL Server features, pricing
The most up-to-date version of Microsoft's RDBMS is SQL Server 2019, although it's currently still in the preview stage. SQL Server 2019 allows users to choose between different platforms for deployments; like predecessor SQL Server 2017, it's available on Windows, Linux and Docker containers. SQL Server 2019 also adds support for creating big data clusters that combine the database system, the Apache Spark processing engine and the Hadoop Distributed File System, all running in Kubernetes-managed containers. Additionally, SQL Server 2019 offers Power BI Report Server, which allows users to access reports in Power BI and SQL Server Reporting Services.
Under Microsoft's community technology preview (CTP) program, the company is updating SQL Server 2019 each month. For example, it released CTP 2.5 of the software in April 2019. Previous incarnations of CTP -- like CTP 2.0, the first preview release -- provided users with the ability to recover from indexing failures. Prior to the introduction of CTP 2.0 in October 2018, failure meant that the indexing process had to be completely restarted, which made it time-consuming and difficult. With SQL Server 2019, users can resume the indexing process without having to start over from the very beginning, creating a smoother and more efficient procedure.
CTP 2.0 also included an Always On Availability Groups update that increased the number of synchronous secondary replicas that can be set up in SQL Server systems. Users are able to configure groups of up to five replicas and program them to have automatic failover within the group.
In addition to those capabilities, CTP 2.5 includes all features introduced since CTP 2.0, such as the ability to submit Spark jobs and use Sparklyr on big data clusters in IntelliJ, external library support for Java and Python on Windows, calculation groups in tabular models, and accelerated database recovery, which is a direct result of redesigning SQL Server's database engine recovery process. This new accelerated database recovery promises a quick and efficient complete recovery after a failover or improper shutdown.
With CTP 2.5, users are able to create and customize their own deployment configuration JSON files for big data cluster deployments instead of using environmental variables. CTP 2.5 also prompts users with mssqlctl cluster create for any new settings under default deployments.
As of the time of this writing, SQL Server 2019 is free to download because it's still in preview mode. However, Microsoft doesn't support it for production uses, except in some cases for customers that are enrolled in its early adopter program.
SQL Server 2017 is the most recent production version of the database system. It comes with a full suite of features, including a high-performing data warehouse, Always Encrypted technology and in-database analytics.
Microsoft licenses the Standard edition on a per-CPU-core basis, with licenses sold in two-core packs; if SQL Server is running in a server's physical operating system environment, you must buy a license for all of the processor cores. A SQL Server 2017 Standard Edition license lists for $3,189. As with any technology, actual licensing costs typically are based on a number of factors, such as volume discounts.
Oracle MySQL features, pricing
MySQL is owned by Oracle, which acquired the technology when it bought Sun Microsystems in 2010; Sun had purchased the original developer MySQL AB in 2008. Oracle MySQL Cloud Service, MySQL Enterprise Edition and MySQL Cluster CGE are all commercial applications built on Oracle's cloud platform and MySQL's database services. Oracle offers two different versions of MySQL: an open source MySQL Community Server and a proprietary MySQL Enterprise Server. The latest incarnation of MySQL -- MySQL 8.0 -- can be downloaded from Oracle's website.
MySQL 8.0 provides users with table encryption management. By default, a table will inherit the encryption of the schema or the general tablespace it was created in, and a privilege check will occur when a schema is created or altered. MySQL 8.0 also supports roles that can be granted or revoked from user accounts. It also maintains information regarding password history and allows restrictions on reusing previous passwords. Additionally, accounts can have dual passwords. These new updates to security make the system harder for hackers to access sensitive information and allow for greater DBA flexibility when it comes to account management.
A transactional data dictionary was also added to MySQL 8.0. The transactional data dictionary stores information about database objects and is automatically upgraded. In previous MySQL releases, the data dictionary was stored in metadata files or nontransactional tables and needed to be upgraded manually by using the mysql_upgrade command.
Other versions of MySQL include MySQL Classic, an embedded database that is available only to independent software vendors or OEMs, and MySQL Community Edition is the open source database available through the GNU General Public License. However, the three primary editions, which include management tools, are commercially licensed through Oracle.
Commercial MySQL licenses are available as a subscription, and the prices vary widely depending on the MySQL edition, the communications socket count and the subscription length. MySQL Standard Edition costs $2,000, and MySQL Enterprise Edition costs $5,000.
SQL Server vs. MySQL performance and scalability
MySQL once had a reputation for being less capable than competing databases. Today, MySQL is a mature and full-featured database used by many high-profile organizations.
MySQL emphasizes database scalability and performance. MySQL databases can easily scale to most multi-terabyte databases and can be optimized to handle high-speed transactional workloads or extremely high-volume workloads of up to a billion queries per day. MySQL uses features like memory tables, b-tree indexing and hash indexing to achieve very high levels of performance and scalability.
MySQL is capable of spreading both the databases and the application queries across multiple servers. MySQL also offers certified and supported solutions that deliver appropriate levels of high availability and scalability to meet service-level requirements. The RDBMS can be configured as a failover cluster, but it also supports high-speed replication. There are also a number of third-party vendors that offer high-availability options for MySQL. MySQL Enterprise Edition provides the MySQL Thread Pool, which meets the high demand for performance and scalability for user, query and data loads. MySQL has been designed to cope with and recover from failures to host; this includes failures of MySQL, the operating system or hardware and maintenance activity that may result in downtime.
Microsoft's SQL Server has also evolved considerably over time, and SQL Server 2019 bears little resemblance to the SQL Server versions of the early 1990s.
Like MySQL, SQL Server can be made highly available, and can handle the largest, most demanding workloads. When SQL Server 2019 big data clusters are deployed with Kubernetes, the deployment is reportedly predictable, fast and elastically scalable, regardless of where the deployment takes place. The big data cluster utilizes a scalable storage layer that integrates SQL Server to scale down to petabytes of data storage. Because Spark engine is now a part of SQL Server 2019, users are able to take full advantage of open source data preparation and query programming libraries; these can be used to process and analyze high-volume data in a scalable layer.
SQL Server vs. MySQL implementation
Both SQL Server and MySQL support SQL, the standard programming language for relational databases, but in each case with a number of proprietary extensions.
Another important consideration when deciding on a relational database management system is standards compliance. SQL standards have existed since 1986, when the SQL-86 standard was introduced. SQL standards have continued to evolve, especially now that it is compatible with Windows, Mac, Linux and containers.
MySQL supports current SQL standards as well as Open Database Connectivity levels 0 to 3.51. Additionally, MySQL can operate in a variety of SQL modes including ANSI, STRICT TRANS TABLES and TRADITIONAL. It is worth noting that MySQL augments the SQL Server standards through a number of extensions. As such, porting a MySQL application to Microsoft SQL Server, or to a competing SQL database engine, requires a significant amount work.
As part of the MySQL Workbench, Oracle's migration wizard provides a thorough guide to database migrations. Regardless of what kind of database needs to be moved, migration wizard is able to handle it, as it is compatible with Microsoft SQL Server, Microsoft Access, PostgreSQL, Sybase ASE, Sybase SQL Anywhere, SQLite and many others. Migration wizard lets database administrators (DBAs) and developers manage their migration projects and allows users to choose which data they want moved.
As of 2017, Microsoft has added support for running SQL Server on Linux systems. Continuing what SQL Server 2017 started, SQL Server 2019 aims to incorporate more platforms as Apache Spark and Hadoop Distributed File System join the program.
SQL Server vs. MySQL: Key differences
Although SQL Server 2019 operates in Windows, Linux and containers, it was originally intended for exclusive Windows use. Likewise, MySQL operates on multiple platforms. Because MySQL was designed with the needs of modern web apps in mind, developers prefer to use it as a component of LAMP Stack with Linux, Apache Web Server or PHP programming languages. The components of LAMP Stack are interchangeable, meaning that users will need to work with different programming languages. Both MySQL and SQL Server 2019 support Java, PHP, C++, Python, Ruby, Visual Basic, Delphi, Go and R; additionally, MySQL supports Perl, Scheme, Tcl, Haskel and Eiffel. MySQL is only available in English, whereas SQL Server 2019 is available in multiple languages.
MySQL allows DBAs to filter out tables, rows and users in multiple ways, although doing so requires that users filter out the tables, rows or users by individual databases. SQL Server 2019 allows DBAs to utilize row-based filtering, which filters data via a database-by-database method, and allows the filtered data to be stored in a separate distribution database.
DBAs using MySQL are able to back up data by extracting all data as SQL statements. This is accomplished with a tool provided by the RDBMS that blocks the database while the data is backed up. SQL Server 2019 doesn't block the databases while backing up data, which allows users to back up and restore data without wasting time and energy.
Both MySQL and SQL Server 2019 incorporate security into their design. MySQL allows DBAs to manage database files through binaries while they are running, and allows files to be accessed by other processes while it is running. SQL Server 2019 doesn't allow any process to access its database files or binaries and requires DBAs to perform specific functions by running an instance, making it impossible for hackers to directly manipulate the data.
MySQL isn't without its limitations. In MySQL, triggers are limited to only one action per timing, meaning that only one trigger can be executed on a table and that triggers can't be defined on views. In regards to triggers, MySQL doesn't follow the full set of SQL standards.
There are two different versions of MySQL -- MySQL Community Server, which is free, and MySQL Enterprise Server, which comes with several proprietary extensions. SQL Server 2019 is still in beta mode and users are able to download it for free.
What can DBAs expect from SQL Server 2019?
New features in SQL Server 2019