How to Linux for SQL Server DBAs — Part 1

As running SQL Server on Linux becomes more common, DBAs must learn and become comfortable with the Linux OS. In this article, Kellyn Pot'Vin-Gorman demonstrates how to create a SQL Server instance running in Linux via a Docker container.

Walt Whitman said “Simplicity is the key to harmony” as it is for technology, too. As more companies speed up their development cycle with DevOps, elimination of complexity is among the top priorities to accomplishing this. Windows is still the champion of the user world, but for the DevOps enterprise environment, a single operating system can provide a powerful step in shortening automation and delivery cycles.

Microsoft’s Entrance to the Linux Arena

With this shift to more agile development, Microsoft has embraced Linux to assist in aligning them with significantly more of the DevOps community. Linux was introduced with SQL Server 2017 beta for on-premises and this brings SQL Server in as an option for DevOps solutions using Linux without multi-operating system complexity.

This opens a new challenge for SQL DBAs as the comfort they once had with Windows is no longer there. SQL Server, unlike Oracle, is a product that was built by the same company as the operating system, so it was more embedded into the OS layer. Many performance tools were dependent upon the version of the OS and simultaneously, much of the internals of SQL Server were hidden inside the database tier to create a boundary between server administrator and database administrator.

With the move to Linux, the SQL Server DBA may find they don’t have the on-premises administration professionals to support them as Oracle DBAs have had in the past. Windows administrators may be as hesitant to work with Linux as Linux administrators have hesitated to work with Windows. If you’re a database professional working with an IaaS, (Infrastructure as a Service) or on-premises solution and don’t have the expertise in the platform your database resides on, this can have a considerable impact to the level of support you are also able to offer.

History Lesson

There were a few stops and starts before Linux came to be the monolith we know today. DOS was well known by the 1980’s, the operating under the covers of every Windows machine in the world by the 90’s and Unix, no matter what the flavor, was the powerhouse behind enterprise hosts. The chipsets they ran on weren’t compatible and the worlds stayed separate until a new version of Unix, called Minix was introduced by a Dutch professor, Andrew Tannenbaum in the mid-1980’s. This new OS ran on Intel 8086 processors, and its goal was to offer Tannenbaum’s students an operating system to work and build on. As the operating system became more well-known in Europe and its user group grew, one of those students became passionately devoted to the code base. That developer was a young, Finnish student, named Linus Torvalds.

Minix has other relationships to Linux, as both projects also go back to the GNU project, (and the GNU C Compiler) a focus to offer free and quality software, and at the GNU Project’s head, Richard Stallman, like Andrew Tannenbaum, had a God-like status in Linus’ world.

With the goal of creating an operating system robust enough to take on the industry, Linus began to write his own operating system, following in the footsteps of those he admired. Linus took what he viewed was great about Minix, but also made a significant change–his version of Unix was truly free, where Minix required a license. In 1991, Linus Torvalds introduced his new operating system to the masses. He originally named the operating system Freax, but the name didn’t stick, and it was soon renamed by his loyal followers to Linux. 

As the product was truly open source, the generation of developers in the 90’s began to feed into what Linus had already put his heart and mind into. One of the most interesting aspects of the connection between Minix founder, Andrew Tannenbaum and Linus Torvalds, is that Tannenbaum once confronted Linus on the Minix User Group and declared,

“…Be glad you are not my student. You would not get a high grade for such a design. Linux is dead.”

Thankful for the IT industry, Linus didn’t listen to the man he had once admired and continued to work with the Unix community to build out the Linux OS. With over 20 million lines of code for the kernel, including versions like Redhat, Debian, Ubuntu, Suse and others, it has become the OS that makes the world go around with versions that are still open source and enterprise.

Getting Your Linux Going

As this series of articles are published, it will be important that you have a way to do some hands on with Linux, not just read about this powerful operating system. To accomplish this, my recommendation is to download Docker for Windows.

Choosing to use Docker over a VM or a cloud deployment might seem odd at first, but I’ve found that Docker is the perfect option from the testing and classes I’ve led. The Docker image is light–using a minute number of resources on any laptop. Access to the docker image isn’t dependent upon Wi-Fi or the user having a cloud account. The image, for the most part, offers an experience that is incredibly similar to a full Linux server in functionality for no additional cost.

Once you’ve downloaded Docker and have it installed, create a Linux container with SQL Server on your workstation, (thanks to Microsoft) to work with and build your knowledge. Start by opening a Command Prompt, cmd from the search option in Windows.

NOTE: Microsoft recommends PowerShell for installing and working with Docker, but I’ve often run into problems and recommend the Command Prompt instead. Be sure to remove any line continuations (back ticks `) before running their PowerShell commands in the Command Prompt.

Creating the Docker container requires a simple, single line command, replacing the password and the container name with those of your choice:

You can follow these steps to create the Microsoft SQL Server 2017 container with Linux. I have some recommendations to offer you a high rate of success, so take advantage of these tips and read this article before creating your container. This post will cover any prerequisites as well as help you avoid common pitfalls that I’ve experienced while giving my classes.

Although this isn’t an article on Docker, there are a few commands that will assist you going forward with the management of the container, so I’ve added them here:

Task

Command

Notes

Stop or (re)start a container

Docker stop <container name>

Docker start <container name>

Docker restart <container name>

Stop is often required before dropping a container

Remove a Container

Docker rm <container name>

Removing containers are no big deal, as you can so easily recreate them.

Status of Containers and Info

Docker ps -a

 

View Logs for a container

Docker logs <container name>

Log entries scroll from earliest to latest.

Inspect a specific container

Docker inspect <container name>

Gives you all the information, port info, ip address, etc for a container.

Now that you know a bit of how to use docker, let’s cover the first facts to know about Linux:

  • Everything in Linux is treated as a file.
  • Although everything is installed as root and you are logging into the container as root, that’s not the proper configuration of an enterprise server. ROOT IS GOD and just as you would in a database server, the privileges least required to perform a task is the best practice.
  • Linux doesn’t have registry to act as an anchor for shared files or protect you from yourself when you log in, (especially root!)

Logging into your docker container is quite simple from the command prompt, (notice I didn’t say Power Shell… 😊):

You should see the following on your screen:

Although it isn’t the case for most Linux servers, the Linux image is kind enough to use the following for the prompt: username@hostname:path#

You are now connected to your Linux container with a secure shell using bash, also known as ‘Born Again Shell.’ Things to remember:

  • Linux is CASE SENSITIVE. You must use capital letters when present.
  • The wild card (*) works to shorten typing, (i.e. /var/t* = /var/tmp)
  • You can do anything to this server- YOU ARE GOD. If you delete a file, it’s GONE, (but I also discuss in my blog post on why this is a safe place to be testing this out.)
  • This is a container, so although most commands and activities are just like a Linux host, there will be a few things that don’t work.

The first commands we’ll learn are those to tell us about our environment:

Command

What it Does

Warnings or Hints

whoami

Tells you who you are logged in as

If you’re switched users, it will show you the user you ARE currently.

pwd

Directory path you currently are in

No, it’s not for password. No, don’t attempt to make an alias to try to make it password. Bad idea.

cd /

Top of the root directory, parent directory of EVERYTHING

You are root. You have rights to this right now, but in an enterprise environment, this would most likely NOT happen, unless you have a really foolish administrator.

df -h

File system, directory and storage information in appropriate size format

You can perform this same command with a -k instead of the -h if you like really big numbers to calculate from kilobytes.

ls

ls -la

ls -ltr

To list

To list all, including permissions

To list all, most recent last

Notice the differences in these commands and what DOESN’T show up when you don’t use the -a argument.

cd <directory>

cd ..

cd

Change directory

Change directory one dir up

Change directory to home dir

cd to /var/tmp, then perform the next two and see how the location changes.

touch <file name>

Creates an empty file

The file doesn’t require an extension and you can create it in a distinct directory by prefixing the filename with the directory name.

apt-get

yum

Two most common updaters and installers for Linux packages

We’ll get into these first, even though they’re listed last.

First Steps with Your Container

This container, although brand new, is a snapshot in time and it may be missing the latest updates for the OS and utilities. As Linux doesn’t have a registry, you can run the updater yourself and not have to worry about rebooting. Its just a very rare thing to have to reboot a Linux/Unix machine.

Run this to get the upgrade for your container:

Download all the latest updates for your container:

The apt-get utility will download all the critical updates for the OS and the most common updates for packages. This is done by connecting to the repositories listed in the sources.list file in the /etc/apt directory on the container host and similarly on a standard Linux server. Your admin could add new repository locations to the file if needed.

Once this is complete, then run the install command, which will install any OS or security required packages and updates.

ls Don’t be surprised if a number are noted, but nothing is done. It means your image is up to date, but the number shown at the end have been made available to your system.

0 upgraded, 0 newly installed, 0 to remove and 20 not upgraded.

Now you’re ready to install a few packages that I think are essential to a Linux host that aren’t included in the image to keep it as small and nimble as possible.

The first thing you’ll desire is the ability to view, edit, as well as view sections of files. Install the following packages:

Package Name

Utilities Included

vim

View, VI, VIM

net-tools

Networking utilities

npm

More

less

Install less separately, from the npm package

The command to install will be similar to the one you typed to update the OS:

As an example, you can run:

OR you can run:

Notice when installing more than one package at a time, it doesn’t require a ‘,’ between the package names. If you’re asked to confirm during the installation, choose Y to confirm and hit enter or N to abort.

Summary

In this article, you’ve been introduced to:

  • A light history about Linux
  • Creation of a Docker container with Linux and SQL Server installed
  • Some basic commands
  • Learned how to update your Linux container with the latest updates and a few packages.

The next article will cover user, groups, permissions, and much more! You’ll also begin to work with the server processes, log into the SQL Server, and begin to manage the Linux host.