SQL Server Migrations & Upgrades

Should I Migrate My SQL Server To Linux?

Updated
6 min read
Written by
Mark Varnas

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!

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Leave a Comment

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials