Tag Archives: Linux

Distributing SSH User Keys via PowerShell

Folks in the Linux world are used to moving SSH keys to and from systems enabling password-less authentication. Let’s take a minute to look at what it takes to use PowerShell to distribute SSH user keys to remote systems.

In the OpenSSH package there’s a command ssh-copy-id which is a bash script that copies a user’s public key to a remote system. There’s a little intelligence in the script to set things up properly on the remote system for password-less key based authentication. If the appropriate directory and key file aren’t set up, ssh-copy-id will create the directory and key file with the correct permissions on remote system. As far as I can tell, ssh-copy-id has not been implemented in the Win32-OpenSSH port. So that leaves us with implementing this functionality ourselves, in PowerShell.

Since ssh-copy-id isn’t implemented on the OpenSSH port for Windows (because it’s a bash script), I wanted to replicate that functionality so that I could easily copy ssh user keys to systems, consistently and easily. So I implemented this functionality as PowerShell. 

Let’s walk though this…and first up, let’s discuss what’s needed for password-less, key based authentication.

The Components of Password-less Key Based Authentication

For password-less key based authentication to work, you need to copy the user’s public key from the local system you want to authenticate from, to the remote system. On the remote system, this key file has to live in a place where the SSH deamon expects it, and that’s in the file ~./ssh/authorized_keys by default.

Let’s take a second to look at the details of how this needs to be configured on a remote system.

  • authorized_keys – this is the default file in which user public keys are stored. The permissions on this file should be 600. Which is read/write for the owner and no access to group or other/world.

-rw-r–r–. 1 demo demo 412 Feb 18 08:53 .ssh/authorized_keys 


  • ~./ssh – the authorized_keys file lives in a hidden directory in your home directory. That’s what that syntax means, the ~ (tilde) is short for the current user’s home directory and that . (dot) indicates that the directory is a hidden directory. Now, the permissions on this directory should be 700, this means it’s it’s read/write/execute to the owner and no access to group or other/world. The execute bit on a directory gives you access to list the contents of the directory and enter that directory.

drwx——. 2 demo  demo         29 Feb 18 08:53 .ssh

It’s kinda like ssh-copy-id, but in PowerShell

First up, I’m assuming that you have SSH remoting already configured, have generated your ssh user key and that you’re on a Windows, Linux/Mac system and you want to copy and SSH user key to a Linux/Mac system. I plan on covering copying keys to Windows systems in an upcoming post. The only real difference between the two is how you set permissions on the .ssh directory and the authorized_keys file. 
 
The first thing that we want to do is to create a PSSession to our host. We’ll reuse this session a few times to execute the required commands on the remote host. This demo user is the user we will want to setup key based authentication for. This session creation will ask for our password. Hopefully this is the last time you have to type it ;)
 

$s New-PSSession -HostName “172.16.94.10” -UserName demo


Then, we’ll read in our public key from our local system into a variable. It’s imperative that you read the public key, id_rsa.pub. The other file, id_rsa is your private key. That needs to stay on the system you want to authenticate from and needs to stay secure.

$key Get-Content -Path ~/.ssh/id_rsa.pub


Next, we’ll want to check to see if the .ssh directory exists in the home directory of our user on the remove system. If not, create the .ssh directory.

Invoke-Command -Session $s -ScriptBlock { If(!(Test-Path -Path ~./ssh)) { New-Item ~/.ssh -ItemType Directory} } 

 
Now, with the directory in place, let’s be sure the permissions are set properly, and that’s 700 in octal notation.
 

Invoke-Command -Session $s -ScriptBlock { chmod 700 ~/.ssh  }  

 
After that, we can copy our key to the remote system’s authorized_keys file. We’ll take advantage of the Out-File cmdlet and use the -Append switch to handle file existence on the remote system and append our key to an existing file or create a new file if it doesn’t exist yet. All that fancy syntax around Invoke-Command is so we can pass a local variable into the Out-File cmdlet over our remoting session.
 

Invoke-Command -Session $s -ScriptBlock { param([string] $key) Out-File -FilePath ~/.ssh/authorized_keys -Append -InputObject $key } -Args $key

 
Now, with the file on the remote system, let’s ensure the permissions are set properly.
 

Invoke-Command -Session $s -ScriptBlock { chmod 600 ~/.ssh/authorized_keys  }

 
..and with that let’s take it for a test run and see if we can open a PSSession without a password using Enter-PSSession
 

PS /Users/demo> Enter-PSSession -HostName server1 -UserName demo
[server1]: PS /home/demo> 

 
Now, there’s a few things I want to point out. This code here is to highlight the needed steps to configure key based authentication. I certainly could (and should) make this code more production ready…but I’ll leave that up to you as the reader. What I really want to highlight here are the steps required for proper key distribution to remote systems, such as directories, files and the required permissions. Oh, if you’re like why don’t you just use ssh-copy-id…fan out remoting. We can use this technique to easily distribute our keys to many systems.

I hope this helps you get an understanding of how key based authentication works, how to configure it and also how to get those keys out to your remote systems!

New Pluralsight Course – LFCE: Linux Service Management – Advanced HTTP Services

My new course “LFCE: Linux Service Management – Advanced HTTP Services” in now available on Pluralsight here! If you want to learn about the course, check out the trailer here or if you want to dive right in check it out here! This course offers practical tips from my experiences building high performance web infrastructure for Centino Systems clients.

This course targets IT professionals that design and maintain RHEL/CentOS based enterprises. It aligns with the Linux Foundation Certified System Administrator (LFCS) and Linux Foundation Certified Engineer (LFCE) and also Redhat’s RHCSA and RHCE certifications. The course can be used by both the IT pro learning new skills and the senior system administrator preparing for the certification exam

Let’s take your LINUX sysadmin skills to the next level and get you started on your LFCS/LFCE learning path.

The modules of the course are:

  • Building Scalable Internet Architectures – Overview of core techniques to ensure your website can perform as scale
  • Installing and Configuring Squid Proxy Server – Let’s use Squid to help accelerate our clients web access and provide a better client experience
  • Configuring Advanced HTTP Services: Apache Modules – Overview of using Apache Modules to provide additional functionality to your web site
  • Configuring Proxying and Caching for HTTP Services – A close look at one of the core building blocks of a high performance website, the reverse proxy

Pluralsight Redhat Linux

Check out the course at Pluralsight!

Speaking at PowerShell Summit 2018!

I’m proud to announce that I will be speaking at PowerShell + DevOps Global Summit 2018 on the conference runs from April 9th 2018 through April 12th 2018. This is an incredible event packed with fantastic content and speakers. Check out the amazing schedule! All the data you need on going is in this excellent brochure right here!

This year I have two sessions!

On Tuesday, April 10th at 2:00PM – I’m presenting “OpenSSH Internals for PowerShell Pros

Here’s the abstract

In PowerShell Core we can use OpenSSH as the transport layer to carry our remoting sessions between our systems. In this session we’ll look at OpenSSH architecture, Authentication methods, including key authentication, sshd configuration, and troubleshooting methods when things go wrong!
  In this session we’ll cover the following: 
                – OpenSSH Architecture
                – Authentication methods
                – Key based authentication
                – sshd Configuration
                – Troubleshooting OpenSSH 

On Wednesday, April 11th at 9:00AM – I’m presenting a workshop with none other than Richard Siddaway on PowerShell Remoting – Installing and troubleshooting in a Multiplatform environment

Here’s the abstract

PowerShell Core is about choice and the transport layer for remoting is one of those choices. In this session we’ll look at remoting in Multiplatform environments, configuring both OpenSSH and WinRM based remoting and how we can leverage remoting to really scale up our administrative capabilities.

I look forward to seeing you there. If you’re on the fence about registering, don’t wait! Click here and do so now. It’s selling out fast!

PowerShell Summit

Attempting to Run SQL on Linux Inside Windows Subsystem for Linux

Shawn Melton MVP and dbatools contributor last week had an issue running SQL Server on Linux inside of Windows Subsystem for Linux.

I didn’t want to leave a brother hanging so I spent this morning digging into this a little bit. 

Reproducing the Issue

The first thing I had to do was reproduce the issue. So on my Windows 10 test VM I installed the Windows Subsystem for Linux, steps to do so are here and I installed the Ubuntu app.

Then, I fired up a bash shell using WSL and then I installed SQL Server on Linux for Ubuntu as documented here

Now, I completed the installation of SQL Server on Linux using mssql-conf when that program completes it attempts to start SQL Server on Linux. BOOM! I’m able to reproduce the same error.

Looking at the error, I decided to see if I could run SQL Server on Linux from the shell as the user mssql. This would remove systemd and mssql-conf from the picture. Basically I wanted to see if I could get another, more descriptive, error to pop out. To do that we’ll need to change over to the mssql user with su.

sudo su mssql -

And then change into the working directory for SQL Server on Linux and try to launch SQL Server.

cd /var/opt/mssql/
/opt/mssql/bin/sqlservr

Now, doing that…generates same same error! Here’s the error in a search engine friendly form :)

mssql@DESKTOP:~$ /opt/mssql/bin/sqlservr
This program has encountered a fatal error and cannot continue running.
The following diagnostic information is available:
Reason: 0x00000003
Message: fd != -1
Stacktrace: 00007f818942d4d3 00007f8188de76ba 00007f81863e73dd
Process: 79 - sqlservr
Thread: 80
Instance Id: 50bd6e1b-8f6c-45b3-939d-2338725d8b4a
Crash Id: d38007c0-48c6-4374-9205-5539333138ff
Build stamp: 5fb3474a5f63ad2f4b7eddadad44a086839721f18a66c5fb5d7cfcce25c0f539
This program has encountered a fatal error and cannot continue running.
The following diagnostic information is available:
Reason: 0x00000003
Message: fd != -1
Stacktrace: 00007f818942d6ac 00007f8188de76ba 00007f81863e73dd
Process: 81 - sqlservr
Thread: 83
Instance Id: 50bd6e1b-8f6c-45b3-939d-2338725d8b4a
Crash Id: d38007c0-48c6-4374-9205-5539333138ff
Build stamp: 5fb3474a5f63ad2f4b7eddadad44a086839721f18a66c5fb5d7cfcce25c0f539
*********** PANIC CORE DUMP GENERATION FAILED **********
Attempt to launch handle-crash.sh failed with error 0x0000000C
Aborted (core dumped)

Digging a Little Deeper

So now with the same error output, I decided to give it a cursory pass with strace to see if I could find anything that would put us closer to why SQL Server on Linux won’t start when using Windows Subsystem for Linux.

What you see in the strace output is the parent process creating the child sqlservr process and failing. In the first line of output you can see process 137 clone and return process ID 139. Which is how a parent process creates a child in Linux. Then process 139 tries to perform some setup operations like registering signal actions (rt_sigaction) and their corresponding routines to call when that signal is received by that process.

Now the only error I found in the output is the prctl call which returns invalid argument.This system call is to perform operations on a process.  On my WSL system the option being set PR_SET_PTRACER is for the Yama LSM subsystem which lives in /proc/sys/kernel/yama normally. This doesn’t exist on my Ubuntu WSL installation. I checked my CentOS full VMs and this exists. I checked a full Ubuntu installation and it’s there too.

After the error SQL Server calls tgkill and kills itself with the SIGABRT signal. A dump occurs and the program exits. 

137 clone(child_stack=0x7fb1a0feff30, flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID, parent_tidptr=0x7fb1a0ff09d0, tls=0x7fb1a0ff0700, child_tidptr=0x
7fb1a0ff09d0) = 139
139 set_robust_list(0x7fb1a0ff09e0, 24 <unfinished ...>
137 fcntl(1, F_SETFL, O_RDONLY|O_APPEND <unfinished ...>
139 <... set_robust_list resumed> ) = 0
137 <... fcntl resumed> ) = 0
139 gettid( <unfinished ...>
137 fcntl(2, F_SETFL, O_RDONLY|O_APPEND <unfinished ...>
139 <... gettid resumed> ) = 139
137 <... fcntl resumed> ) = 0
139 rt_sigaction(SIGABRT, {0x7fb1a6a2e470, [ABRT], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, <unfinished ...>
137 getrlimit(RLIMIT_NOFILE, <unfinished ...>
139 <... rt_sigaction resumed> {0x7fb1a6a2d290, [ABRT], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
137 <... getrlimit resumed> {rlim_cur=1024, rlim_max=4*1024}) = 0
139 rt_sigaction(SIGILL, {0x7fb1a6a2e470, [ILL], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, <unfinished ...>
137 setrlimit(RLIMIT_NOFILE, {rlim_cur=4*1024, rlim_max=4*1024} <unfinished ...>
139 <... rt_sigaction resumed> {0x7fb1a6a52790, [], SA_RESTORER|SA_STACK|SA_NODEFER|SA_SIGINFO, 0x7fb1a63f1390}, 8) = 0
137 <... setrlimit resumed> ) = 0
139 rt_sigaction(SIGFPE, {0x7fb1a6a2e470, [FPE], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, <unfinished ...>
137 gettid( <unfinished ...>
139 <... rt_sigaction resumed> {0x7fb1a6a52790, [], SA_RESTORER|SA_STACK|SA_NODEFER|SA_SIGINFO, 0x7fb1a63f1390}, 8) = 0
137 <... gettid resumed> ) = 137
139 rt_sigaction(SIGSEGV, {0x7fb1a6a2e470, [SEGV], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, <unfinished ...>
137 rt_sigprocmask(SIG_BLOCK, [TERM], <unfinished ...>
139 <... rt_sigaction resumed> {0x7fb1a6a52790, [], SA_RESTORER|SA_STACK|SA_NODEFER|SA_SIGINFO, 0x7fb1a63f1390}, 8) = 0
137 <... rt_sigprocmask resumed> NULL, 8) = 0
139 rt_sigaction(SIGBUS, {0x7fb1a6a2e470, [BUS], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, <unfinished ...>
137 rt_sigtimedwait([TERM], NULL, NULL, 8 <unfinished ...>
139 <... rt_sigaction resumed> {0x7fb1a6a52790, [], SA_RESTORER|SA_STACK|SA_NODEFER|SA_SIGINFO, 0x7fb1a63f1390}, 8) = 0
139 rt_sigaction(SIGTRAP, {0x7fb1a6a2e470, [TRAP], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a52790, [], SA_RESTORER|SA_STACK|SA_NODEFER|SA_SIGINFO, 0x7fb1a63f1390}, 8) = 0
139 rt_sigaction(SIGSYS, {0x7fb1a6a2e470, [SYS], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2d290, [SYS], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGXCPU, {0x7fb1a6a2e470, [XCPU], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2d290, [XCPU], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGXFSZ, {0x7fb1a6a2e470, [XFSZ], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2d290, [XFSZ], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGSTKFLT, {0x7fb1a6a2e470, [STKFLT], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2d290, [STKFLT], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 mmap(NULL, 4194304, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fb1a03f0000
139 munmap(0x7fb1a03f0000, 4194304) = 0
139 mmap(NULL, 8384512, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fb19fff0000
139 munmap(0x7fb19fff0000, 65536) = 0
139 munmap(0x7fb1a0400000, 4124672) = 0
139 open("/proc/self/status", O_RDONLY) = 41
139 fstat(41, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
139 read(41, "Name:\tsqlservr\nState:\tS (sleepin"..., 4096) = 663
139 close(41) = 0
139 rt_sigaction(SIGABRT, {0x7fb1a6a2d290, [ABRT], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [ABRT], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGILL, {0x7fb1a6a2d290, [ILL], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [ILL], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGFPE, {0x7fb1a6a2d290, [FPE], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [FPE], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGSEGV, {0x7fb1a6a2d290, [SEGV], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [SEGV], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGBUS, {0x7fb1a6a2d290, [BUS], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [BUS], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGTRAP, {0x7fb1a6a2d290, [TRAP], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [TRAP], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGSYS, {0x7fb1a6a2d290, [SYS], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [SYS], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGXCPU, {0x7fb1a6a2d290, [XCPU], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [XCPU], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGXFSZ, {0x7fb1a6a2d290, [XFSZ], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [XFSZ], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 rt_sigaction(SIGSTKFLT, {0x7fb1a6a2d290, [STKFLT], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, {0x7fb1a6a2e470, [STKFLT], SA_RESTORER|SA_RESTART, 0x7fb1a39154b0}, 8) = 0
139 prctl(PR_SET_PTRACER, PR_SET_PTRACER_ANY) = -1 EINVAL (Invalid argument)
139 prctl(PR_SET_PDEATHSIG, SIG_0) = 0
139 open("/proc/self/status", O_RDONLY) = 41
139 fstat(41, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
139 read(41, "Name:\tsqlservr\nState:\tS (sleepin"..., 4096) = 663
139 close(41) = 0
139 rt_sigprocmask(SIG_UNBLOCK, [ABRT], NULL, 8) = 0
139 tgkill(137, 139, SIGABRT) = 0
139 --- SIGABRT {si_signo=SIGABRT, si_code=SI_TKILL, si_pid=137, si_uid=999} ---
139 gettid() = 139
139 write(2, "Dump collecting thread [139] hit"..., 57) = 57
139 exit_group(-1) = ?
137 +++ exited with 255 +++
138 +++ exited with 255 +++
139 +++ exited with 255 +++

What’s Really Happening?

Well I think something is missing from Windows Subsystem for Linux. Is it the Yama stuff…perhaps. But clearly SQL Server isn’t happy with the environment and kills itself. I haven’t dove into WSL yet and I don’t know how it’s implemented, but there could also be something up at that level too. Generally I don’t write blog posts where I don’t know exactly what’s going on, but I did want to let folks know that SQL on Linux doesn’t work on Windows Subsystem for Linux. 


A Novel Idea for High Availability in SQL Server on Linux

Over the past year we’ve learned about how SQL Server on Linux is implemented, leveraging SQLPAL and the team is pretty confident in their architectural decisions as indicated in this post here.

Now that there is this wrapper around SQL Server, this really opens up some interesting opportunities…perhaps we can leverage SQLPAL to facilitate some new high availability techniques.

When I was in graduate school, I worked on a research project, that became my master’s thesis. In this work, I developed a technique that synchronized the process address space of a virtual machine on two separate physical hypervisors.The technique involved an initial copy of all pages between the two systems and then selectively copying the virtual machine’s pages as they became dirty. Using this technique, the process address space of the virtual machine is synchronized between the two hypervisors. This allows for a significant reduction in the amount of information that had to be replicated between the hypervisors but more importantly…the virtual machines memory in sync which meant if hypervisor hosting the virtual machine crashed we could theoretically start the virtual machine on the second hypervisor.

Now, during my PASS Summit talk this year, I presented to the audience my theory that SQLPAL is virtualization. But it’s not machine virtualization, it’s process virtualization. Which means there’s a purpose built environment hosting the SQL Server process. This environment, SQLPAL, is the main allocator of resources from the physical system. It’s the thing that asks for memory, disk, network anything that’s needed from the underlying operating system.

Now, what if we took these two ideas and brought them together? What if SQLPAL was able to synchronize the program state and resources between two separate systems? Could we provide highly available SQL Services with a technique like this? I think we can. Perhaps we don’t even synchronize the pages between the system. Perhaps an even lighter technique could be used, such as duplicating the system calls between the two copies of SQL Server and thus implicitly synchronizing the program state.

Think about the possibilities…we could have a system that fails over with all the context of the currently active system, active connections could stay active, buffer pool populated, plan cache could still exist and not have to be rebuilt. Yes, we’ll likely need some sort of low latency, high bandwidth interconnect..but we have those. And there’s certainly more implementation details that need to be thought through…but I think there’s something here. 

A couple questions I thought of while writing this…

1. Does this provide more value than Availability Groups? I think so…program state remains in sync between the two systems. So things like user connections could be maintained during failover (with the appropriate relocation of the IP of course). I also think the quorum model would be simpler, as there is only one pair in the synchronization.

2. Does this provide more value than virtual machine migration, perhaps. This technique could be hypervisor independent.

I’d love to hear your thoughts on this! Most of all I want you to start thinking about new ways we can leverage SQLPAL and it’s abstraction from hardware.

I’m Presenting Two Linux Sessions on One Day!

On 12/13 I’m presenting two, back to back, sessions on SQL Server on Linux online. So you can attend from anywhere!

Let’s go through both!

First, on 12/13 at 1PM Central, I’m presenting for the PASS Database Administration Virtual Group here’s my session details:

Topic: Linux OS Fundamentals for the SQL Admin

Registration: You must register if you want to attend. You can register at http://dba.pass.org/. When you register, you will receive a link to the meeting. All attendees will be entered into a raffle for a $25 gift card.

Abstract: SQL Server and PowerShell are now available on Linux and management wants you to leverage this shift in technology to more effectively manage your systems, but you’re a Windows admin! Don’t fear! It’s just an operating system! It has all the same components Windows has and in this session, we’ll show you that. We will look at the Linux operating system architecture and show you how to interact with and manage a Linux system. By the end of this session you’ll be ready to go back to the office and get started working with Linux with a fundamental understanding of how it works.

 

Second, on 12/13 at 2PM Central, I’m presenting at the PASS Marathon: Linux Edition here’s my session details:

Topic: Monitoring Linux Performance for the SQL Server Admin

Registration: You must register if you want to attend. You can register at http://www.pass.org/marathon/2017/december/Registration.aspx.

Abstract: So you’re a SQL Server administrator and you just installed SQL Server on Linux. It’s a whole new world. Don’t fear, it’s just an operating system. It has all the same components Windows has and in this session we’ll show you that. We will look at the Linux operating system architecture and show you where to look for the performance data you’re used to! Further we’ll dive into SQLPAL and how it architecture and internals enables high performance for your SQL Server. By the end of this session you’ll be ready to go back to the office and have a solid understanding of performance monitoring Linux systems and SQL on Linux. We’ll look at the core system components of CPU, Disk, and Memory monitoring techniques for each and look some of the new tools available including new DMVs and DBFS.
Hope to see you online!

NewImage

Top 5 Reasons Why I think SQL Server on Linux is Legit

Here are my top 5 reasons why I thing SQL Server on Linux is Legit!

  1. SQL Server on Linux is Fast – Earlier this year SQL Server on Linux posted the fasted 1TB TPC-H benchmark in the world and at the end of October posted the a 10TB result! Check out the results here and some info on how they did it here and here.
  2. It’s tunable – From and OS standpoint, I think the “tunability” of the operating system is more well documented and well known on Linux. Check out Microsoft’s recommendations here and also Redhat’s here.
  3. Features – If you’re a developer in the Linux ecosphere, this is the reason why you’re evaluating using SQL Server on Linux…there’s likely a feature you want…that you can now have. Check them out here!
  4. Enterprise Support – I like cruising around in forums just like anybody else, but sometimes you have to call support to bring in the people that actually wrote the software.
  5. Availability Solutions – SQL Server has a proven track record for availability, those same concepts and techniques apply to SQL Server on Linux. Backups, Availability Groups, and Failover Clusters check it out.

Launching SQL Server on Linux in Single User Mode

There was a question this morning on the SQL Server Community Slack channel from SvenLowry about how to launch SQL Server on Linux in Single User Mode. Well you’ve heard everyone say, it’s just SQL Server…and that’s certainly true and this is another example of that idea.

The command line parameters from the sqlservr binary are passed through into the SQLPAL managed Win32 SQL Process. So let’s check out how to do this together…

First, you’ll want to switch to the user mssql and you can do that with this command

bash-4.2$ sudo su mssql –


What’s happening here is we’re using sudo to switch our user’s security context to the user mssql. This is the account that SQL Server normally runs under. That last dash there is to load the mssql user’s shell, rather than ours.
 
Next, we need to launch the /opt/mssql/bin/sqlservr binary with the -m parameter
 

bash-4.2$ /opt/mssql/bin/sqlservr -m 


Here’s the output from the console while SQL Server is starting up. 

2017-11-09 12:53:18.70 Server      Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) – 14.0.3006.16 (X64) 

Oct 19 2017 02:42:29 

Copyright (C) 2017 Microsoft Corporation

Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

2017-11-09 12:53:18.70 Server      UTC adjustment: -6:00

2017-11-09 12:53:18.70 Server      (c) Microsoft Corporation.

2017-11-09 12:53:18.70 Server      All rights reserved.

2017-11-09 12:53:18.70 Server      Server process ID is 4120.

2017-11-09 12:53:18.70 Server      Logging SQL Server messages in file ‘/var/opt/mssql/log/errorlog’.

2017-11-09 12:53:18.70 Server      Registry startup parameters: 

-d /var/opt/mssql/data/master.mdf

-l /var/opt/mssql/data/mastlog.ldf

-e /var/opt/mssql/log/errorlog

2017-11-09 12:53:18.70 Server      Command Line Startup Parameters:

-m

Output omitted…

2017-11-09 12:53:19.50 spid4s      SQL Server started in single-user mode. This an informational message only. No user action is required.

Output omitted…

2017-11-09 12:53:19.90 spid4s      Always On Availability Groups was not started because the SQL Server instance is running in single-user mode.  This is an informational message.  No user action is required.

Output omitted…

2017-11-09 12:53:20.62 spid4s      Recovery is complete. This is an informational message only. No user action is required.


From here, you can go about what ever task it is you needed single user mode for.
 

New Pluralsight Course – SQL Server on Linux Administration Fundamentals

New Pluralsight Course – SQL Server on Linux Administration Fundamentals

My new course “SQL Server on Linux Administration Fundamentals” in now available on Pluralsight here! If you want to learn about the course, check out the trailer here or if you want to dive right in check it out here!
 
This course targets DBAs that design and maintain SQL Server on Linux systems (or those evaluating the technology). This course can be used by both the seasoned DBA to learn foundational Linux skills and also what’s new and different when running SQL Server on Linux. 

Course Description

SQL Server is available on Linux, and management wants you to leverage this shift in technology to more effectively manage your data platform. In this course, SQL Server on Linux Administration Fundamentals, you’ll delve into SQL on Linux in order for you to become an effective DBA.  First, you’ll explore an overview of its architecture, installation, and configuration. Next, you’ll learn how to administer SQL Server on Linux. Finally, you’ll discover high availability and disaster recovery options available to you for keeping your SQL Server online. By the end of this course, you’ll have a solid foundation necessary to utilize SQL Server on Linux in production.

The modules of the course are:

  • Introduction and SQL Server Architecture – Introduce the viewer into world of SQL Server on Linux. Why did Microsoft do this? What’s the strategy? Introduce the SQL Server Ecosphere, such as the database engine, SQL Server Agent and SSIS.
  • Installing and Configuring SQL Server on Linux – We’ll look at our installation and configuration options for SQL Server on Linux, introducing Linux package managers and repositories and install SQL Server on Linux and it’s components.
  • Administering Linux for DBAs – We’ll look at managing services with systemd and how to query journald’s log files for information about SQL Server.  Also dive into file ownership, disk partitioning concepts and mounting file systems and remote file systems.
  • Managing SQL Server on Linux: Administration and Tools – Now that the viewer knows where things are in this new operating system, let’s move up the stack and look at the tooling available for SQL Server on Linux. We’ll cover VS Code, SSMS, SQLCMD and DBFS.
  • High Availability and Disaster Recovery with SQL Server on Linux – Dive into the High Availability and Disaster Recovery options available to SQL Server on Linux
     

Pluralsight Redhat Linux

Check out the course at Pluralsight!

Exit Codes, systemd and SQL Server on Linux

In this blog post we’re going to cover systemd, process exit codes and highlight how systemd reacts in certain exit conditions from SQL Server on Linux. My friend and SQL Server guru Argenis Fernandez – @dbargenis asked about this behavior on Twitter and I’ve been meaning to write this post, so here you go! Also, there’s a Connect item filed by Argenis on this here. Vote!

systemd Basics

Systemd is an initialization daemon, it’s job is to bring the system to usable state. Meaning, it’s responsible for the orderly starting of services on a Linux system. It does much more than that, in fact, one of it’s other core components is journald. Journald stores logging information from systemd units. 

Now with respect to SQL Server on Linux, the information logged into journald is systemd’s information about our mssql.service unit and also the SQL Server Error Log. The SQL Server Error Log lands in here because it’s written to standard out and by default, a service unit’s standard out is written into the journal. 

We can query the information stored in journald with the command journalctl and below is the syntax to query a particular service unit’s log in journald. Running just journalctl will cause the text output to run off the side of the screen without wrapping the text in your terminal. To get the text to wrap, use the following code…then pipe the output into a pager like more or less because you know…less is more. We’ll use this command in the next section.

journalctl -u mssql-server –no-pager | more

Service units in systemd are configured in unit files and SQL Server on Linux’s unit file lives in the file /usr/lib/systemd/system/mssql-server.service. Inside that file we have the following configuration:

# Restart on non-successful exits.

Restart=on-failure

What Restart=on-failure option does, is if a systemd unit returns an unclean exit code, systemd will automatically restart the service. There are other conditions that cause it to restart such as responding to unclean signals, watchdog and also service timeouts. We’ll save those discussions for another day.

Understanding Process Exit Codes

When a process exits, it will return an integer value to the parent process. If a process terminates cleanly, it conventionally returns 0 to the parent process. This means all is well in the world and the process has shut down. In a relational database system this is significant, in that, we really do want our systems to shut down cleanly. Now when things go wrong, that’s when process return a non-zero value.  systemd is the parent process of the sqlservr process and receives it’s exit code. Let’s see what happens in certain exit conditions from SQL Server on Linux.

Let’s say we initiate a shutdown in SQL Server the T-SQL command SHUTDOWN here’s what we’ll get in journald. 

Oct 28 05:27:58 sqlb.lab.centinosystems.com sqlservr[1103]: 2017-10-28 05:27:58.37 spid51      Server shut down by request from login sa.

Oct 28 05:27:58 sqlb.lab.centinosystems.com sqlservr[1103]: 2017-10-28 05:27:58.37 spid51      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

Oct 28 05:28:00 sqlb.lab.centinosystems.com systemd[1]: mssql-server.service: main process exited, code=exited, status=1/FAILURE

Oct 28 05:28:00 sqlb.lab.centinosystems.com systemd[1]: Unit mssql-server.service entered failed state.

Oct 28 05:28:00 sqlb.lab.centinosystems.com systemd[1]: mssql-server.service failed.

Oct 28 05:28:00 sqlb.lab.centinosystems.com systemd[1]: mssql-server.service holdoff time over, scheduling restart.

Oct 28 05:28:00 sqlb.lab.centinosystems.com systemd[1]: Started Microsoft SQL Server Database Engine.

Oct 28 05:28:00 sqlb.lab.centinosystems.com systemd[1]: Starting Microsoft SQL Server Database Engine…

Oct 28 05:28:02 sqlb.lab.centinosystems.com sqlservr[1822]: 2017-10-28 05:28:02.87 Server      Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) – 14.0.3006.16 (X64)

 
Now in the output above, you’ll notice a bolded line. In there, you can system that systemd[1] receives a return code from SQL Server of status=1/FAILURE.  Systemd[1] is the parent process to sqlservr, in fact it’s the parent to all processes on our system. It receives the exit code and immediately, systemd initiates a restart of the service due to the configuration we have for our mssql-server systemd unit.
 
What’s interesting is that this happens even on a normal shutdown. But that simply doesn’t make sense, return values on clean exits should return 0. It’s my understanding of the SHUTDOWN command, that it will cause the database engine to shutdown cleanly. 
 
Now let’s say we initiate a SHUTDOWN WITH NOWAIT
 

Oct 28 05:31:24 sqlb.lab.centinosystems.com sqlservr[1822]: 2017-10-28 05:31:24.22 spid51      Server shut down by NOWAIT request from login sa.

Oct 28 05:31:24 sqlb.lab.centinosystems.com sqlservr[1822]: 2017-10-28 05:31:24.22 spid51      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

Oct 28 05:31:25 sqlb.lab.centinosystems.com systemd[1]: mssql-server.service: main process exited, code=exited, status=1/FAILURE

Oct 28 05:31:25 sqlb.lab.centinosystems.com systemd[1]: Unit mssql-server.service entered failed state.

Oct 28 05:31:25 sqlb.lab.centinosystems.com systemd[1]: mssql-server.service failed.

Oct 28 05:31:25 sqlb.lab.centinosystems.com systemd[1]: mssql-server.service holdoff time over, scheduling restart.

Oct 28 05:31:25 sqlb.lab.centinosystems.com systemd[1]: Started Microsoft SQL Server Database Engine.

Oct 28 05:31:25 sqlb.lab.centinosystems.com systemd[1]: Starting Microsoft SQL Server Database Engine…

Oct 28 05:31:27 sqlb.lab.centinosystems.com sqlservr[2035]: 2017-10-28 05:31:27.64 Server      Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) – 14.0.3006.16 (X64)


In this output, we see the same behavior, SQL Server returns a status=1/FAILURE and restarts. This makes sense to me, an immediate shutdown should return a non-zero value and it does. 
 
In this post we covered, systemd units and their restart configurations. We looked at exit codes and how they’re used to communicate back to the parent process about the child’s exit status. While it’s just SQL Server…as DBAs we still need to learn about this new operating environment and how it works! Keep learning!
 
For more details on this behavior and it’s configuration I encourage you to read the documentation at this link here. Further, if you’d like a deep dive into systemd check out my Pluralsight course LFCE: Advanced Network and System Administration In this course I cover systemd in great detail, it’s architecture, units and target.