Linux 101 for SQL Server DBAs, Part II

Linux has become a clear area of required learning for SQL Server DBAs in as of SQL Server 2016.  What?  You didn’t read that right, you say?  Yes, while a Linux edition was introduced in SQL Server 2016, it was pushed to the forefront with the release of 2017, along with Python in a push towards stronger DevOps initiatives, (one OS to rule them all!) and Python to assist with even stronger analytics trends.

As most database platforms already run on Linux, the SQL Server community has a bit of catch up to do, so as many advanced DBA linux posts and classes that are out there, let’s start with some basics.  There’s already a Part I to this post, but we’ll continue to go on, repeating a little of what will be needed as part of this post and then going deeper into some more basics.

Privileges-  The Least Required

I love the Linux SQL Server 2017 container, but there is one thing I’m really not thrilled with.  Everything is installed under root.

root@4b714989afa3:/opt# ls -ltr
drwxrwxr-x 4 root root 4096 Nov 18 04:55 mssql
drwxrwxr-x 4 root root 4096 Nov 18 04:56 mssql-tools
drwxr-xr-x 3 root root 4096 Nov 18 04:56 microsoft

Every Oracle DBA reading this is saying, “WTF?”  There should be a SQL Server user, most likely MSSQL that should own this, along with supporting group ownership, such as DBA and SQLINSTALL.  This would follow the appropriate best practice, (I know, I know, you hate that term…) and anything that required super user privileges, (think ADMINISTRATOR on Windows) would then require SUDO, (switch user and do) to perform.

Processes

the ps command is going to be your friend.

Searching for SQL Server.

ps -ef

Now this will show you all the processes that are running and could be a bit more than you might want to see upon initial view.  Maybe you just want to see what’s running for SQL Server:

root@4b714989afa3:/# ps -ef | grep sql
root 1 0 0 Jan21 ? 00:00:00 /bin/sh -c /opt/mssql/bin/sqlservr
root 5 1 0 Jan21 ? 00:00:00 /opt/mssql/bin/sqlservr
root 7 5 53 Jan21 ? 17:02:26 /opt/mssql/bin/sqlservr

root@4b714989afa3:/# ps aux | grep sql
root 1 0.0 0.0 4508 796 ? Ss Jan21 0:00 /bin/sh -c /opt/mssql/bin/sqlservr
root 5 0.0 0.3 80208 13300 ? Sl Jan21 0:00 /opt/mssql/bin/sqlservr
root 7 50.1 17.2 3028796 692196 ? Sl Jan21 1024:57 /opt/mssq
/bin/sqlservr
root 6236 0.0 0.0 11284 920 pts/0 S+ 23:46 0:00 grep --color=auto sql

This is much cleaner, no matter  and you won’t have to view everything running on the server, which once the server is running in a standard company environment, might be quite littered with processes.  Note the aux option now shows you the child processes, along with the parent.

You can also filter by user:

root@4b714989afa3:/opt# ps -f -u root
 UID PID PPID C STIME TTY TIME CMD
 root 1 0 0 Jan21 ? 00:00:00 /bin/sh -c /opt/mssql/bin/s
 root 5 1 0 Jan21 ? 00:00:00 /opt/mssql/bin/sqlservr
 root 7 5 50 Jan21 ? 17:05:04 /opt/mssql/bin/sqlservr
 root 5840 0 0 21:51 pts/0 00:00:00 bash
 root 6249 5840 0 23:49 pts/0 00:00:00 ps -f -u root

Viewing the Contents of a File

You can do this multiple ways, but these are the most common:

• cat <filename>
• view <filename>
• vi/vim <filename>

These are 3/4 options, with the first two offering to view the contents of the file and the 3rd/4th to edit.  You can also edit with the “view” option, but you’ll need to perform an override to write to the choice to view only the file.  This can be done with a standard vi/vim command of :w! while still inside the file.

Yes, that is <colon> <w> <bang>

We’ll go over this in deeper detail in a moment, but let’s go back to just cat’ing a file.  cat, short for concatenate, is one of the most common ways to look through a file and is often used  to parse through the contents.  We can use it in conjunction of other commands, often stated we “pipe” the commands together since we use the “|” symbol to connect the two commands.

And example of this might be:

# cat /home/mssql/scripts/perfdata.log | grep -i ‘cpu’

In the example above, it searches through the perfdata.log file and will return any line with the word cpu in it.  It is capital sensitive and will capture cpuinfo or cpuwait as well as cpu.

Now editing files with vi/vim takes a bit and it’s also considered “old school” but it’s where I live.  Before editing any file, consider backing it up with the copy command:

cp <filename> <bkup filename>

Being able to simply edit from the keyboard without having to click on a mouse makes my life easy and has been second nature for as long as I can remember.  Here are the basic keyboard commands you need to know:

Open a file in vi: vi <filename><enter>

You can hit the escape, <Esc> key to get start over on each command.

Go down a line: j

Go up a line: k

Go to the right: l

Go to the left: h

Insert: i

Append: a

Append to the end of a line: <Shft> a

Add a line and insert below your cursor: o

Add a line and insert ABOVE your cursor: <shft> o

Undo: u

Quit without saving: :q <enter>

Quit without saving to a read only file: :q!<enter>

Write to a file: :w<enter>

Write to a read only file, (override): :w!<enter>

Write to a file and quit: :wq<enter>

Write to a read only file, (override) and quit: :wq!<enter>

 

Well, I think I’ve given you enough to start being dangerous…and how to learn not to be, too.