With SQL Server 2017, and now SQL Server 2019, SQL Server is available on Red Hat Enterprise Linux, SUSE Linux Enterprise Server (SLES), and Ubuntu.
For many technical professionals, the ability to run SQL Server on the same open-source operating system as the rest of the application is a dream that Microsoft made come true.
With the release of SQL Server 2019, Linux support now extends to new features, support, and capabilities.
Let’s look at a few notable things before embarking on the brave new world of Linux.
Understand the difference in a Linux distribution
Just because you can get SQL Server to run on non-supported Linux distributions such as CentOS doesn’t mean you should.
Understanding what distributions are best for enterprise deployments can ensure what you have deployed into the environment can withstand the demands the business puts on it.
Each distribution may have its unique requirements and quirks.
Stick to the supported versions and the user experience will be more satisfying along with new features available in regular release intervals.
Supported versions and performance enhancements
Until the date of this post, Azure Marketplace PAYG (Pay As You Go) supports images for SQL Server 2019 on:
- RHEL 8.0
- Ubuntu 18.04
- SLES 12 SP5
Deploying these images ensures you get the latest functionality for both SQL Server 2019 and the operating system.
Here are the key performance improvements:
- Filesystem Improvements: SQL Server 2019 on RHEL 8.0 and SLES 12 SP5 images include operating system improvements to XFS filesystems, featuring Forced Unit Access (FUA bit) introduced around general kernel version 4.18.
This enables reduced forced filesystem flushes and can improve performance by 20-40% for I/O-bound workloads.
- HA Repo Pre-Enabled: The images come with the HA repo pre-enabled, allowing the deployment of a pacemaker cluster environment for setting up availability groups on SQL Server 2019 without incurring double subscription costs.
Setting up cross-platform availability groups
Also, you can use a combination of Windows and Linux nodes with the availability group configured with cluster type=none.
It’s also referred to as a read scale replica or clusterless availability group.
This means that it does not offer automatic failover though you can have both sync/async data movement.
Here are the instructions on how to configure Availability Groups on SQL Server with RHEL VM on Azure.
Does SQL Server run better on a Windows OS?
You can expect pretty similar performance and it shouldn’t be the primary consideration for choosing OS/platform to host SQL Server.
SQL Server is the same binary application running on either Windows or Linux.
In the case of Linux, it uses a very lightweight Platform Abstraction Layer (SQLPAL) to run the same Windows binary.
If you see the TPC-H results below, the SQL Server on Windows and Linux have very similar results as well, topping in most categories.
Published by TPC.org, the performance metric reported by TPC-H is called the TPC-H Composite Query-per-Hour Performance Metric (QphH@Size) and reflects multiple aspects of the capability of the system to process queries.
The command-line rules in Linux
As this is Linux, the command line is still king.
It will be essential to know how to install and update your server and applications if a Linux administrator is not already on staff in your organization.
You will need to learn how to update your existing PowerShell scripts to work on Linux or learn how to write Bash scripts.
This is commonly done from a terminal editor, and knowing how to use it will give you credibility when working with Linux and deter too many questions during day-to-day tasks or in the midst of critical work.
Tools for Database Administrators (DBAs)
- Database Connection: As a DBA, you can connect to your Linux SQL Server 2019 databases using Azure Data Studio (preferred) or SQL Server Management Studio (SSMS).
- Development Tools: While Server Management Studio cannot be installed on Linux, developers can access SQL Server 2019 on Linux using Visual Studio Code with the SQL Server extension, enabling full use of SQL Server Data Tools (SSDT).
Remember, SQL Server Reporting Services (SSRS) is not supported on Linux, but you have SQL engine and SQL Server Integration Services (SSIS).
These points above can be a game-changer depending on your needs.
Conclusion
SQL Server on Linux has the same licensing model as on Windows.
The only cost savings would come from Operating System (OS) prices.
The functionality and performance are nearly identical across operating system platforms for SQL Server.
The primary consideration should be the ecosystem you have in terms of administrative capabilities.
If the entire app ecosystem is based on Linux and the only Windows Server being maintained is for SQL, it may make sense to consider hosting that on Linux, and vice versa.
Additionally, if you have existing workloads using other DBMS that are based on Linux, migrating them to SQL on Linux could be advantageous.
This would reduce the number of changes required in the ecosystem.
Agree? Disagree? Comment below!
