Choosing an OS for SQL Server in Azure VMs

When you create a new SQL Server on an Azure VM, among the first choices you have is which OS to choose. In this section, we will look at some of the thought processes to go through when deciding whether to choose Windows Server or one of the Linux distributions (also referred to as distros) for your SQL Server instance.

SQL Server can run on Windows or Linux, and what might be surprising is that it is the exact same SQL Server engine code base. Of course, Windows and Linux cannot run the same binaries natively, so Microsoft built the SQL Platform Abstraction Layer (SQLPAL), which makes this a reality. For more details on SQLPAL and how it is used, the blog55 by the SQL Team offers interesting insights.

While SQL Server on Windows and Linux have the same code base, there are differences to be understood. In this section, we will look at the reasons for choosing either Windows or Linux, and then discuss the differences between the two implementations.

Reasons to choose either Windows or Linux for SQL Server

This book's focus is on using Microsoft SQL Server on an IaaS platform, and the fact that SQL Server runs on either OS may sound fishy to you. You may be suspicious that they want to get you on Linux and then lure you to Windows over time. It is important to realize that this is not the case: they are both the same SQL Server product and are equally supported.

The primary deciding factor on which OS to use is actually based on the comfort level of the company that will be using it. While there are a few differences in feature sets, there is no compromise in quality or performance with either choice. Much like the decision process in choosing between the IaaS model or the PaaS model, the choice to use Linux for the server OS is more a matter of your organization's needs, rather than being based upon any objective benefit.

If an organization is not comfortable with Windows, it is going to be harder to implement SQL Server on Windows, even using a pre-built image. If you are comfortable with Linux, the process to install and configure SQL Server is more like what you are used to when installing Linux software. As we will see in the following section, while the functionality to the user is going to be almost exactly the same on either OS, the differences in how you configure SQL Server are significant.

One thing to note is that Open-source Software (OSS) developers often prefer Linux because they can install and run additional OSS packages in the same environment with their database server as part of a solution. These packages may or may not run on the Windows platform. SQL Server on Linux lets them reap the benefits of SQL Server's mature data storage engine to build applications that run faster, are more secure, have tight integration with machine learning, and so on.

Note

The goal of SQL Server on Linux is not to kill the Windows version, but to broaden the audience, making the product speak the implementation language of a new audience while providing the same SQL Server T-SQL language to all.

The only reason that you may not be able to feasibly choose Linux for your OS is that some features are not yet supported (for example, Merge Replication, or having multiple instances on a single server), some that will likely never be (such as FILESTREAM and FileTable, both of which interface tightly with the Windows OS), and others that will require new binaries to be created (or another abstraction layer), such as Analysis Services. Any need for these features, even something like Reporting Services, which runs as a separate executable, would currently require an entire extra license for SQL Server to run on a separate Windows Server, which could be cost-prohibitive.

The final issue to bear in mind when choosing an OS for SQL Server is cost. Cost is a very complicated discussion because costs come from many different places, some more obvious than others. There are obvious cost differences you can compare empirically, such as the hourly rate of running a VM on Windows versus one with Linux. However, if you must hire new staff and train them on how Windows works, or pay consultants every time something isn't working, the costs may be prohibitive. The same can be said about Linux.

In the end, the most compelling reasons to choose between Linux and Windows are to do with your comfort level with each OS and whether you need specific features that may not be available on Linux (a list that we noted earlier shrank considerably with SQL Server 2019.)

Differences between SQL Server on Linux and Windows

Beyond the obvious deep differences in how the different OSes behave (even the different versions of Linux have their own ways of doing things), there are some differences between the Windows and Linux versions of the product.

In this section, we are going to highlight some of the differences between SQL Server on Linux and Windows, whether you use a pre-built SQL Server VM image from Azure or install SQL Server on your own on-premises computers. The following table contains a list of key differences between SQL Server on Windows and Linux:

Figure 1.2: Differences between SQL Server on Linux and Windows

A few of these differences warrant a little bit of discussion; most importantly, we'll consider the way you install and configure SQL Server and some feature-set limitations of SQL Server on Linux.

Installation/configuration

While the experience of the typical user employing T-SQL or using an application will be very nearly 100% the same, there are some major differences between SQL Server 2019 on Linux compared to running it on Windows. Obviously, the biggest difference is that in Windows, most SQL Server DBAs will be used to working with a GUI rather than the CLI. Hence, changing from Windows to Linux can be very a large paradigm shift, particularly during installation but also even when choosing where to locate database files. However, if you are used to Linux, the method of server and instance installation/configuration should generally be obvious to you; the same goes for how the file system works.

While it is true that there is a version of Windows that you manage mainly from the command line (Windows Core) that SQL Server can execute on, it is not typically used because Windows administrators are generally used to managing the server via the GUI (in the same manner as their Windows and even Macintosh computers that they regularly use). Even then, however, the commands to install on either platform differ in that the Core SQL Server installation is one executable with many parameters, rather than requiring multiple commands to install different features.

When you install the Linux image with SQL Server the first time, it will be necessary to access the server via what is basically a command terminal. Features are added and configured individually using command-line tools instead of setup.exe. Once you have the server installed, you can access the SQL Server instance on the Linux computer using SSMS, Azure Data Studio, SQL Server Configuration Manager, and the other GUI tools on your Windows computer. If you do wish to run tools on a Linux GUI, SSMS will not work, but Azure Data Studio56 will.

Feature set

Probably the most compelling reason for choosing Windows over Linux is if you need some of the external services that are not on Linux. While the engine is the same, there are several services that have their own binaries and are not a part of the core SQL Server engine. For SQL Server 2019, this list includes:

• Reporting Services
• Analysis Services
• Data Quality Services
• Master Data Services

You would need another SQL Server license to run these services on a different computer, which might increase your costs greatly. None of these services are needed for a typical OLTP database, but if you are looking to implement a business intelligence (BI) solution together with SQL Server on Linux in Azure VMs, cloud-based options to augment your VM include Power BI and Azure Analysis Services.