Microsoft Most Valuable Professional – Data Platform for 2018-2019

Today, I’m proud to announce that I have been renewed as an Microsoft MVP – Data Platform for the 2018-2019 award year, my second MVP award. This is an truly an honor and I’m humbled to be included in this group of exceptional data professionals. I really look forward to continuing to work with everyone in the MVP community and continuing to contribute to our unmatched SQL Community!

MVP Logo Horizontal Secondary Blue286 CMYK 300ppi

What is an MVP?

Here’s the definition according to Microsoft

Microsoft Most Valuable Professionals, or MVPs, are technology experts who passionately share their knowledge with the community. They are always on the “bleeding edge” and have an unstoppable urge to get their hands on new, exciting technologies. They have very deep knowledge of Microsoft products and services, while also being able to bring together diverse platforms, products and solutions, to solve real world problems. MVPs make up a global community of over 4,000 technical experts and community leaders across 90 countries and are driven by their passion, community spirit, and quest for knowledge. Above all and in addition to their amazing technical abilities, MVPs are always willing to help others – that’s what sets them apart.

For 2018-2019, I have been named a Data Platform MVP, which means my technical specialization is on data products like SQL Server. The group of people that have received this award is quite small…by my count 367 worldwide and less than 100 in the US. I’m honored to be in this group of talented professionals.

Why I’m excited to be an MVP?

Honestly, the primary reason I’m excited to be an MVP is to give back (more), I’ve learned so much from other MVPs and receiving this award will help me to continue to build relationships with other MVPs and Microsoft employees to further help develop the Data Platform itself and the community that surrounds that platform.

At the start of 2016 I had set a goal of being an MVP in 5 years. I don’t know why I picked that number, but what I figured was…MVP would be validation of consistent, quality work for our community and being recognized for the work that I’ve contributed. Things like blogging, social media, public speaking and more. You learn a ton by teaching! 

To the people that have helped along the way, I thank you all!

Linux LFCE Learning Path Available at Pluralsight

I’m proud to announce the completion of my first Pluralsight Learning Path. This learning path is built to advance your Linux knowledge to the system administrator or system engineer level. In this series of courses you’ll learn the theory behind how things work and also practice demonstrations and tips to really nail home the things you need to know to run production Linux systems. 

The learning objectives of this series align with the Linux Foundation Certified Engineer (LFCE) certification “Domains and Competencies” however these courses will be very valuable to your development as a Linux professional even if you’re not interested in certification.

Thanks and good luck with your studies! 

Check out the path here – Linux Foundation Certified Engineer (LFCE)

  • LFCE: Advanced Linux Networking – this course is the network primer for the Linux professional, covering the OSI model, IP addressing, ARP and DNS, IP routing, routing on Linux, TCP internals and what to look for with things go wrong.
  • LFCE: Advanced Network and System Administration – in this course you’ll dive into managing network services with systemd, monitoring system performance, advanced package management and sharing data with NFS and Samba.
  • LFCE: Network and Host Security – here, you’ll look at security concepts, using iptables and TCP wrappers, firewalld, using OpenSSH and remote access tools and techniques.
  • LFCE: Linux Service Management HTTP Services – This course is all about Internet architecture and HTTP services. Installing, securing, SSL certificates and troubleshooting Apache.
  • LFCE: Linux Service Management Advanced HTTP Services – In this course you’ll learn how to build scalable Internet architectures with techniques like proxying and caching with Squid, we’ll also cover implementing advanced Apache features with modules.
  • LFCE: Linux Service Management Advanced Email Services – In this course, you will learn common email architectures, and you will explore installing Postfix. After that, you will look at common SMTP scenarios and learn how to secure email relay services and provide clients access to email with IMAP.

    Pluralsight logo vrt color 2

Installing OpenSSH Server on Windows 10

So in yesterday’s post we learned that the OpenSSH client is included with the Windows 10, Update 1803!  Guess, what else is included in this server, an OpenSSH Server! Yes, that’s right…you can now run an OpenSSH server on your Windows 10 system and get a remote terminal! So in this post, let’s check out what we need to do to get OpenSSH Server up and running.

First, we’ll need to ensure we update the system to Windows 10, Update 1803. Do that using your normal update mechanisms.

With that installed, let’s check out the new Windows Capabilities (Features) available in this Update, we can use PowerShell to search through them.

Now to install OpenSSH server, we can use the Add-WindowsCapability cmdlet

To confirm it’s installation we can use the Get-WindowsCapability cmdlet again, and this time it’s state is “Installed”

With that installed, let’s take a look at where sshd lives on our Windows system and that’s in C:\Windows\System32\OpenSSH\

On Windows systems, network daemons run as “Services”. We can see with the Get-Service cmdlet, the installer added ssd and also ssh-agent!

As you can see the state is stopped, so let’s start the Services and also set them to start on boot

We can use netstat to see if we’re up and running

So now that it’s up and running, you should know that the configuration files and host keys live in ProgramData\ssh\ so if you need to change the behavior of SSH you’ll head for the sshd_config file and when finished, restart your service with Restart-Service -Name sshd 

You’ll likely need to open your Windows firewall, which can be done with the following cmdlet on PowerShell 5.1

So let’s test it out, I’m going to ssh from my Mac into my Windows 10 laptop

And that’s it, you can now install OpenSSH server on your Windows 10 system. I can only imagine it’s a matter of time before this hits the server side of things! Bravo PowerShell Team, bravo!

OpenSSH is now Part of Windows!

Today is a big day! The OpenSSH client version 7.6p1 is now part of the Windows 10 operating system! Microsoft released Windows 10 Update 1803 and included in that release is the OpenSSH client, which is installed as part of the update.

That’s right an SSH client as part of the Windows operating system by default! Also included with this update is the OpenSSH Server which is included as an Windows Feature on Demand.

Let’s take a look at what this is all made of!

Start off by updating your system to Windows 10, version 1803. You can do this via your normal Windows Update mechanism.

Here you see I have installed Windows 10, version 1803.

Screen Shot 2018 05 16 at 8 07 53 PM

With that, let’s look at what we got in the update! We’ll search our Windows Capabilities (Features)

Cool, so we know OpenSSH is installed, but where? Let’s check out C:\Windows\System32\OpenSSH\

Let’s look a littler closer at the ssh.exe

So this looks like all of the usual suspects in an OpenSSH installation. But it does look like sshd.exe and ssh_config_default came along for the ride during the update even though we didn’t install the OpenSSH.Server Feature!  More on that in my next blog post…

A big shoutout goes out to the PowerShell team for making this happen, check out the project on GitHub. The code is here and the issues and releases are here!

Speaking at SQLSaturday Atlanta – 733

Speaking at SQLSaturday Atlanta!

I’m proud to announce that I will be speaking at SQL Saturday Atlanta on May 17th 2018! This one won’t let you down! Check out the amazing schedule!

If you don’t know what SQLSaturday is, it’s a whole day of free SQL Server training available to you at no cost!

If you haven’t been to a SQLSaturday, what are you waiting for! Sign up now!

My presentation is Monitoring Linux Performance for the SQL Server Admin” 

SQLSaturday #733 - Atlanta 2018

Here’s the abstract for the talk

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, Memory and Networking monitoring techniques for each and look some of the new tools available including new DMVs and DBFS.Prerequisites: Operating system fundamentals, process, memory and disk concepts.

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 “” -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, 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/

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.

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

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

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. 

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.