Monthly Archives: June 2017

Reflecting on the Last Year of Microsoft’s OpenSource Technologies

This past year has certainly been interesting in the world of Linux. Microsoft has taken a new strategy and is embracing the open source model. It’s releasing it’s key software products with versions for Linux. It’s truly a remarkable time. In this post I want to highlight some of the bigger events and cover what does this mean to you and where you can go do get some training on these topics.

Here’s some of the highlights from the last year

Microsoft becomes a Platinum Member in the Linux Foundation – this means Microsoft is committing itself to a long term investment in the Open Source community and continuing to develop open source software. Don’t believe me on the Open Source thing…well check out their GitHub repo. Who would have seen this coming? 

Now, let’s Look at the new tools you have to build cross platform applications and develop your systems

  • .NET Core – Literally you can build native .NET applications to run on any platform, Windows, Linux, Mac…Docker!
  • bash Ubuntu on Windows – One of the primary reasons I bought my first Mac years ago was I wanted a bash shell, well now I’m not tied to this hardware anymore. 
  • Visual Studio Code – With all this cross platform stuff, you’ll need a consistent development environment, VS Code runs on Windows, Linux and Mac. And it’s darn nice too. Very extensible with many languages available. 
  • SQL Server on Linux – This is the real deal, it’s fast and consistent with your existing SQL Server experience. I’ve blogged about it a bit :)
  • PowerShell Core – Microsoft adds another management tool to your tool belt with this. Windows, Linux and Mac…can be managed all with one Language. For me, this was mind blowing, I got to do a training video with literally the inventor of PowerShell Jeffrey Snover and MVP Jason Helmick! I blogged about PowerShell a bit too.

What does this mean to you?

So what’s this mean to you? Get out there and start learning about this stuff and discover how it can impact you. In the coming years new solutions are going to be developed using these components and it’s upon you to train yourself and learn how to leverage these tools to solve problems. 

I’ve spent the last year developing some fun training at Pluralsight I think you should check out. The training is based on the Linux Foundation Certified Engineer curriculum and takes you from installation up to a running Linux system. 

  • Understanding and Using Essential Tools for Enterprise Linux 7 – If you’re new to Linux, start here! This will course will help you install Linux and get oriented with the operating system and the command line interface. 
  • LFCE: Advanced Network and System Administration – Next, you’ll need to learn how to control your system’s services, install packages, manage performance and share data between systems. Check this course out to make your Linux system really work for you
  • LFCE: Advanced Linux Networking – Your systems don’t stand alone, in this course you’ll dive deep into how data moves between Linux systems. Protip, these concepts apply to Windows systems too.
  • LCFE: Network and Host Security – My newest course, let’s learn how to secure our Linux systems from both the networking and host perspective. We’ll cover security concepts and architectures, securing Linux services and take a deep dive into OpenSSH and remote access.
  • LFCE: Linux Service Management – HTTP Services – I’m currently developing a course on HTTP Services – you’ll learn how to install, configure and manage Apache.
  • More to follow – announcements coming up soon! I can’t wait to tell you what’s next.

I’ve got tons of blog posts on these topics, 

So go ahead, get digging in there learn download Linux (yes, I prefer CentOS), install SQL Server and PowerShell and start moving your skills towards where the technology is going to take you!

Speaking at SQLSaturday Sacramento – 650!

Speaking at SQLSaturday Sacramento!

I’m proud to announce that I will be speaking at SQL Saturday Sacramento on July 15th 2017! And wow, 650 SQLSaturdays! 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!


This year I have TWO sessions!

1. Linux OS Fundamentals for the SQL Admin

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 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.

2. Designing High Availability Database Systems using AlwaysOn Availability Groups

Are you looking for a high availability solution for your business critical application? You’re heard about AlwaysOn Availability Groups and they seem like a good solution, but you don’t know where to start. It all starts with a solid design. In this session we introduce the core concepts needed to design a Availability Group based system. Covering topics such as recovery objectives, replica placement, failover requirements, synchronization models, quorum, backup and recovery and monitoring. This session is modeled after real world client engagements conducted by Centino Systems that have lead to many successful Availability Groups based systems supporting tier 1 business critical applications.

dbfs – command line access to SQL Server DMVs

With SQL Server on Linux, Microsoft has recognized that they’re opening up their products to a new set of users. People that aren’t used to Windows and it’s tools. In the Linux world we have a set of tools that work with our system performance data and present that to us as text. Specifically, the placeholder for nearly all of the Linux kernel’s performance and configuration data is the /proc virtual file system, procfs. Inside here you can find everything you need that represents the running state of your system. Processes, memory utilization, and disk performance data all of this is presented as files inside of directories inside /proc.

Now, let’s take this idea and extend it to SQL Server. In SQL Server we have DMVs, dynamic management views. These represent to current running state of our SQL Server. SQL Server exposes the data in DMVs as table data that we can query using T-SQL. 

So, Microsoft saw the need to bring these two things together, we can expose the internals of SQL Server and its DMVs to the command line via a virtual file system. And that’s exactly what dbfs does, it exposes all of SQL Server’s DMVs as text files in a directory. When you access one of the text files…you’ll execute query against the SQL Server and the query output comes back to you via standard output to you Linux console. From there you can use any of your Linux command line fu…and do what you want with the data returned. 

Setting up dbfs

So first, let’s go ahead and set this up. I already have the Microsoft SQL Server repo configured so I can install via yum. If you have SQL on Linux installed, you likely already have this repo too. If not, go ahead and follow the repo setup instructions here. To install dbfs we use yum on RHEL based distributions.

sudo yum install dbfs -y
First off, think about what’s going on under the hood here…we’re going to allow the system to execute queries against DMVs…so let’s try to keep this as secure as possible, I’m going to create a user that is allowed to only query DMVs with the VIEW SERVER STATE permission. So let’s do that…
Let’s log into our SQL Server via SQLCMD
sqlcmd -H localhost -U sa -p 
And execute this code to create a user named dbfs_user 
CREATE LOGIN [dbfs_user]
WITH PASSWORD=N'ThisSureIsntSecure',
Once created, let’s assign this user permissions to query DMVs
The next step is we need to create a directory where dbfs will place all the files representing the DMVs we wish to query
mkdir /tmp/dbfs  
Now, let’s go ahead and configure dbfs. I’m going to place it’s configuration file in /etc/ since that’s the standard location for configuration files on Linux systems.
sudo vi /etc/dbfs.config
And inside that file, let’s use the following configuration. Pretty straight forward. Define a configuration name, here you see server1, the hostname which is the locally installed SQL instance. We’ll use the username and password of the user we just created and also defined is a version. While this isn’t very well documented, the code here shows that if you’re on version 16 (SQL Server 2016) or newer it will create files dbfs files with a .json file extension which exposes your DMV data as…you guessed it JSON. Also if you want to add a second server to dbfs, just repeat the configuration inside the same text file.

Running dbfs

Now with all the preliminaries out of the way, let’s launch dbfs. Basic syntax here, the actual program name with the parameter -c pointing to the configuration file we just created and the -m parameter pointing to the directory we want to “mount” our DMVs into.
dbfs -c /etc/dbfs.config -m /tmp/dbfs/ 
Now, what’s interesting about dbfs is if you log out dbfs stays running. Honestly, I don’t like that, if this is the case it should be running as a service managed by systemd or whatever init daemon you’re using on your Linux distribution. I mentioned that on their GitHub repo. If this is going to be a user process, then I should have the choice the background the task myself.

Using dbfs

Looking at the source for dbfs it gets a list of all DMVs from sys.system_views from the SQL Server you configured it to connect to, then creates a file for each and every one of those DMVs. So we have full coverage of all the DMVs available to us and since you can use any bash command line fu to access the data now…the options are really limitless. Microsoft has a few good demos on the GitHub repo here. Let’s walk through a few examples now.
Accessing a DMV

This is pretty straight forward, you read from the file just like you would read from any other file on a Linux system. So let’s do that…we add the column -t option to make sure all the columns are aligned in the output.
cat dm_exec_connections | column -t

And our output looks like this…

session_id  most_recent_session_id  connect_time  net_transport  protocol_type  protocol_version  endpoint_id  encrypt_option  auth_scheme  node_affinity  num_reads  num_writes  last_read  last_write  net_packet_size  client_net_address  client_tcp_port  local_net_address  local_tcp_port  connection_id  parent_connection_id  most_recent_sql_handle
51          51                      Jun           19             2017           09:46:33:660AM    TCP          TSQL            1946157060   4              FALSE      NTLM        0          7           7                Jun                 19               2017               09:46:34:103AM  Jun            19                    2017                    09:46:34:107AM  8000  37641  1433  EDC82F4B-D333-4DCA-88BB-4AB2CE9  02000000a0c09f36765d6d3b8a15a90772d74e103ac8b653000000000000000
Notice in the output above how the connect_time column is split incorrectly? We need to tell column to use the tab as a delimiter. By default it uses whitespaces. So let’s do that…
cat dm_exec_connections  | column -t -s $'\t'
And now our output looks much better
session_id  most_recent_session_id  connect_time                net_transport  protocol_type  protocol_version  endpoint_id  encrypt_option  auth_scheme  node_affinity  num_reads  num_writes  last_read                   last_write                  net_packet_size  client_net_address  client_tcp_port  local_net_address  local_tcp_port  connection_id                    parent_connection_id  most_recent_sql_handle
51          51                      Jun 19 2017 09:29:36:340PM  TCP            TSQL           1895825409        4            FALSE           SQL          0              7          7           Jun 19 2017 09:29:36:347PM  Jun 19 2017 09:29:36:350PM  4096              42784            1433            EDFB041F-B319-4098-B4DE-80739A7                        01000100f0e88f076013d837050000000000000000000000000000000000000

Selecting off a subset of columns

Well you probably noticed that the output is a bit unruly since it’s outputting all of the DMV’s columns. So let’s tame that a bit and pull out particular columns. To do that we’ll use a tool called awk which will print out columns based on the numeric index, so $1 is the first column and so on. 
cat dm_exec_connections | awk '{ print $1,$3,$4,$7 }'
And our output looks like this
session_id connect_time net_transport endpoint_id
51 Jun 19 TCP
Something isn’t right…as DBAs we think of things in rows and columns. So we’re going to count across the top and think the 7th column is going to yield the 7th column and it’s data for each row, right? Well, it will but data processed by awk is whitespace delimited by default and is processed row by row. So the 7th column in the second line isn’t the same as the output in the first line. This can be really frustrating if your row data has spaces in it…like you know…dates.
So let’s fix that…the output from the DMVs via dbfs is tab delimited. We can define our delimiter for awk with -F which will allow for whitespaces in our data. Breaking the data only on the tabs. Let’s hope there isn’t any tabs in our data!
cat dm_exec_connections | awk -F $'\t' '{ print $1,$3,$4,$7 }'
And the output from that looks like this, much better but we don’t have the nice columns.
session_id  connect_time  net_transport  endpoint_id
51          Jun           19             2017         02:29:36:340PM  TCP  4
We’re so close, we can’t throw column on the end to make this nice and columnar because awk with this configuration it will remove the tab delimiters on it’s output stream. column by default will do the same thing too, but we can let column do the work for us and have it print tab delimiters in it’s output stream. 
cat dm_exec_connections | column -t -s $'\t' -o $'\t' | awk -F $'\t' '{ print $1,$3,$4,$7 }'
And voila, we end up with some nice neatly formatted output
session_id connect_time               net_transport endpoint_id
51         Jun 19 2017 02:29:36:340PM TCP           4          

Searching in Text

We can search for text in the output using grep, here’s a quick example looking for the dedicated admin connection in dm_os_schedulers
grep DAC dm_os_schedulers
And here’s the output. 
00000005391c0040	64	1048576	0	VISIBLE ONLINE (DAC)	1	1	1	3	5	2	940180	0	00000005392aa160	000000053906e040	0000000539070040	4000	44	0	0

SQL folks…keep in mind, grep will only output lines matched, so we loose the column headers here since they’re part of the standard output stream when accessing the file/DMV data.

Moving forward with dbfs

We need the ability to execute more complex queries from the command line. Vin Yu mentions this here. As DBAs we already have our scripts that we use day to day to help us access, and more importantly make sense of, the data in the DMVs. So dbfs should allow us to execute those scripts somehow. I’m thinking we can have it read a folder on the local Linux system at runtime, create files for those scripts and throw them in the mounted directory and allow them to be accesses like any of the other DMVs. The other option is we place those scripts as views on the server and access them via dbfs. Pros and cons either way. Since it’s open source…I’m thinking about implementing this myself :)

Next is, somehow we need the ability to maintain column context throughout the output stream, for DBAs it’s going to be tough sell having to deal with that. I know JSON is available, but we’re talking about DBAs and sysadmins here as a target audience. 

In closing is a great step forward…giving access into the DMVs from the command line opens up SQL Server to a set of people who are used to accessing performance data this way. Bravo! 

PowerShell Remoting in Multi-Platform Environments – Use Cases

In our previous post we discussed how to implement OpenSSH (the plumbing) as the transport layer for PowerShell remoting. In this post, we’re going to leverage that configuration and look at some common remoting use cases. This is one of the core things I use everyday when I work with PowerShell. Remoting gives me the ability to administer scale up and administer large collections of systems. So like I said in my very first post about my PowerShell journey, it’s become a part of my every day life and techniques like this are at the core of how I use PowerShell. So let’s get started…

We’re going to look at the following

  • Entering a remote command line interface on a server
  • Using sessions
  • Executing commands against one remote system
  • Executing commands on a collection of remote systems

This is going to be awesomeness…Yep, I said awesomeness, when I’m finished you’ll agree that’s about the only way to describe this.

I do want to point out that we’re using Beta software here, things change. The version of PowerShell I’m using here is Version 6 Beta 2.

Remote Command Line Interface

Up first, let’s cover the simplest remoting case, where we use remoting to get a command line interface to a remote system.

Enter-PSSession -HostName server1.demo.local

Nothing special here, simple syntax, but the seasoned PowerShell remoting pro will notice that we’re using a new parameter here -HostName. Normally on Windows PowerShell you have the -ComputerName parameter. Now, I don’t know exactly why this is different, but perhaps the PowerShell team needed a way to differentiate between OpenSSH and WinRM based remoting. Further, Enter-PSSession now has a new parameter -SSHTransport which at the moment doesn’t seem to do much since remoting cmdlets currently use OpenSSH by default. But if you read the code comments here, it looks like WinRM will be the default and we can use this switch parameter to specify SSH as the transport.

Once we execute this command, you’ll have a command prompt to the system that passed as a parameter to -HostName. The prompt below indicates you’re on a remote system by putting the server name you’re connected to in square brackets then your normal PowerShell prompt. That’s it, you now have a remote shell. Time to get some work done on that server, eh? Want to get out of the session, just type exit.

[server1.demo.local]: PS /home/aen>

Using Sessions

So, in PowerShell, when you exit from a remoting session your session goes away. So any work, variables or program state you had will disappear. But what if we wanted to keep our session’s state, log out and log back into that session again? We can leverage sessions to help us persist our remote sessions. We can create a session connect and disconnect from it at will. So let’s check that out.

We create a new session by calling New-PSSession. This will start up the remoting session just like we did with Enter-PSSession, via SSH, but we won’t attach to the remote terminal. The output we do get from New-PSSession shows us the details of the session created. We use Get-PSSession to get a list of all current sessions.

PS /Users/aen> New-PSSession -HostName server1.demo.local -UserName aen

 Id Name            ComputerName    ComputerType    State         ConfigurationName     Availability
 -- ----            ------------    ------------    -----         -----------------     ------------
  2 SSH2            server1.demo... RemoteMachine   Opened        DefaultShell             Available
We can connect to an existing session with Enter-PSSession. If there are multiple entries in your session list…just use the correct Id from the session list to identify the session you want to connect to.
PS /Users/aen> Enter-PSSession -Id 2
Now, when you exit from this session with the exit command, your session will persist. Let’s use Get-PSSession to see.
PS /Users/aen> Get-PSSession

 Id Name            ComputerName    ComputerType    State         ConfigurationName     Availability
 -- ----            ------------    ------------    -----         -----------------     ------------
  2 SSH2            server1.demo... RemoteMachine   Opened        DefaultShell             Available
Cool, our session is still there, let’s reuse it. This time we’re going to assign it to a variable this time. This will keep our command line syntax simple in the upcoming examples.
PS /Users/aen> $s = Get-PSSession -Id 2

Executing Commands Against One Remote System

We can use PowerShell remoting to execute a command against a remote system, so let’s look at the simplest case…just one command. Here you can see, we’re using the Invoke-Command cmdlet to execute the Get-Process command on the remote system and it’s output returns to our console. Here we have the top 5 processes by CPU on the remote system.
PS /Users/aen> Invoke-Command -Session $s { Get-Process | Sort-Object -Descending CPU -Top 5 }

 NPM(K)    PM(M)      WS(M)     CPU(s)     Id  SI ProcessName        PSComputerName
 ------    -----      -----     ------     --  -- -----------        --------------
      0     0.00     761.55   2,805.86  14770 704 sqlservr           server1.demo.local
      0     0.00       8.37     183.29  13480 479 vmtoolsd           server1.demo.local
      0     0.00       7.24      65.31  13553 550 ManagementAgent    server1.demo.local
      0     0.00       0.00      61.74    401   0 xfsaild/dm-0       server1.demo.local

Executing Commands Against a Collection of Remote Systems

Now, let’s add one more session to our list of sessions. With a new session to server2.
PS /Users/aen> New-PSSession -HostName server2.demo.local -UserName aen

 Id Name            ComputerName    ComputerType    State         ConfigurationName     Availability
 -- ----            ------------    ------------    -----         -----------------     ------------
  3 SSH3            server2.demo... RemoteMachine   Opened        DefaultShell             Available
New-PSSession just returns the session created, so let’s get a list of all of the sessions with Get-PSSession. There you can see our two remoting sessions are currently opened the new session has a new Id.
PS /Users/aen> Get-PSSession

 Id Name            ComputerName    ComputerType    State         ConfigurationName     Availability
 -- ----            ------------    ------------    -----         -----------------     ------------
  2 SSH2            server1.demo... RemoteMachine   Opened        DefaultShell             Available
  3 SSH3            server2.demo... RemoteMachine   Opened        DefaultShell             Available
This time, let’s take both our sessions and save them to a variable. We’re going to reuse $s.
PS /Users/aen> $s = Get-PSSession
Now to the amazing part…let’s run a command against a collection of remote systems.
PS /Users/aen> Invoke-Command -Session $s { Get-Process | Sort-Object -Descending CPU -Top 5 }

 NPM(K)    PM(M)      WS(M)     CPU(s)     Id  SI ProcessName                      PSComputerName
 ------    -----      -----     ------     --  -- -----------                      --------------
      0     0.00       0.57       5.09    666 666 rngd                             server2.demo.local
      0     0.00      93.82       2.03  24218 218 powershell                       server2.demo.local
      0     0.00       6.57       1.78      1   1 systemd                          server2.demo.local
      0     0.00       8.37       0.91  13475 474 vmtoolsd                         server2.demo.local
      0     0.00       0.00       0.89     94   0 kworker/0:2                      server2.demo.local
      0     0.00     761.41   2,810.99  14770 704 sqlservr                         server1.demo.local
      0     0.00       8.37     183.69  13480 479 vmtoolsd                         server1.demo.local
      0     0.00       7.24      65.48  13553 550 ManagementAgent                  server1.demo.local
      0     0.00       0.00      61.88    401   0 xfsaild/dm-0                     server1.demo.local
      0     0.00       1.69      58.19  13749 749 crond                            server1.demo.local
This example brings up a very interesting point in PowerShell remoting, everything inside the curly braces of the Invoke-Command call happens on the remote system. This means, all the sorting and processing is remote, the remote systems deal with all the computational aspects and less data has to traverse the network. There’s a deeper topic of serialized objects we’ll cover in a later post. Now the output from these systems comes back to your console as fast as it comes back, so there’s really no order here. So you might get a list of data that’s all commingled. But we can control that by sorting locally. Let’s look at that.
Now, let’s say you want to get the top 5 processes across all the systems in your sessions list, easy enough.
PS /Users/aen> Invoke-Command -Session $s { Get-Process } | Sort-Object -Descending CPU -Top 5

 NPM(K)    PM(M)      WS(M)     CPU(s)     Id  SI ProcessName                      PSComputerName
 ------    -----      -----     ------     --  -- -----------                      --------------
      0     0.00     761.41   2,814.59  14770 704 sqlservr                         server1.demo.local
      0     0.00       8.37     183.95  13480 479 vmtoolsd                         server1.demo.local
      0     0.00       7.25      65.59  13553 550 ManagementAgent                  server1.demo.local
      0     0.00       0.00      61.96    401   0 xfsaild/dm-0                     server1.demo.local
      0     0.00      94.83      61.93  24464 393 powershell                       server2.demo.local

We move the sorting to the pipeline on our local system, notice the Invoke-Command call is going to return the entire process list from all machines, then it will sort the data locally and output to the local console. So you can see in this list here we have the top 5 processes across our two systems. Imagine you had a web farm of servers and you needed to chase down a bad process fast, this would be useful, right?

When we’re all finished, you’ll want to clean up your sessions. We can do that a by passing the $s variable into Remove-PSSession

PS /Users/aen> $s | Remove-PSSession

And that’s it, so like I said in my very first post about my PowerShell journey, it’s become a part of my every day life and techniques like this are at the core of how I use PowerShell.