Chapter 3
Linux for the SQL Server Professional
In This Chapter
The primary purpose of this chapter is to help you get started with Linux, but it also covers some more advanced topics and system monitoring tools. A traditional SQL Server book would not cover how to use an operating system or its administration. This book is an exception, however, because SQL Server users have been working with the Windows platform since the beginning, and most are not familiar with Linux. The information in this chapter may be extremely useful to help with the transition from the Windows platform to Linux.
This chapter is not intended to provide much information to the Linux administrator, but I hope it helps SQL Server professionals get started with Linux. As he or she would when administering the Windows platform, the SQL Server administrator should work with related professionals such as system or storage administrators to help achieve optimum results, especially when configuring and administering production environments.
First of all, Linux is an operating system based on Unix. Unix was originally conceived and implemented in 1969 and later rewritten in the C language, with its first version publicly released in 1973. There have been a large number of Unix implementations throughout the years, including some successful commercial versions such as System V, Sun Solaris, IBM AIX, and Hewlett Packard HP-UX.
In 1991, Linus Torvalds, looking to create a free operating system as a hobby, wrote and published Linux. Currently Linux reportedly includes more than 18 million lines of source code. As with Unix, Linux has many distributions. This huge number of implementations, with incompatibilities and lack of standardization, has been considered a weakness of this operating system. Linux is open source, while Unix is mostly proprietary software (only a few versions of Unix have been free or open sourced, such as BSD).
NOTE
Writing this chapter brought back some memories. I used to teach Unix system administration in a university back in the 1990s, and this is my first time writing a tutorial since then. I first heard about Linux in a personal computing magazine back in the early days and immediately went to download it onto a few 3½-inch floppy disks and installed it. Not many people knew about Linux or even had access to the Internet in those days. I also worked with all the most popular Unix implementations including AIX, Sun Solaris, and HP-UX.
Getting Started
If you made it this far, you are probably at least familiar with the most basic Linux commands such as ls, cd, more, md, and ps. You also may know that Linux commands are case-sensitive and usually in lowercase. For example, typing ls works as expected, but typing LS or Ls does not. Let’s review some basic commands in this section and then move to more advanced ones later in the chapter.
When you open a new session, you are always running a shell (more on that later—for now think about opening an MS-DOS command prompt session in Windows). Unix traditionally provides different shells such as Bourne, C, or Korn Shell. In Red Hat Enterprise Linux, the default is Bash (for Bourne-again shell), one of the most popular shells in Linux distributions. You are also defined a default startup directory, which is your home directory.
NOTE
This book uses the Courier font to show the commands to execute, and in some cases, the output follows. The command you have to type will usually be the first line and starts with the shell prompt, such as $ or #.
Use the man (manual) command, mentioned in Chapter 1, to see the Linux documentation or online reference manuals. For example, to look for documentation of the ps command, you can use
You will see something similar to Figure 3-1.
Figure 3-1 The ps man page
To exit the man page, press q for quit. To learn more about the man command, you would use man itself—for example, type the following:
Use man –k to search for the man page descriptions for keywords and display any matches. For example, to find information about shells, you could use this:
Also, if you are accustomed to the style of the SQL Server documentation (where at the end of a topic, similar commands are mentioned), if you don’t find what you are looking for or if you want more information, look for a “SEE ALSO” section with additional commands or man pages:
Another important concept is the path, defined by the $PATH variable. Same as with Windows, $PATH enables you to define the directories containing executable code so you can run a program without specifying its file location. In my default installation, I have the following:
I can include a new directory, if needed. For example, trying to execute the SQL Server sqlcmd utility in a default installation will show the following:
Try the following command to add the /opt/mssql-tools/bin directory to the current $PATH variable. Notice the separator is the colon (:) character:
Then you can verify the updated value of the variable by running this command:
You will now be able to execute sqlcmd without having to worry about its location in the file system. Now, this path change is just for the current session; if you disconnect and connect again, the changes will be lost. To make this change permanent, you need to update your profile file, which for the Bash shell is named .bash_profile. Notice the following lines inside the file:
Open the file and edit the following line as shown:
NOTE
There are several ways to edit files in Linux. The traditional Unix way is to use the vi editor, which may be complicated to use for new Linux users. There is also the Vim (Vi Improved) editor. Covering vi is beyond the scope of this chapter, but you should be able to find a tutorial online to learn how to use any of these editors.
Files and Directories
I will start this section by showing you how to manage and navigate directories. First, to display your current directory, use the pwd (print working directory) command:
NOTE
Some Unix commands may have awkward names, such as pwd, which may be an abbreviation of a word or words describing what they do. I will be using parentheses to show what the command stands for, if needed. In some other cases, such as with find or sort, its description will be obvious.
You can navigate to any directory you have access to by using the cd (change directory) command. For example, to move to the SQL Server databases file directory, you could use the following:
If you tried the MS-DOS chdir command, you may have noticed that it does not work here. Using cd without a directory moves you back to your home directory, which is defined by the $HOME variable:
You can also use the tilde (~) symbol as a shortcut for your home directory, which may be useful when you want to run a command or script while you are in some other directory without having to specify the full file path. For example, the following commands create a backup directory in your home directory and then try to list the contents of the same directory even if you are working in the /tmp directory:
As with Windows, you can also use relative paths. For example, use the following command to start in your current directory and change to apps/dir1:
You can use a dot (.) to refer to your current directory and two dots (..) to refer to the parent directory of the current directory. For example, use the following command to move two levels up the directory hierarchy structure:
Assuming you have the required permissions, you can also create and remove directories using the mkdir (make directories) and rmdir (remove empty directories) commands. Interesting to note is that, unlike the cd command, the Windows md and rd commands will not work.
No surprises here if you are familiar with these commands on the old MS-DOS or Windows command prompt. The first command creates the directory mydir; the second command changes the current directory to the directory just created and then changes to the previous directory by going one level back. Finally, the directory is removed.
The rmdir command can remove only empty directories, however. Trying the following will return an error message:
In the next section, I will show you how to remove nonempty directories using the rm command. You could also manually remove each file and directory inside this directory, which, although time consuming, in some cases may be beneficial—for example, to avoid deleting something you may need.
To list the contents of a directory, use the ls (list) command. As with many Unix commands, ls has a large number of options, so make sure you use the man documentation if you need more details. Most typical use is as ls or ls –l. For example, the following ls -l command on the /var/opt/mssql/data directory will list the current SQL Server database files:
You can see a lot of interesting information about the listed file, including file type and permissions, shown as -rw-r----- (more details on permissions later in this chapter), the number of links to the file, the user owner, the group owner, the size in bytes, the last date the file was modified, and the filename.
Displaying the size in bytes for very large files can be difficult to read, so you can also try both the –l and -h (human readable) options. They will show the previous size bytes value 15400960 as 15M. Size units are reported as K, M, G, T, P, E, Z, and Y for kilobytes, megabytes, gigabytes, terabytes, petabytes, exabytes, zettabytes, and yottabytes, respectively (though I don’t think anyone is using some of those extremely large file sizes just yet).
NOTE
Although the amount of data generated worldwide is increasing rapidly, units such as exabytes, zettabytes, and yottabytes are still huge. In comparison, the maximum database size allowed in SQL Server is 524,272 terabytes, which is very large even for current standards.
Some other useful ls options are -a (or --all) and -A (or --almost-all), which are used to include hidden files on the list. The only difference is that the latter does not include the named . and .. entries, which represent the current and parent directories, respectively. A Unix file starting with a dot (.) is considered a hidden file.
Files
Almost everything in Unix is a file, and, unlike Windows, Linux does not use a file extension in the filename. (The file command can be used to determine what kind of file it is, although this will be rarely needed.) You can perform basic file manipulation by using the cp (copy), mv (move), and rm (remove) commands.
Use cp to copy files and directories. The following makes a copy of file1 and names it file2:
You can also use -r (or -R or --recursive) to copy directories recursively. For example, the following statements create a couple of files inside the dir1 directory and copy the dir1 directory and its contents to the backup directory:
Use the mv command to move files and directories. Or use it to rename files and directories. The next commands create a new dir2 directory and move file1 from directory dir1 to dir2:
The following command renames file2 to file3:
The following example is similar to the previous cp example, but this time it moves the entire directory dir1 and its contents to the backup directory. Note that no recursive option is needed:
Use the rm command to remove files and directories. The following example removes the file named file1:
I showed you the rmdir command to remove directories. But if the directory is not empty and you use this command, you will get the “directory not empty” error message. Use rm with -r (or -R or --recursive) to remove directories and their contents recursively:
Using –i to prompt before removal could be useful in some cases to protect against some mistakes. The -f option, on the other hand, forces the removal and does not prompt for user input. It is common to use both -r and -f to delete a directory and its entire contents, but you need to be extremely cautious about their use:
The following example deletes all the files in the current directory, while being prompted on each one:
Use the touch command to change file timestamps or update the file access and modification times to the current time. It is also commonly used to create empty files:
The find command is very useful; its purpose is to search for files in a directory hierarchy. find also has a large number of parameters and choices, so make sure to check the documentation if you have some specific needs. For example, you may be wondering where the sqlcmd utility is located. Here’s how to find it:
You’ll need the required permissions for the folders you specify, or you may get the following message for one or more directories:
Optionally, you can use the -iname option for a case-insensitive search. To find the files modified in the last 50 days, use the -mtime option, as in the following example:
Similar to -mtime, use the -atime command to report the files that have been accessed on a specified number of days:
Use the -type option to find a specific type of file, which can be b (block), c (character), d (directory), p (named pipe), f (regular file), l (symbolic link), and s (socket). Use the -empty option to find empty files or directories:
You can also find files owned by a specific user:
The find command is extremely flexible and can also be used to perform an operation or execute a command on the files found. For example, the following command changes the permissions on all the files encountered (chmod is explained later in the chapter in the “Permissions” section):
The next example removes the files found:
The next command searches for a string within the files found:
In these three cases, find is using the –exec option, which enables you to execute a command for each filename found. The string {} is replaced by the current filename being processed. Use the optional -print to print the filename found; it is useful to have a quick indication of the work performed. Executed commands must end with \; (a backslash and semicolon).
Finally, find is also commonly used to find information and send it to another command for additional processing. More on than later in this section, where I cover piping and redirection.
You can use a few Linux commands to show information from a file, such as cat (concatenate), more, less, tail, or head. The cat command is the simplest to use to display small files:
Both the more and less commands enable you to read a file or some other input with additional choices. For example, you can navigate one page at a time allowing forward and backward movement or search for text:
Once you open a file, you can press the spacebar or f to move forward, press b to go backward one page, or press q to quit.
Use the tail command to output the last part of a file, which can be used to inspect the latest data on a changing file:
Use the head command to output the first part of a file:
Use the diff (difference) command to see the differences between two files. This will report the differences between both files, or nothing will be returned if the files are the same.
If nothing is returned (and that seems confusing), you can use the –s option to get a confirmation when the files are the same:
Use the sort command to sort lines of text files. For example, assuming file1 has a list of items, running the following command will return the list sorted:
This will sort to the standard output, but if you want to keep the sorted list, you may save it to a file, like in the following example:
Finally, you can use the file command to determine the file type. Here’s an example:
The Unix File System
Another new experience for first-time Linux users coming from the Windows world is the Unix file system. First of all, Unix uses no drive letters, such as C:, D:, or E:. If you are familiar with Windows mount points, introduced with Windows 2000, you may have an idea of how a Unix file system looks. A Unix file system is defined as one rooted tree hierarchy of directories. The root of the entire tree is denoted as root or / and every additional directory starts from there. New volumes can be mounted as a directory in this tree structure. Traditionally a Unix file system includes the following directories:
/bin Directory for binaries; may be a link to /usr/bin
/dev Directory for devices
/etc (etcetera) Directory for system configuration files and system databases
/home User home directories
/lib Directory for libraries
/mnt (mount) Empty directory used as a temporary mount point
/opt Directory for locally installed software
/proc Directory for processes
/sbin Directory for system binaries
/root Root user home directory
/sys Directory for system hardware and operating system information
/tmp Directory for temporary files
/usr User directory, traditionally hosted user home directories; also hosts additional binaries and libraries
/var Directory for files that may change frequently such as system log files, mail spools, or print jobs
NOTE
As you may remember from Chapter 1, SQL Server software was installed in the /opt directory, while the databases were placed in /var/opt.
SQL Server on Linux works on XFS or EXT4 file systems; other file systems, such as BTRFS, are not currently supported. To print your file system type, use the df –T or cat /etc/fstab command.
Use the mount command to mount a file system, for example, when you have a new disk partition. Use umount to unmount a file system. Running mount without any option will list all the current mount points.
You can also use the lsblk command to list the block devices or disks available in the system; this output is easier to read than the output using mount. Here is an example on my system:
The most important columns are the Name, Size, and Type of the device and their mount point.
Use the lscpu command to display information about the Linux system CPU architecture. Here is some partial output on my system from a Windows Azure virtual machine:
Additional Commands
The ps (process status) command is one of the most useful Unix commands and has a large variety of options. Use it to see the current processes running in the system. Let’s start with the most basic ps command, which will return only the processes running in the current session:
The columns returned are PID (process ID), the terminal associated with the process, the cumulated CPU time, and the executable name. This example shows only the current shell and the ps process itself. You will usually need to specify some parameters to see system-wide information. Here’s a common one:
Use option -e to select all the processes and -f to add columns. These additional columns are UID, PPID, C, and STIME, which are the user ID, parent process ID, CPU usage, and time when the process started, respectively. In addition, -f includes the command arguments, if any. Use options -A and –a to select all the processes in the system. Keep in mind that ps provides a snapshot of the system at the time the command is executed. For real-time information, you could also use the top and htop commands, which I cover in the “System Monitoring” section later in this chapter.
Several other options are useful as well. Let’s try a few.
Use -u to display processes by a specific user:
Use -C to select the processes for a specific executable file:
It is also popular to use the BSD syntax to show every process in the system, as shown next. Notice that no dash (–) is required in this case:
The ps command offers incredible flexibility. For example, you can specify which columns to list using the -o option:
This example will list process ID, user ID, CPU usage, memory usage, and command name. For the entire list of possible columns, see the man documentation.
You can sort by one or more columns, in ascending or descending order, where + means increasing, which is the default, and - means decreasing. As with the -o option, you can see the entire list of available columns in the documentation using this command:
You can limit the previous output to a specific number of processes by sending it to the head command, which outputs the first part of it. In the following example, we use the head -n option to specify only ten lines:
You can use the watch command to run the same ps command periodically and display its current output. In this case, watch uses the -n option interval in seconds:
Press ctrl-c to stop the previous command.
Finally, use the –forest (or f, not to be confused with -f) option to display the process tree:
This output shows that the sqlservr process PID 857 spawned a second copy of the process with PID 939. You can also correlate using the PPID or parent process ID: PID 939 has PPID 857.
Use the grep (global regular expression parser) command to return lines matching a pattern or basically to search text and regular expressions in the provided input. Let’s create a text file for our next examples. The next command saves the documentation of the grep command itself into a file named file1:
Let’s try our first search looking for the word “variables”:
In my case, it returned seven lines.
By default, a search is case-sensitive. A commonly used option to change this is –i (or --ignore-case) to ignore case distinctions:
This example returns the previous output, plus one more line with the word in all uppercase.
Use the -v option (or --invert-match) to return all the lines that do not contain the indicated text:
Use single quotes to include spaces:
Add -n to include the line number in addition to the line text:
Use the –E option (or the egrep command) to search for multiple patterns at a time:
You can also use regular expressions to search for text. For example, the following command will search for either “egrep” or “fgrep” on the just created file1:
This expression means search for either “e” or “f” followed by “grep.”
You can use numbers, too. The following command will search for four digits inside file1:
If you try a search on binary files, such as in the next example, by default, you’ll get the following response only:
One choice is to use the -a option to process a binary file as if it were text. The following will perform the desired search:
Finally, grep also includes the behavior of both egrep (extended grep) and fgrep (fixed-string grep), which are now deprecated but still available for backward-compatibility. (For more details about regular expressions, see the grep documentation.)
Use the kill command to terminate a process. You need to specify the process ID as in the following example:
You can also specify kill -9 (SIGKILL signal) as a last resort when terminating a process that cannot be terminated otherwise:
Basically, kill is used to signal processes. By default, kill sends the SIGTERM signal, which tells the process to exit gracefully. If the process is coded properly, it will call its own exit routines, shut down gracefully, and clean up its internal resources. Using kill -9 will send the SIGKILL signal, which will kill the process without calling its exit routines.
NOTE
Do not use the kill command to stop a service, including SQL Server. To see the proper way to stop, start, or restart a service, see the section “Services Management” later in this chapter.
Use the who and whoami commands to display information about the current logged-in user:
Use the date command to print the system date. Or use it to set the system date:
You can use the wc (word count) utility to count lines, words, and bytes for each specified file. For example, on the previously created file1, I get the following output:
Another very helpful command is history. Use it to display the history of commands recently executed, which can be useful when you want to type the same command again. The history command provides an easy way to move through the history of commands, optionally edit them, and execute them again—for example, using keyboard arrows to move up and down. Here’s a partial output in my system:
A specific command can also be executed using the ! symbol and the history line number, as in the following case:
Sometimes you may need to find the version of Red Hat Enterprise Linux. You can see it by opening the /etc/redhat-release file:
Finally, you can use the clear command to clear the terminal screen and the passwd command to update a user password.
Building a Command
It is very common in Unix environments to use commands built from one or more commands using piping or redirection. This is typically the result of one command producing some output to be processed by another command. Output is usually redirected from one command to another using the pipe symbol (|). Output can also be redirected to other devices or files using the > and >> symbols.
Let’s start by looking at some examples. The following command saves the output of ps into a file named ps_output:
Using > always creates a new file, overwriting an existing file if needed. You can use >> to add the output to an existing file. Running the following command will add the new output to the existing file:
Using 2> redirects the standard error output to a file. For example, you may have standard error output mixed with normal output, which may be difficult to read. A common case is using find and getting the permission denied error on multiple directories:
You could try something like the following command, which returns a clean output with only the information you need:
You may choose to inspect the created file later. Or, better yet, you may want to discard the errors if you do not need to see them by sending the standard error output to the null device, a concept that also exists in Windows:
Using < sends the content of a file as the input to a command:
You can send the content of one file as the input to a command and send its output to yet another file:
It is very common to use the pipe symbol to build complex commands by sending the output of one command as the input to another. For example, it is typical to start with a ps -ef command. This may return more than 100 lines, one for each process. I list here just a few examples:
You could next send the output as the input to another command—for example, grep, followed by a string to search for. Here is the desired output:
Permissions
Use root or superuser permissions only when performing system administration activities and as minimally as possible. As expected, making mistakes as the root user can create catastrophic failures, especially on a production system. The sudo command enables a permitted user to execute a command as either the superuser or another user, according to the defined security policy. Multiple examples using sudo to execute a command as superuser were provided in Chapter 1, where I covered the installation and configuration of SQL Server. Here’s one example used in that chapter:
You could also switch to superuser using the sudo su command, as shown next:
The root prompt is #. When you’re finished executing the administrative tasks, execute exit or press ctrl-d.
Now let’s cover file permissions. We’ll start with the default permissions for creating a file. Run the following statements:
The default permissions in Linux are defined by a permissions mask called umask, which can also be changed to anything you need. Permissions are r (read), w (write), and x (execute), and they correspond to the owner, group, and other (meaning anyone else who is not the owner or is not in the group).
NOTE
The first character is the file type and usually is a dash (-) for a regular file or d for a directory. As introduced earlier with the find command, there are also five special file types that use b for block file, c for character, p for named pipe, l for a symbolic link, and s for a socket.
Use the chmod (change mode) command to change the mode bits of a file, where u is user, g is group, and o is other. Use the symbol + to grant the permission and - to revoke it.
In this example, o+w grants the write permission to other and g-w revokes the write permission to the group.
You can also change multiple permissions at a time:
This example removes execute permission from the user, assigns write permissions to the group, and removes write permissions from other.
An alternative method to work with permissions is to use the octal permission representation, as shown in the following example:
You can define any combination of permissions with just three digits, but using this method may be more complicated for the beginner. Basically, this octal representation uses 4 for read, 2 for write, and 1 for execute, and you can add these values to assign more than one permission at a time. For example, to give read permission you’d use the value 4, to give write you’d use 2, to give both read and write you’d add both values to get 6, to give read and execute you’d add both values to get 5, and to give all three permissions you’d add all three values to get 7. It is also possible to remove all the permissions. You can see a chart with all the possible permissions in Figure 3-2.
Figure 3-2 Octal notation
The same permissions apply to directories in which read means that the user can list the contents of the directory, write means that the user can create or delete files or other directories, and execute means that the user can navigate through the directory.
In addition to changing the permissions of a file or directory, you can use the following commands to change its owner and group. Use chown (change owner) to change the file or directory owner and chgrp (change group) to change its group. For example, the following commands are executed as root:
The next example uses chgrp to change the group to mssql:
Bash Shell
As mentioned in Chapter 1, a shell is a user interface used to access operating system services, and in the case of Unix, most of you will use a shell as a command-line interface. It is called a shell because it is a layer around the operating system kernel. Most popular Unix shells used throughout the years include the Bourne, C, and Korn shells. Most Linux distributions use the Bourne-Again shell, or Bash, as is the case with Red Hat Enterprise Linux, Ubuntu, and SUSE Linux Enterprise Server.
Unix and Linux sometimes have a GUI similar to that of Windows. However, there has never been a standard, and those interfaces could differ greatly from one Unix or Linux distribution to another. Although learning such GUIs could be useful, using command-line commands and scripts is essential for working with Linux.
Let’s discuss some basics about working with a Linux shell. The SHELL variable will return the full pathname to the current shell:
Similar to Windows, you can use wildcards in Linux. It is possible to use the asterisk symbol (*) to represent zero or more characters and the question mark symbol (?) to represent a single character. You can also use brackets ([ ]) to represent a range of characters—for example [0–9] or [a–e] to include any number between “0” and “9” or any letter between “a” and “e,” respectively. As an example, assuming that you create the following four files, the ls command will list anything starting with “b” or “c” plus “123”:
NOTE
As you noticed from the first line, you can submit multiple commands in the same line if they are separated by a semicolon.
Although most Unix commands are provided as executable files on the file system, some could be provided by the shell as well. In some cases, even the same command could be provided by both. For example, the pwd command could be provided by the shell or by the operating system as an executable file. Usually the shell version supersedes the latter. Consider the following:
This shows the shell version, which complains that only the choices –L and –P are allowed.
Now try the following to execute the pwd version located on /usr/bin. Notice that, this time, the –version option is allowed:
It is also very common to change the Linux prompt instead of just simply $ to include some information, like the hostname you are connected to. This is usually defined on the .bash_profile configuration file or in some other script or variable defined or called inside it. For example, my current prompt is defined as follows:
where \u is the username, \h is the hostname, and \W is the current working directory. In my case, the following would be displayed next if, for example, I am currently on the directory dir1 and my hostname is sqlonlinux:
You can update your prompt by assigning a new value to the PS1 variable, if needed.
NOTE
.bash_profile is the profile filename used by the Bash shell. Other shells will use different filenames.
Finally, scripting is a very common method to perform system administration tasks. Scripting would take an entire chapter or book on its own, so I will not cover it here. But let’s create a “hello, world” script in case you want to get started. Create a new file called hello, with the following two lines:
Try to execute the script by typing its name. You may get a couple of errors before you can make it work:
First, the current directory is not included in the current $PATH environment variable, so the second time I used ./hello to specify the script location explicitly. The second issue was that by default the file did not have execute permissions, so I granted those, too. Finally, I was able to execute the script.
NOTE
The tradition to use a “hello, world” program as the very first program people write when they are new to a language was influenced by an example in the seminal book The C Programming Language by Brian Kernighan and Dennis Ritchie (Prentice Hall, 1978).
Services Management
As introduced in Chapter 1, systemd is an init, or initialization system, used in Linux distributions to manage system processes that will run as daemons until the system is shut down. systemd has several utilities, such as systemctl, which you can use to start, restart, or stop a service or to show runtime status information.
Chapter 1 also covered the basics of systemctl, including how to start, stop, and restart a service using the following syntax:
More specifically, the chapter covered the following commands:
Stop SQL Server sudo systemctl stop mssql-server
Start SQL Server sudo systemctl start mssql-server
Restart SQL Server sudo systemctl restart mssql-server
This section covers some additional systemctl choices. A very useful option is status, which you can use as shown in the following example:
You will get an output similar to the following, plus the latest ten lines of the SQL Server error log (not shown here). Technically, it returns the last ten lines from the journal, counting from the most recent ones.
Notice that the status option provides a lot of valuable information, such as whether the service is loaded or not, the absolute path to the service file (in this case, /usr/lib/systemd/system/mssql-server.service), the process ID of the service process, whether the service is running, and how long it has been running.
Use the is-active option to return whether the service is active or not:
Use the try-restart option to try to restart a service, but only if it is running. In any of both cases, it will return silently.
You can also use the service file extension as in the following case:
To display the status of all the services in the system, use the list-units option:
My system returns 48 service units. Here’s a partial list:
The output shows the name of the unit, description, and whether it is loaded and active.
Use the reload option to reload a service configuration without interrupting the service. It does not apply to all the services including SQL Server.
Use the enable option to enable a service so it can be started automatically at boot time:
You can also do the opposite and disable a service so it does not start automatically at boot time:
A disabled service can be started manually using the start option.
You can also use systemd on a remote computer using the following syntax:
Here’s an example:
This would require that the sshd service be running on the remote computer.
Finally, in addition to many other features, you can use systemctl to shut down the system, shut down and reboot the system, or shut down and power off the system. The required commands, respectively, are shown next:
This is similar to using the shutdown, halt, poweroff and reboot commands which, depending on the options provided, can also be used to shut down, shut down and reboot, or shut down and power off the system.
Software Management
Chapter 1 gave you a quick introduction to package managers to provide basic information on how to install SQL Server software. A package managing system is a collection of utilities used to install, upgrade, configure, and remove packages or distributions of software or to query information about available packages. There are several package management systems, and the chapter briefly covered some package management utilities such as yum, apt, and zypper, which we used with Red Hat Enterprise Linux, Ubuntu, and SUSE Linux Enterprise Server, respectively. This section covers yum in more detail. You can easily find similar choices in the documentation for the apt or zypper utilities.
The RPM Package Manager (RPM) is a packaging system used by several Linux distributions, including Red Hat Enterprise Linux. The yum package manager is written in Python and is designed to work with packages using the RPM format. Yum requires superuser privileges to install, update, or remove packages.
In Chapter 1, we used yum to install the SQL Server package mssql-server by running the following command:
Let’s continue with additional topics, starting with updating packages. During the SQL Server beta deployment process, it was common to upgrade regularly to new CTP or RC packages. You could check which installed packages had updates available by running the command
NOTE
CTPs (Community Technology Previews) and RCs (Release Candidates) are versions of SQL Server used during the beta program before the final software was released. The final release, which is the only version that can be used in a production environment, is the RTM (release to manufacturing) version.
Depending on your system, you may see a long list of entries. For example, this is what I get from a fresh Microsoft Azure virtual machine, which seems to have an old version of SQL Server:
The second row, for example, shows the package mssql-server, architecture x86_64, and the available version in this case is 14.0.900.75-1. It also shows the repository in which the package is located: packages-microsoft-com-mssql-server. The current version provided by my new virtual machine is SQL Server vNext CTP 2.0 14.0.500.272, so, obviously, the recommendation by yum is a newer version.
Let’s install the latest recommended version. Stop your SQL Server instance and run the following command to update the mssql-server package:
Part of the interesting output follows (formatted to fit this book):
After this command was completed, my software version was successfully updated to SQL Server 2017 RC2 14.0.900.75. If you update your SQL Server version, be sure to update the remaining SQL Server–related packages as well or run the check-update command again to validate that they are up to date:
If you already have the latest version of the software, you will see something similar to this:
You could also update packages that have security updates available—though, at the time of writing, no security updates have been released:
Use the yum info command to display information about a specific package. Here’s an example:
The package version matches the version build returned by SQL Server—for example, using the @@VERSION function.
Finally, to remove a package, you can run the following:
Use the list all option to list information on all installed and available packages. Commands that return a large amount of data usually allow an expression similar to the following example to limit the data returned:
Optionally, you can also list all the packages in all yum repositories available to install:
Finally, use the search command when you have some information about the package but do not know the package name:
NOTE
For more details and options, consult the yum documentation, which you can access via the man yum command.
Disk Management
Let’s review a few commands that can provide information about the file system disk space usage. The df (disk free) command is a very useful utility to display file system disk space usage. Here is the output I get on my default Microsoft Azure Red Hat Enterprise Linux virtual machine:
The listed information is self-explanatory and includes the file system name, the total size in 1KB blocks, the used space and available disk space, the used percentage, and the file system mount location.
Use -h to print sizes in human-readable format—for example, using K, M, or G for kilobytes, megabytes, and gigabytes, respectively.
Add totals using the --total option:
Finally, use -T to display the file system type:
Use the du (disk usage) command to display disk usage per file. Here is an example using the -h human readable output on /var/opt/mssql, summarized for space:
System Monitoring
System monitoring is also one of those topics that would require an entire chapter of its own. In this section, I’ll show you some basic commands to get you started. Earlier in the chapter, you learned about the ps command, which displays the current process information in the system. You also learned about top and htop.
The top command shows information about system processes in real time. A sample output is shown in Figure 3-2. To exit the top command, press q.
Here’s a quick summary of the information provided by the top command:
The first line shows information about the system uptime, or how long the system has been running; the current number of users; and the system load average over the last 1, 5, and 15 minutes. This line contains the same information displayed with the uptime command.
The second line shows, by default, information about system tasks, including running, sleeping, and stopped tasks or tasks in a zombie state.
The third line shows information about CPU usage.
Figure 3-3 The top command
The next section consists of two lines showing memory information.
The last section, which uses most of the screen real estate, lists the current processes running in the system.
By default, top lists the following columns, all of which are also available with the ps command:
PID Process ID
USER Username
PR Scheduling priority of the task
NI The nice value of the task; this Unix concept is related to the priority of the task and defines which process can have more or less CPU time than other processes
VIRT Virtual memory size
RES Resident memory size
SHR Shared memory size
S Process status, which could be R (running), S (sleeping), T (stopped by job control signal), t (stopped by the debugger during trace), Z (zombie), or D (uninterruptible sleep)
%CPU CPU usage
%MEM Memory usage
TIME CPU time
COMMAND Command or program name
NOTE
For more details about the top command, see the man documentation.
Optionally you can use htop, a variant of top that is becoming increasingly popular. The htop command is not included with Red Hat Enterprise Linux and must be installed separately.
NOTE
For more details about htop see http://hisham.hm/htop.
Use the free command to display the amount of free and used memory in the system. Here’s a sample of output I got from my test system:
The values returned include total installed memory, used memory, free memory, shared memory, memory used by kernel buffers, memory used by the page cache, and memory available for starting new applications without swapping.
Finally, you should be aware of the Unix cron facility, which is used to execute scheduled commands, and the crontab command, which is used to maintain crontab files for individual users. You can think of cron as something similar to Windows Task Scheduler. Although the availability of the SQL Server Agent on the Linux platform makes it less likely that you’ll need to use the Linux cron, you still may require it for some maintenance scenarios.
Summary
The chapter covered all the basic Linux commands required to get started, including managing files and directories and their permissions, along with a few more advanced topics including system monitoring. The chapter is not intended for the system administrator, but is intended to help the SQL Server administrator work with Linux. As he or she would with the Windows platform, the SQL Server administrator should work with related professionals such as system or storage administrators to help achieve optimum results, especially for production implementations.
Software management knowledge will be an essential skill for the SQL Server administrator because it will be required to install and upgrade a SQL Server instance. Service management will also be important to understand because it will provide the same functionality provided in the Windows world, including using the SQL Server Configuration Manager utility. The chapter closes with a variety of Linux commands related to file and disk management and system monitoring.