7 Things Every SQL Server DBA Should Know About Linux

Problem

You may have heard that SQL Server 2017 runs on *gasp* Linux! Moving from Windows to command-line Linux is like moving across the country from a major city to a small town. All the modern conveniences are there, but the names are unfamiliar and the scenery is a bit Spartan. It’s easy to get lost and lost and the natives talk funny. You may even find a nice graphical interface, but even that speaks with an accent. So, in this tip we will help you make the transition and show you things you need to know about Linux.

Solution

Database administrators, the successful type anyway, are pretty smart and adaptable professionals. With a good guidebook and some helpful phrases they’ll do just fine once they have time to acclimate. Here is a list of things about Linux that will make your experience on a new platform more productive and enjoyable.

The root account

“Root” is the administrator account that owns your server. Depending on your situation you may have access to the root account, or you’ll find that it is jealously guarded by the bearded guy down the hall. There is a middle ground that will get you what you need and is less dangerous than logging in as administrator all the time. Linux has a utility called “sudo” (super user do) which can be configured to allow you to run a limited number of commands as root without giving you full run of the server. Even if you have the root password the best practice is to configure your regular user account with sudo to allow the few commands you will need for your day-to-day work, saving root for patches, upgrades, and installs that require occasional unlimited access to a larger variety of commands.

Case sensitivity

One of the most frustrating differences moving from the Windows environment to Linux is case sensitivity, especially when creating and navigating the directory tree. The directory DataFiles is not the same as datafiles or dataFiles. Windows will happily ignore the difference, but more than one new (and even experienced) Linux user has been tripped up by this shifty change in case. This should be one of the first things you look for when debugging.

Environmental Variables

Environmental variables will not be a new concept, but they are much more pervasive in Linux and many of them are set for you in advance and assumed to exist. The command “env” will list them out for you, take a few minutes to examine its output and familiarize yourself with your system’s defaults and what they mean. You have to be careful not to accidentally overwrite them in your scripts or debugging will be significantly more difficult. HOME, USER, PATH, and TERM are examples that are expected by many commands to be set correctly (and automatically). The convention is to name all of your environmental variables in upper case, though this is not set in stone.

The bash shell

A PowerShell window or cmd are your only command line options in Windows. Linux has a wide variety of shells or command-line interpreters. The bash shell is the most commonly used, but there are many others such as sh, ksh, csh, fish, and zsh which are used on different distributions for different purposes. Some are similar to bash, but some are wildly different. Bash or its older brother sh are by far the most common and most scripts you find will be written for one these two shells. There are also common scripting languages such as python and perl you can use for administrative tasks, but they are not used as a user interface. Bash has many, many useful features and there are entire websites and books dedicated to it, I have a dog-eared copy of a bash reference on my desk that I refer to often when trying to automate a task. Find a good Bash reference and put it on the shelf next to the PowerShell and SQL Server internals books that you grab without looking ten times a day.

Open Source

Open source software is common enough in the Windows world, CodePlex and GitHub are great examples, but Open Source in the Linux world is not so much a kind of software license as a way of life. Linux is the poster child of the Open Source movement and as such is the platform of choice for many projects. The PowerShell and CodePlex communities reflect this ethos by sharing projects around the internet, but still have a closed-source root (Windows). You can find all kinds of useful tools available pre-compiled as .rpms or .debs for your platform or you can download the source and roll your own (if you’re feeling adventurous). When you find a project useful, and if you have the skillset, consider contributing back your changes and additions.

Keeping Files Secure

The file system security model used in Linux is a bit simpler than Windows, it considers three levels of ownership and three levels of permissions for each level of owner. A file has an owner, a group, and the world (or other) and each of these may read, write, or execute the file. You will see the permissions of a file represented as a series of ten “d”, “-“, “r”, “w”, and “x” characters at the beginning of the file listing.

There are a couple of other special cases, but these are the basics. The first character will be a “-” for a regular file or “d” for a directory. The next three are the permissions of the file’s owner, the first is read, the second is write, and the third is execute. They will either be enabled, indicated by a “r”, “w”, or “x” in that position, or disabled indicated by a “-“. The next three represent group permissions following the same model and the last three are the world or other permissions. For example “-rw-rw-r–” means that the owner can read and write the file, anyone who is has the same group as the file may also read or write it and anyone may read it, but no one can execute it.

File Permissions are often expressed as a three-digit number based on the last nine positions of the permissions string and can be easily calculated. The “r” = 4, the “w” = 2, and the “x” = 1, just add them up for each set. The above example would be 664. A file that can be read, written, and executed by anyone has “-rwxrwxrwx” or 777 permissions, a file that can be read and written by anyone is “rw-rw-rw-” or 666, a file that can be read, written and run by just the owner is “rwx——” or 700.

A couple of commands that are useful when dealing with file permissions are chmod and chown, which change the permissions, owner, and group of the file respectively. To change a file to have 777 permissions you would “chmod 777 filename.ext” (chmod also accepts another format for permissions which you can find in the man page).

Only the root account, using chown (remember, sudo makes you root for just one command at a time), can change a file’s ownership or group. The command “sudo chown owner:group filename.ext” will change the ownership of the named file to the owner:group pair specified. There are other options for chown which can be found in the man page.

Life without a GUI

Navigating your server without a GUI may seem clunky at first. If you’re running Linux on your own laptop or desktop you probably have a Gnome or KDE environment to work with, but most server installs will not contain the GUI for space and security reasons. Usually your Linux server will only be available via a terminal window using ssh so it will be worthwhile to spend some time familiarizing yourself with the command-line environment. I’m a horrible typist and have found several command-line shortcuts extremely helpful. When you find you have a long command that you type repeatedly you can create an alias for it or enclose it in a small shell script placed in your path. For example, I need to start and stop my VPN connections often and created nice two-letter aliases to replace the full commands. Further, “vpnstat” is a short perl script that tells if I the VPN is up or not.

There is no penalty for double-dipping your shortcuts, here is the perl code run by the alias “vs”

#!/usr/bin/perl
@ps = split(/\s+/,`ps -ef|grep vpnc|grep -v grep`);
 
if ($ps[8]){
  print "running " . $ps[8] . " vpn\n";
}else{
  print "no vpn running\n";
}

Summary

SQL Server 2017 on Linux provides a fantastic opportunity to expand your skillset. Even if you don’t plan to use it in your own environment a couple of hours of research and practice will pay big dividends if you ever need to interface with non SQL Server databases running on the Linux platform.

Next Steps

Familiarize yourself the Linux command line and its options- they will soon become second nature.
Read the dozens of tips available on MSSQLTips.com for running SQL Server on Linux, here are a few to get you started.
Don’t be afraid to as the community for help, this is an exciting new change and there are many experienced DBA’s willing to share their experience.

Resources

Community

Subscribe