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

 

Top 10 Linux Commands for SQL Server DBAs


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

Problem

Microsoft has released SQL Server on Linux and there are several good tips on getting SQL Server up and running on Linux here at MSSQLTips.com. I need to know how to handle some routine tasks in Linux that I already know how to do in Windows.

Solution

Many of the basic command line functions on Linux are not vastly different from their cmd counterparts. Basic directory navigation and listing of contents are pretty simple to figure out, many distros have aliases for common functions that map the familiar cmd commands to their bash counterparts. Let's look at the equivalents to some more advanced tasks you will want to accomplish in your daily work: editing configuration files, monitoring resources (CPU and Memory), locating important files, running commands "as administrator", archiving files, and installing additional tools and utilities.

man

Every Linux user should know how to access the user manual via the "man" command. When you need to know more about a Linux command you can read the manual or man page by typing man followed by the command you're curious about. The man page will give you a basic description of the command and a complete list of parameters and their meanings. There is also an "info" command which has a bit more verbose description of the command, but may not list all of the command-line parameters. Think of PowerShell's Get-Command minus the examples. Here is the first part of the man page for tar.

sample man page

cat

Short for conCATenate (not felis catus) this command lists the contents of a text file (well, a binary file too, but it's messy!). It is often used in a conjunction with "more" to paginate the output. The cat command is useful for examining log files or configuration files without risking an accidental change. For a small file you can simply type cat <filename> and the file will be displayed on your terminal. For a longer file see the next section for more pagination.

more

To scroll through a longer file you will want to use a paginator such as more. Simply type more <filename> and the file will be displayed a page at a time on your terminal. The spacebar will take you forward to the next page, "b" will take you back to the previous page, and "q" will exit the program. You can also pipe the output from a command into more to paginate it, for example to list the contents of a large directory try ls -l | more. There are more options for navigating and searching the input file in the man page.

vi

You need to be familiar with a text editor for viewing and updating configuration files and the like. I'm partial to vi and vim, they are handy because the default bash command-line navigation and editing keystrokes are adapted from the vi commands. There are entire books dedicated to vi and there are many, many vi reference sheets available for download, search Google or Bing for them. To get started here are the basics:

To edit a file type vi <filename>. If the file does not exist vi will create a new, empty file. When vi starts it is in view mode, you can use the arrow keys to move around or the vi keys "h", "j", "k", and "l" for up, down, left and right respectively. The "x" key will delete the character under the cursor, the "r" key will let you overwrite the character under the cursor, capital "R" enters overwrite mode, allowing you to replace multiple characters until you hit escape. To enter insert mode hit "i" and type your new content then escape to go back to view only mode. The "O" and "o" keys will insert a blank line above or below the cursor position respectively and enter insert mode (escape to exit). To delete the line the cursor is on type "dd". To exit vi type ":q" to save your work type ":w" to save and exit type ":x" or ":wq" to exit and discard changes type ":q!".

top

The top command provides much the same information as the Windows Task Manager. You can see the running processes' CPU and memory usage as well as the overall system load. Below is an example of top running processes on an essentially idle Ubuntu VM. You should be able to easily identify CPU usage, memory usage, and who is running the most expensive processes. The man page will explain the options and more arcane output, they can differ slightly by distro. Enter top on the command line to start and "q" to exit

sample top output

Daniel Farina has a detailed tip on using top to monitor CPU.

df

Windows Explorer shows how much of your disk volumes are used, on the Linux command line you can use the "df" command to display how much of each volume is used. By default df displays disk storage in units of 1k blocks, I prefer to use the "-h" argument that uses the most appropriate of kilobytes, megabytes, or gigabytes indicated by "K", "M", or "G" (actually all the way up to "Y") next to the number. Here is the output of df -h on my Ubuntu VM:

sample df output

find

Files get lost especially in an unfamiliar operating system. The find command allows you to search all or part of your server by file name, size, age, owner, and much much more. The man page is your friend, but here are a few simple use cases:

  • Find all the files with the .dbf extension find / -name "*.dbf"
  • Find all the files in the /data directory larger than 100 megabytes find /data -size +100M
  • From my current directory find all files changed less than 7 days ago find . -ctime -7

One of the cool features of the find command is the ability to execute a command on each of the results. While find / -name "*.dbf" will list the filenames find / -name "*.dbf" -exec ls -l {}\; will find the files and run ls -l with each filename passed as the argument.

sudo

How many times a day do you need to "Run as Administrator" on your Windows Server? The administrator (aka superuser) on Linux is "root", if you're lucky (unlucky) you get to administer your own server. If you are not the admin you can ask her for permission to use the sudo (SUperuser DO) command (immortalized in the XKCD comic #149 below). The sudo command gives you superuser rights for just one command and gives the system administrator very fine-grained control over what mere mortal users are allowed to execute with it. She may give you carte-blanche or only allow you to run a specific command with a specific set of parameters. Usually you will be required to enter your password to complete a command executed via sudo. For example, to install a new package on your server, try sudo yum install cowsay or sudo apt-get install cowsay.

sandwich comic courtesy of xkcd.com/149

tar

Often you will receive data files or software bundles together in a "tarball". The tar command (for Tape ARchive) can be used to gather, and optionally compress, many files into a single archive for distribution. To create a tarball type tar cf tarball.tar <list of files here>, to create a compressed tarball type tar czf tarball.tgz <list of files here>. Notice that a compressed tarball has the extension .tgz (it uses gzip behind the scenes). Extracting a tarball uses the "x" argument rather than "c". To extract a plain tarball type tar xf tarball.tar, for a compressed tarball you would extract it with tar xzf tarball.tgz. The tarball will be extracted to your current directory in either case so place the it at the root of where you want the files to live. The man page describes many more options, but these two are, by far, the most common use cases you will run into.

yum or apt

Depending on your distro you will use yum (Red Hat, Fedora, CentOS) or apt (Ubuntu, Debian) to download and install software from vendor repositories to your server. These are called package managers and can be used not only to install software, but to upgrade or remove it as well. A package is like an .msi file in Windows, but is not self-executable and will have an extension of .rpm (yum) or .deb (apt). The package managers will not only install the software, but will list (and install, if you like) any dependencies it finds. The simple commands on the SQL Server Linux installer tutorials are a good start, but when you need to install more tools, yum and apt, are the way to go. There is an install example above in the section on sudo, if you wanted to make sure all your packages were up to date you would use sudo yum update followed by sudo yum upgrade or sudo apt-get update followed by sudo apt-get upgrade.

Summary

These are just a few of the commands you will find helpful on the Linux command line. If you find some commands or tasks you want to learn how to do leave me a comment so I can follow up with the next 10 Linux Commands for SQL Server DBAs.

Next Steps
  • Make friends with your sysadmin
  • Find a good book on bash (the Linux command-line)
  • Stay tuned to MSSQLTips.com for more useful information SQL Server on Linux tips


Last Update:






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     



Monday, April 17, 2017 - 1:01:52 PM - jeff_yao Back To Top

Good article. 

With SQL v.Next on Linux, we DBAs indeed need to pay more attenton to and start to learn Linux related stuff.


Monday, April 17, 2017 - 10:32:10 AM - Reza Back To Top

 

 

Thanks for sharing. Vi/Vim is very powerful editor; however, it will be very complicated to use this editor once you move from Windows to Linux. I recommend using nano editor instead. Moreover, the following commands are mandatory for DBA in Linux environment also...

chmod – change permissions

chown – change ownership

mkdir 

rm 

 


Learn more about SQL Server tools