Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

7 Things Every SQL Server DBA Should Know About Linux


By:   |   Read Comments (1)   |   Related Tips: More > SQL Server on Linux

Attend a SQL Server Conference for FREE >> click to learn more


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. Its 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 theyll 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 youll 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 systems 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 youre 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.

directory

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 files 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).

script file

Only the root account, using chown (remember, sudo makes you root for just one command at a time), can change a files 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.

script file

Life without a GUI

Navigating your server without a GUI may seem clunky at first. If youre 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. Im 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.

alias

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 dont 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


Last Update:


signup button

next tip button



About the author
MSSQLTips author John Grover John Grover is a 25-year IT veteran with experience in SQL Server, Oracle, clustering and virtualization.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, July 19, 2017 - 10:12:07 AM - Chuck Back To Top

Nice article. It covers all the basics for a SQL DBA who's adding Linux to his/her repertoire. 

I would mention that its great if you do have a linux file manager on your desktop and it interacts very nicely with the filesystems on serves. Just use sftp (ssh's close cousin) to navigate with a url something like this - sftp://servername/directoryName. The same file manager can navigate windows shares by changing the url to smb://servername/sharename.

Another open source tool that SQL DBAs may find useful in learning bash and the Linux command line is Cygwin (www.cygwin.com). Its a linux emulator for Windows thats about 95% the same as bash on linux with a few differences so it plays nicely with Windows. For example file names in cygwin are not case sensitive by default but there as options to make it so. You also need to keep in mind the difference between Linux and Windows end of line characters. In Windows its CR/LF but in Linux it's just CR.


Learn more about SQL Server tools