Many database administrators (DBAs) tell me that one of the main reasons they run their Microsoft SQL Server instances on Red Hat Enterprise Linux (RHEL) is to reduce costs. The fact is, many SQL Server shops have a proliferation of smaller instances of Express and Standard editions in their environments.
With Red Hat Enterprise Linux, these instances can be consolidated onto a single RHEL system as containers, and with the new container licensing for SQL Server 2022 Standard Edition from Microsoft, there’s never been a better time to take advantage of this. Under the new pricing, Microsoft customers can license SQL Server Standard by virtual operating system environment (VM guest) and, as long as they have active Software Assurance from Microsoft, they can run an unlimited number of containers containing SQL Server within that virtual guest.
In this blog, we’ll learn how to set up an environment where DBAs take advantage of these savings by consolidating SQL Server containers and running them in a single host or virtual guest. In the past, DBAs might have achieved something similar with a technique known as instance stacking, where multiple instances of SQL Server were installed on the same Windows instance. We’ll be achieving the same thing by installing separate discrete containers on RHEL.
As with instance stacking, organizations with Software Assurance from Microsoft can still only pay for one license for SQL Server, still only have one operating system environment to pay for (this time a RHEL subscription), and (as with multi-instance) only have a single host OS to patch and maintain. So IT administrators and DBAs really do get all the advantages of a multi instance deployment.
So what’s better about taking the container approach? Three key points come to mind. First, performance tuning is easier, because it’s easier to dynamically assign CPU, networking and storage resources to discrete containers. Second, reboot planning can require less coordination, because in many cases we can update one container at a time without needing to reboot the entire system. Third, there are fewer potential security risks because administrators don’t need to give DBAs host system administration access to allow them control and manage their container environments.
It’s for this last reason that we’re going to demonstrate SQL Server container setup on RHEL with Podman in two parts. In the first part, as an IT administrator, we’re going to set up the host environment to more easily deploy and manage containers using the RHEL web console. This must be done as the system administrator (root) which is why we’re focusing on the IT administrator. We’re going to start off from the command line to make sure that our system is just generally ready to be a good container host.
If you’re a DBA without root access, stay tuned for my next blog where we’ll walk you through SQL Server container deployment. This only requires a basic user account on the system, one without administrative access. We’ll set that account up as part of the first phase, then we’ll use it to login to the graphical RHEL web console in the second, so that our SQL Server DBAs have an easy time of getting their applications up and running without requiring a lot of specific knowledge of Linux.
First, in order to use the RHEL web console to manage containers, we need to make sure that the software I’ll need is installed on the system.
We’ll use the dnf install command to make sure that cockpit and podman packages are installed:
$ sudo dnf install cockpit podman cockpit-podman tuned-profiles-mssql
There are several methods that can be used to connect to the web console, including Satellite integration with the web console, SSH access methods, or running the Cockpit web server on hosts. For more information on these options, refer to part 1 and part 2 of the blog series on connecting to the RHEL web console.
In this example, we’ll use the Cockpit web server, so we’ll enable the cockpit systemd socket with the following command:
$ sudo systemctl enable --now cockpit.socket
We then make sure that the RHEL Web Console cockpit service is enabled to open port 9090 with:
$ sudo firewall-cmd --add-service=cockpit --permanent $ sudo firewall-cmd --reload
Now we’ll add an account for the SQL Server DBA to be able to use when logging in to the RHEL system. In this example I call the account mssqldba:
$ sudo useradd mssqldba
We’ll also need to set up a password for the mssqldba account. When we run the passwd command as below, it will prompt us twice for a password. The passwords will not be displayed as they are typed in as seen below:
$ sudo passwd mssqldba Changing password for user msqldba New password: Retype new password: passwd: all authentication tokens updated successfully.
Next up we’ll enable lingering, to make sure that we’re able to restart end-user containers at boot time instead of at login time:
$ sudo loginctl enable-linger mssqldba
The linger capability we’ve enabled above provides a simple and easy way to get containers to restart without requiring system administrator access. Note that using linger for auto-starting containers may not work in some circumstances, such as when ecryptfs, NFS, or 2FA are used on a user account.
As a final step, we’ll want to tune our host so that we can get the best possible performance out of SQL Server. We do this using tuned-admin:
$ sudo tuned-adm profile mssql
At this point the mssqldba account we’ve created will be able to deploy and manage containers without root access. What’s more, because we’ve enabled the RHEL web console, they’ll be able to do this without needing a whole lot of knowledge about the Linux command line.
Stay tuned for the second blog in this series where we’ll walk you through how a DBA can deploy Microsoft SQL Server containers using the graphical experience of the Red Hat Enterprise Linux web console .
About the author
Louis Imershein is a Product Manager at Red Hat focussed on Microsoft SQL Server and database workloads. He is responsible for working with Microsoft and Red Hat engineering to ensure that SQL Server performance, management, and security is optimized for Red Hat platforms. For more than 30 years, Louis has worked in technical support, engineering, software architecture, and product management on a wide range of OS, management, security, and storage software projects. Louis joined Red Hat as part of the acquisition of Permabit Technology Corporation, where he was VP of Product.