RDBMS databases form the backbone of most business software solutions. When people discuss SQL (Structured Query Language), it’s in reference to an RDBMS system. Applications store all their important data there. The databases (usually) power all the searches. A good database can bring a system to a higher level. A bad database can bring a business to its knees. For any developer or enterprise embarking on a new software venture, one big question is “which database vendor should I use?”. In the early days of computing, database vendors such as IBM and Oracle reigned supreme. That has changed in recent years. MySQL (open source solution recently purchased by Oracle) and Microsoft’s SQL Server have gained market share. According to a 2019 StackOverflow survey, they hold the #1 and #3 rankings respectively in most popular databases used by professional developers. But which one is best for YOUR business? MySQL vs SQL Server presents a tough and complicated decision!
Main Differences Between MySQL and MS SQL Server.
- Both integrate well with most popular operating systems and it will depend on the existing infrastructure or your desired infrastructure as to which one is best suited (MySQL for Linux- MS SQL for Windows)
- MySQL has a free community version that is more than capable of production loads but will cost more for additional support. Similarly, MS SQL has a basic offering for developers that is potentially limited for production load and you will have to upgrade at greater cost.
- From a green-field point of view MS SQL and Microsoft Azure offer a comprehensive cloud solution. MySQL still holds its own but the Microsoft combo is hard to beat.
- MS SQL Server offers a more robust tool set with its SQL Server Management Studio (SSMS) In particular when it comes to security, reporting and analysis through a simple GUI.
- Hand coding APIs for either of these database systems can be a tedious task. If you require a rapid development cycle and want to cut down on bespoke APIs you can create a MySQL or MS SQL API in minutes.
So Which Database is Right For You?
Obviously there is a great deal of information to unpack here. The “it depends” caveat still looms largely over the entire decision process. A general rule of thumb for approaching this decision might be:
- If you are a Linux shop, already using pieces of the LAMP stack, MySQL would fit in with that nicely.
- If you are a Microsoft shop, already invested in .Net and the Windows ecosystem, SQLServer seems like the obvious choice.
- If you are completely green field, or looking to make a clean start, the evidence above leans towards SQL Server. Microsoft is building momentum in the cloud arena with Azure’s SQL Database. They are continuing to embrace other ecosystems (eg, Linux) and open source. And SQL Server features a better toolset, the more robust TSQL, and arguably better performance.
An In-Depth MySQL vs. MS SQL Server Comparison
To select the best database solution for the task at hand, one must weigh several factors, including:
- Operating System
- Cloud Support
- Tool Support
Most companies have already invested time, money, and expertise in their computing infrastructure. This includes their choice of Operating System (OS). Usually that consists of “Windows vs Linux” (although cloud computing is beginning to change that). When selecting a database to power your business, the OS your company is already is a big deciding factor. Here’s how that looks for MySQL vs SQL Server:
SQL Server was originally written for the Microsoft Windows operating system. In recent years, Microsoft has made strides in embracing the open source community, and providing support for both Linux and Mac OS. The most recent versions of SQL Server run on Linux natively, and will run on Mac OS within a Docker container.
Advantage – It Depends
Honestly, this one depends on what OS your company is already using. While both platforms support the two major operating systems, there are “home court advantages” to each. If you’re already a Windows and .Net shop, it probably makes sense to use SQL Server. If you’re a Linux and Python/Java/PHP shop, MySQL might be the better choice.
Cost is always a factor when making decisions about software, and an enterprise-grade database can be one of the biggest expenses. Both solutions offer a “free” tier. From there, the price depends on how powerful a database you need, and what sort of support you’re looking for. It may be tempting to try and save money, and go for the free tier. But if the database is mission critical, paying for advanced monitoring, backup, and support is probably worth the cost. Here’s the breakdown:
MySQL’s free offering is the MySQL Community Edition. It boasts a decent number of the standard features. This would work fine for a developer learning the platform. It should also meet a smaller system’s needs.
For a more complete feature set (as well as Oracle support), you need to shell out some bucks. According to recent pricing, this can run you anywhere from $2k-$10k per server, annually. There are 3 different tiers (Standard Edition, Enterprise Edition, and Cluster CGE). Choosing between them largely depends on the complexity and scale of your data needs.
SQL Server’s free offering comes in two flavors – here’s how Microsoft describes them:
- Developer – “Full-featured version of SQL Server software that allows developers to cost-effectively build, test, and demonstrate applications based on SQL Server software.”
- Express – “Free entry-level database that’s ideal for learning, as well as building desktop and small server data-driven applications of up to 10 GB.”
In a nutshell, Developer edition gives you everything you need, as long as you’re not using it in production. Express has a smaller feature set, but it’s license allows for production use. Like MySQL, if you’re business needs and scale are smaller, Express may do the trick.
If you need a more robust feature-set, you’re going to have to pay for it. According to Microsoft’s pricing page, you can pay anywhere from $931 to $14,256 per core. There is a wide discrepancy in pricing here, and your business needs will dictate how much power you need.
Advantage – It Depends
Once again, the best choice here depends on the needs of your business. Both solutions offer a free tier. Both have complicated pricing schemes beyond that. Consult with the sales department of each to get a final determination of what you need, and what you would end up paying.
In recent years the computing landscape has undergone a dramatic transformation. Cloud computing is all the rage. The “Big 3” providers are currently Amazon Web Services (AWS), Microsoft Azure, and Google Cloud. Each offer robust services, such as storage, computing, and yes, SQL Databases.
This revolution has impacted the first two bullet points of this article (OS and Cost). The cloud provider manages the OS and server complications, and offer “pay as you go” plans to avoid the major up-front costs. In a way, this shift has diminished the importance of OS/Cost. Instead, other considerations such as performance, tool support, feature set are bigger factors. Here’s how the offerings stack up:
All 3 of the “Big 3” cloud providers support MySQL with the following offerings:
- AWS offers MySQL on their Relational Database Service.
- Azure offers MySQL on their Azure Database for MySQL service.
- Google offers MySQL on their Cloud SQL offering.
Each service claims easy administration, high scalability, robust security, and pay-as-you-go pricing. This article offers an in-depth comparison of MySQL offerings across cloud providers. It does not attempt to compare pricing due to differences between the providers.
It seems cloud pricing also falls into the “it depends” category – there is no “one size fits all” answer. The best approach might be to first create MySQL environments in several clouds. Then, load test typical usage for your business operations. and determine how the different costs shake out.
It is worth noting that Oracle (owner of MySQL) ALSO features a cloud offering for MySQL. This might be worth exploring due to Oracle’s “native” support of MySQL. However, a SQL database is only one piece of a software architecture landscape. A system still needs storage, computing, and security services. Oracle’s not currently a market leader for providing these services. For that reason, Oracle’s cloud may be a risky choice for hosting MySQL.
It is ALSO worth noting that all the cloud providers also offer Virtual Machine services, upon which you can run your own MySQL instances. This is an option for customers that want more control over their databases. This approach requires more expertise (and is more expensive).
Similar to MySQL, each major cloud provider has a SQL Server offering:
- AWS offers Sql Server on their Relational Database Service.
- Azure offers SQL Server on their SQL Database service. While SQL Server runs under the covers, the SQL Database offering abstracts much of the server administration away from the end user.
- Google offers SQL Server on their Google Cloud Platform offering.
An interesting twist here is that one of the major cloud providers (Microsoft) is also the creator of SQL Server. While all 3 providers offer strong choices, there’s a sense of a “home-court advantage” with Microsoft.
Like MySQL, you could also pay to host Windows VMs in the cloud, and self-host SQL Server. This also comes with the same expertise requirements and additional cost concerns.
Advantage – SQL Server (SQL Database)
While either solution works as a cloud offering, the combination of Microsoft Azure and SQL Database is hard to beat. If you are ALREADY using another provider, or have ALREADY invested in MySQL, then that would still probably be your choice. However, coming into a green-field decision, the Azure/SQL Database choice is pretty compelling.
Database performance is crucial to any software application. If the database doesn’t respond in an expedient fashion, the entire system bogs down. This leads to issues like poor user experience, delays in operations, and lost money.
Database performance depends on an IMMENSE number of variables. Slight differences in workloads can skew advantages one way or another. Minor tweaks can improve results. A well-designed database is worth its weight in gold.
MySQL and SQL Server both tout extensive performance and scaling capabilities. After scouring the web for comparisons between the two, SQL Server seems to have the advantage.
Here are some hard numbers:
- Comparative Performance Analysis of MySQL and SQL Server Relational Database Management Systems in Windows Environment
- Count Distinct Compared on Top 4 SQL Databases
An additional consideration is MySQL is Oracle’s “entry level” database. For high performance needs, Oracle would steer you towards their flagship database offering. On the other hand, SQL Server IS Microsoft’s flagship offering.
Advantage – SQL Server
While not a slam dunk, SQL Server’s slightly better numbers, and “flagship” status give it a slight advantage here.
In order to work with a database, one needs a good toolset. The database itself is a background process without a GUI. However, in order to develop and support the database, you need to interact with it. Both MySQL and SQL Server provide front end clients for this purpose.
MySQL’s client application is MySQL Workbench. Workbench has offerings that run on Windows, Linux, and MacOS. It offers several important database management tools, including:
- Database connection and management
- SQL editor and execution
- Database and Schema modeling GUI
- Performance monitoring and query statistics
SQL Server’s client application is SQL Server Management Studio (SSMS). While SQL Server runs on Windows, Linux and MacOS (via Docker), SSMS is ONLY available on Windows machines. Note that Microsoft provides a Visual Studio Code extension to execute SQL from a Linux-based machine.
SSMS has a more robust feature set than MySQL Workbench. This includes:
- More extensive Database management tools. Includes a robust set of security, reporting, analysis, and mail services.
- A powerful execution plan visualizer. This allows easy and fast identification of performance bottlenecks.
- Integrated Source control.
- Real-time activity monitor with filtering and automatic refresh.
Advantage – SQL Server
Both offerings provide “the basics” (ability to execute SQL and view/manage databases), but the SSMS experience is far superior. Seasoned Database Administrators (DBAs) may wish to manage their databases with scripts and SQL. But many users want a simple GUI to perform these tasks. This is an area where SSMS shines. Also, the execution plan visualizer makes performance bottlenecks easy to fix. That can pay for itself time and time again.
Both platforms utilize SQL to interact with their schema and data (with some minor differences). However, they differ when it comes to runtime languages interfacing WITH the database.
For example, in a typical server architecture, you might have:
- Database – SQL reads/writes data
- App Server – C++/PHP/Perl/Python/.Net/Java provide business logic, and interface with database
Here’s some of the differences to consider between the two systems:
- SQL Server supports T-SQL, a proprietary extension to SQL. This enables concepts such as Procedural Programming, local variables, string/data processing functions, and FROM clauses in UPDATE/DELETE statements. Basically, you can do more with your SQL.
- Runtime languages – both systems support connecting using the major programming languages (C#, Java, PHP, C++, Python, Ruby, Visual Basic, Delphi, Go, R). There are some articles on the web claiming that less-popular languages such as Eiffel are only supported on MySQL, but as long as you can connect using ODBC, both databases are available.
- If using a .Net language (C#, F#, Visual Basic, etc), once again Microsoft provides a “homecourt advantage”. Microsoft wrote the ADO.Net library for SQL Server first. ADO.Net works with MySQL, but it really shines with SQL Server.
- SQL Server also provides the additional (and possibly controversial) mechanism of invoking .Net code FROM a stored procedure. This can be a powerful mechanism for injecting all sorts of functionality within your database. It also allows you to shoot yourself in the foot. Proceed with caution here.
DreamFactory Creates MySQL and MS SQL APIs Instantly
With your Database decision made, what’s next? Most business applications consist of choices around the following rough architecture:
- Database (hopefully we’ve advanced understanding of that here)
- Middle Application Programming Interface (API) layer (connects Database to front end GUI)
- GUI layer
Wouldn’t it be nice to knock out work in the middle layer automatically? Some sort of code generation mechanism that made all the database information instantly available for a GUI to consume? Friends, that day is upon us! The good folks at DreamFactory have built a system that does just that.
With some configuration, and a few clicks of a mouse, DreamFactory will turn your database objects into a REST API. They support all sorts of databases (MySQL, SQL Server, and a long list of others). They even auto generate the documentation. Watch this video, and prepare to breath a sigh of relief. DreamFactory just removed a big piece of heavy lifting off your plate!