Should I migrate my SQL Server to Linux?

With SQL Server 2017, and now SQL Server 2019, SQL Server is available on Red Hat Enterprise Linux, SUSE Linux Enterprise Server, 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, the inclusion of Linux now includes 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.

Until the date of this post, Azure marketplace PAYG (Pay As You Go) support images for SQL Server 2019 on RHEL 8.0, Ubuntu 18.04, and SLES 12 SP5. You can deploy these images to get the latest of both SQL Server 2019 functionality and operating system.

SQL Server 2019 on RHEL 8.0 and SLES 12 SP5 images include the operating system improvements to XFS filesystems, including Forced Unit Access (FUA bit). This was introduced in the Linux ecosystem around general kernel version 4.18. It enables you to take advantage of reduced forced filesystem flushes and improve performance between 20-40% for IO-bound workloads. The images have pre-enabled the HA repo. So, if you want to deploy a pacemaker cluster environment to setup Availability Groups on SQL Server 2019, you can do so without a double subscription costs.

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 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.

Figure 1 – SQL Server 2019 on Linux distributions (Azure marketplace).

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 the 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.

Figure 2- TPC-H – Top Price/Performance Results – Non-Clustered (on 05/07/2020).

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 when in day-to-day tasks or the middle of critical work.

As a DBA, you can use Azure Data Studio to connect to your Linux SQL Server 2019 databases (preferably) or SQL Server Management Studio (SSMS).

You can’t install Server Management Studio into Linux. Developers can access SQL Server 2019 on Linux with Visual Studio Code with the SQL Server extension and have 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 Operation 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 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 which are based on Linux, migrating them to SQL on Linux would reduce the number of changes required in the ecosystem.

 

Agree? Disagree? Comment below!

 

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

4.5/5

42

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.