Brian Jackson - Fotolia
SQL Server and MySQL are two of the most popular relational database management systems on the market, both based on Structured Query Language. While one isn't clearly superior to the other, there are certain uses in which Microsoft's SQL Server 2019 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 workloads.
Microsoft SQL Server features, pricing
The most up-to-date version of Microsoft's RDBMS is SQL Server 2019, which is currently in the preview stage. SQL Server 2019 promises to allow users to choose which language and platform to utilize, as it's available on Window, Linux and containers. Additionally, SQL Server 2019 offers Power BI Report Server, which allows users to access Power BI reports and SQL Server Reporting Services.
As of April 2019, the SQL Server 2019 preview features community technology preview (CTP) 2.5. Previous incarnations of CTP -- like CTP 2.0 -- provided users with the ability to recover from indexing failures. Prior to the introduction of CTP 2.0, failure meant that the indexing process had to be completely restarted, which made it time-consuming and difficult. With SQL Server 2019, users will be able to 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 Always On Availability Groups that increased the number of synchronous replicas. Users are able to configure groups of up to five replicas and program them to have automatic failover within the group.
In addition to indexing capabilities and Always On Availability groups, 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 still in preview mode, which is free to download.
The other SQL Server versions include SQL Server 2017. SQL Server 2017 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 5.6 -- can be downloaded from Oracle's website.
MySQL 5.6 provides users with methods for encrypting stored authentication credentials and checking password security. These new updates to security make the system harder for hackers to access sensitive information. The Enterprise edition of MySQL 5.6 offers a set of encryption functions based on the OpenSSL library. This new set of functions allows users to use public-key asymmetric cryptography to add data protection; create public and private keys and digital signatures; perform asymmetric encryption and decryption; and use cryptographic hashing for digital signing, data verification and validation.
Table-partitioning enhancements were also added to MySQL 5.6. The maximum number of partitions now includes all partitions and subpartitions in the table and has been increased to 8,192. MySQL 5.6's performance schema now includes instrumentation for table input and output and event filtering by table based on schema, table names or thread.
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