Author Archives: Anthony Nocentino

Speaking at SQL Saturday Pensacola!

I’m proud to announce that I will be speaking at SQL Saturday Pensacola on June 3rd 2017! 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 Designing High Availability Database Systems using AlwaysOn Availability Groups” 

Abstract:

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.

Learning Objectives: 

This session highlights the importance of doing thorough design work up front. Attendees will learn core concepts needed for successful Availability Group based systems. This includes, recovery objectives, replica placement, failover requirements, synchronization models, quorum, backup and recovery and monitoring. From this session attendees will have a firm footing on where to start when they start designing their AlwaysOn Availability Group based systems.

PowerShell Remoting in Multi-Platform Environments using OpenSSH

So in my last post I told you about how I started my journey on learning PowerShell, let’s keep going down that path together. In this post I’m going to introduce PowerShell Remoting in Multi-Platform Environments, specifically using OpenSSH. We’ll discuss WinRM in multi-platform systems in an upcoming post.

Have you ever had to execute a command against one system or a collection of systems? Have you ever wanted a remote shell on a Windows system? Using Remoting you can you can do all of these things, very, very easily.

The Plumbing

So for our first stop, let’s talk about how remoting works. Essentially what’s occurring under the hood is a PowerShell process on one system is sending commands to a PowerShell process on another system then the remote system sends the output back. Easy enough, right? Let’s dig into to how it exchanges information.

The PowerShell processes exchange messaged defined in the PowerShell Remoting Protocol (PSRP). PSRP defines semantics of the communication and types of message being exchanged. Now these messages need a transport layer, for that we have WinRM and OpenSSH. Let’s look at both.

By default, Windows PowerShell systems Remoting connections communicate using WinRM which uses HTTP/S endpoints. WinRM is an implementation of the WS-Management industry standard. WS-Man defines the semantics of the interactions between the systems and data transfer. PowerShell encapsulates its PSRP messages and uses WinRM to transport them between systems.

On Linux systems, well…they don’t communicate with WinRM, by default, so we need something else…that’s where OpenSSH comes in. PowerShell encapsulates it’s inputs and outputs as PSRP messages and it uses SSH to securely exchange the messages between systems.

The PowerShell team has implemented WinRM over HTTP remoting on Linux and already has a port of OpenSSH on Windows. The key idea going forward is…we will have a choice, we can use WinRM (HTTPS) or we can use SSH regardless of OS. For the rest of this post, we’re going to talk remoting over OpenSSH, even on the Windows side of things. We’ll discuss WinRM in multi-platform systems in an upcoming post.

The Installation

For starters, go ahead and install PowerShell Core your systems if you haven’t already, its pretty straightforward check out this link here.

Next, on Windows we need to install OpenSSH and the PowerShell team has developed a Win32 port of OpenSSH. So go check that out here. I’m going to be honest, the installation isn’t easy. But I’m going to let you in on a secret, Darwin Savoy wrote a killer Chocolatey package that does the hard work for you. Check that out here. You’ll want to use the following syntax to install OpenSSH as a service.

If you’re on Windows verify OpenSSH is up and running

Now on Linux, just about every distribution includes an OpenSSH installation their default setup…so we’re not going to cover that here.

The Configuration

Wether it’s Windows or Linux, we need to tell OpenSSH about PowerShell and we can do that with a subsystem configuration.

On a Windows system we’ll need a line like this in SSH daemon configuration file C:\Program Files\OpenSSH\sshd_config. Find the subsystem section and add this line.

On a Linux system we’ll need a line like this one in /etc/ssh/sshd_config. Find the subsystem section and add this line.

Basically what’s going on here is we’re telling OpenSSH to invoke a PowerShell process when it receives a message from a remote system that’s says it’s going to use the PowerShell subsystem. So SSH is just the conduit for these two PowerShell processes to communicate their inputs and outputs.

When you’re finished editing these files, you’ll want to restart the SSH daemons on your system…Windows or Linux.

On Windows that’s Restart-Service -name sshd on Linux that’s systemctl restart sshd

Now here’s the thing…before you move on…test your SSH connections. You will absolutely go crazy if there’s an issue with your SSH configuration and you try using PowerShell Remoting right away. If there’s an issue, PowerShell will happily swallow OpenSSH’s errors and you’ll get no feedback. Simply grab your favorite SSH client and try to connect to the systems right now and verify you can connect and get a command line. Things will be a lot easier for you before you try to use remoting.

Up next in this series we’ll look at some remoting use cases, specifically using a command line interface, executing commands against remote a remote system and executing commands against a collection of remote systems.

Why PowerShell?

Why do I use PowerShell?

Well, here’s a little back story…last year I was involved in a Pluralsight Play by Play with Jason Helmick and Jeffrey Snover for launch of Open PowerShell on Linux and Mac. Before this video, I didn’t take PowerShell seriously. Basically, if I Google’d a problem and found a solution in PowerShell I would grind my teeth and copy and paste the text into the foreign blue console and cross my fingers.

Fast forward a bit to PowerShell Summit this year, Jason and I were slated to do a session together on…PowerShell on Linux. I had to miss the trip to Seattle this year, but during the session Jason told a story a story about me learning PowerShell…check it out here around 28:27. Its pretty funny. 

Pipelines similar but different

If you don’t want to listen to Jason’s story, it goes like this. I needed to learn PowerShell…FAST. I had only a few weeks to build my skills enough so that I could sit at a table with the inventor of PowerShell and an industry recognized expert and have a meaningful conversation and oh yea, record a 5 hour training video. So as Jason says I “did the Don Jones/Jeff Hicks thing” (there’s a newer version available now) .

Well, having a UNIX background I understood the concepts of a pipeline. UNIX processes have had pipelines for a long time. Byte streams can move data from one command to the next allowing you to build more complicated commands. PowerShell can do the same, but it moves objects…rather than text/byte streams. 

Well, while learning PowerShell I built a PowerShell cmdlet pipeline to get the top 10 processes on a system sorted by CPU.

And to do the same in bash, we need to do something like this…

So, I worked through these examples and I fired up my email client and sent an email to Jason and said hey Jason…here’s two commands that do the same thing. One in PowerShell and one in native bash commands. I asked…hey, which one do you think I like better? 

As we learned in the PowerShell Summit video, he expected me to answer with my crotchety, suspenders wearing UNIX guy answer…well he was in for a surprise. I told him I liked the first one better…the PowerShell version.

I actually don't own suspenders...neckbeards are another story

Now hear me out, I’ve been using Linux/UNIX since 1997, I manage large internet commerce sites through the holiday season, I’ve done PhD level research on topics like IO, CPU scheduling and memory management…literally my fingers can type the bash commands to get the top 10 processes without even thinking…muscle memory to the max. BUT, the PowerShell version of this literally reads like a sentence. Get-Process, Sort-Object, Select-Object, pretty straight forward stuff here. No surprises. the commands do exactly what they say. This means, I can put stuff like this in a script, and other people can read it without 20 years of experience. 

What’s next?

Well, since the Play By Play, PowerShell isn’t the Google the answer…copy/paste solution anymore for me…I’ve decided to really take this seriously and PowerShell is now a go to tool in my toolbox. I’m not telling you this because I’m a Microsoft MVP, I’m not telling you this because I did a video with THE PowerShell gurus…I’m telling you this because I really use PowerShell now…every day.

This post is the first post in a series I plan on bring to you that will document my discovery process of using PowerShell on Linux. We’ll discuss the techniques and technologies I use to solve some real world problems in a multi-platform world!

Now, there’s two things I really consider special about my first go with PowerShell…asynchronous job posting via Remoting and Desired State Configuration (DSC). We’ll cover these topics in this series. 

Why Did Your Availability Group Creation Fail?

Availability Groups are a fantastic way to provide high availability and disaster recovery for your databases, but it isn’t exactly the easiest thing in the world to pull off correctly. To do it right there’s a lot of planning and effort that goes into your Availability Group topology. The funny thing about AGs is as hard as they are to plan…they’re pretty easy to implement…but sometimes things can go wrong. In this post I’m going to show you how to look into things when creating your AGs fails.

When working at a customer site today I encountered and error that I haven’t seen before when creating an Availability Group. So I’m going to walk you through what happened and how I fixed it. So if your AGs fail at creation, you can follow this process to dig into why.

First, let’s try to create our Availability Group

But, that fails and we get this error…it tells me what happened and to go look in the SQL Server error log for more details.

OK, so let’s look in the SQL Server error Log and see what we find.

Clearly something is up, the AG tried to come online but couldn’t.

The error here say check out the Windows Server Failover Clustering log…so let’s go ahead and do that. But that’s not as straightforward as you think. WSFC does write to the event log, but the errors are pretty generic for this issue. Here’s what you’ll see in the System Event Log and the Cluster Events section in the Failover Cluster Manager

Wow, that’s informative, right? Luckily we still have more information to look into.

Let’s dig deeper with using the WSFC cluster logs

The cluster logs need to be queried, they’re not readily available as text for us. We can write them out to file with this PowerShell cmdlet Get-ClusterLogs. Let’s make a directory and dump the logs into there.

Now we have some data to look through!

When we look at the contents of the cluster logs files generates by Get-ClusterLogs, we’re totally on the other side of the spectrum when it comes to information verbosity. The logs so far have been pretty terse and haven’t really told us about what’s causing the failure…well dig through this log and you’ll likely find your reason and a lot more information. Good stuff to look at to get an understanding of the internals of WSFCs. Now for the the reason my Availability Group creation failed was permissions. Check out the log entries.

Well that’s pretty clear about what’s going on…the process creating the AG couldn’t connect to SQL Server to run the very important sp_server_diagnostics stored procedure. A quick internet search to find a fix yielded this article from Mike Fal (b | t) which points to this Microsoft article detailing the issue and fix.

For those that don’t want to click the links here’s the code to adjust the permissions and allow your Availability Group to create.

So to review…here’s how we found our issue. 

  1. Read the error the create script gives you
  2. Read the SQL Server error log
  3. Look at your System Event log
  4. Dump your Cluster Logs and review

Use this technique if you find yourself in a situation where your AG won’t come online or worse…fails over unexpectedly or won’t come back online. 

New Pluralsight Course – LFCE: Network and Host Security

My new course “LFCE: Network and Host Security” 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 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.

If you’re in the SQL Server community and want to learn how Linux secure your Linux systems…this course is for you too! You have heard that Microsoft has SQL Server for Linux now, right, if not…read this!

The modules of the course are:

  • Linux Security Concept and Architectures – Introduction you into the fundamental concepts needed for securing your environment
  • Securing Hosts and Services – iptables and TCP Wrappers – Host based firewall concepts and techniques with iptables and TCP Wrappers
  • Securing Hosts and Services – firewalld – Learn leverage firewalld to develop more complex firewalls systems…simply. Including concepts such zones, service, ports and NAT
  • Remote Access – OpenSSH – We’ll look at encryption, authentication and how to configure SSH for public authentication
  • Remote Access – Tools and Techniques  – SSH is more than just remote access, we’ll look at secure copy, tunneling and how to use windowing systems such as X11 and VNC…securely.

Pluralsight Redhat Linux

Check out the course at Pluralsight!

Speaking at PowerShell Summit!

Speaking at PowerShell + DevOps Global Summit 2017!

I’m proud to announce that I will be speaking at PowerShell + DevOps Global Summit 2017 on the conference runs from April 9th 2017 through April 12th 2017. This is an incredible event packed with fantastic content and speakers. Check out the amazing schedule!

This year I have two sessions!

On Tuesday, April 10th at 10:00AM – My session is with none other the Jason Helmick. Our session is “Cross platform Management – Windows/Linux

Here’s the abstract

Let Jason Helmick and Anthony Nocentino take you through a fun filled, demo heavy adventure of how Windows and Linux admins can work together managing a heterogeneous environment. You will learn all you need to know from both sides of the aisle to get started!

On Wednesday, April 11th at 10:00AM – I’m presenting solo on “Linux Fundamentals for the PowerShell Expert

Here’s the abtract

PowerShell is now available on Linux and your management wants you to leverage this shift in technology to more effectively manage your systems, but you’re a Windows guy! Don’t fear, iIt’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 In this session we’ll cover the following – Process control – Service control – Package installation – Configuration management – System resource management (CPU, disk and memory) – Using PowerShell to interact with Linux systems

PowerShell Summit

 

Using dbatools for automated restore and CHECKDB

OK, so if you haven’t heard of the dbatools.io project run by Chrissy LeMaire and company…you’ve likely been living under a rock. I strongly encourage you to check it out ASAP. What they’re doing will make your life as a DBA easier…immediately. Here’s an example…

One of the things I like to do as a DBA is backup my databases, restore them to another server and run CHECKDB on them. There are some cmdlets in the dbatools project, in particular the Snowball release, that really make this easy. In this post I’m going to outline a quick solution I had to throw together this week to help me achieve this goal. We’ve all likely written code to do this using any number of technologies and techniques…wait until you see how easy it is using the dbatools project.

Requirements

  1. Automation – Complete autopilot, no human interaction.
  2. Report job status – Accurate reporting in the event the job failed, the CHECKDB failed or the restore failed.

Solution

  1. Use dbaltools cmdlets for restore and CHECKDB operations
  2. Use SQL Agent Job automation, logging and alerting

So let’s walk through this implementation together.

Up first, here’s the PowerShell script used to restore and CHECKDB the database. Save this code into a file named restore_databses.ps1

Let’s what through what’s going on here. First the line with $ErrorActionPreference = “Stop” that’s crucial because it will tell our script to stop when it encounters and error. Yes, that’s what I want. The job stops and the error from the cmdlets will reach the SQL Agent job we have driving the process. Using this, the job will fail, and I’ll have a nice log telling me exactly what happened.

Next we have some variables set, including the backup path and the location of the data and log files on the destination system.

Now, here’s the Restore-DbaDatabase cmdlet from the dbatools project, this cmdlet will traverse the backup path defined in -Path parameter, find all the backups and build the restore sequence for you. Yes…really! If you don’t define a parameter defining a point in time it will build a restore sequence using the most recent backups available in the share. The next few parameters define the destination data and log directories and tell the restore to overwrite the database if the database exists on the destination server. That next parameter tells the job to ignore using log backups. This is sufficient in my implementation because I’m running full backups daily, I don’t need the point in time recovery. You might, so give it a try. CHECKDB can take a long time…the final parameter, tells Invoke-SqlCmd2 not to timeout while running its query.

Now, I need to run some T-SQL to clean up the databases, for example, I change the recovery model, then shrink the log. This is so I don’t have a bunch of production sized log files laying around on the destination system I do this after each restore, this way I can save a little space. And finally, I run CHECKDB against the database.

If you want to do this for more than one database, you could easily parameterize this code and drive the process with a loop. You’re creative…give it a try.

Now, I take all this and wrap it up in a SQL Agent job.

SQL Agent Job Step

 Figure 1: SQL Agent Job Step Definition

Using a SQL Agent job, we get automation, reporting and alerting. I’ll know average run times, if the job fails and have a log of why and it sends me an email with the job’s results.

The SQL Agent job type is set to Operating system (CmdExec), rather than PowerShell. We run the job this way because we want to use the latest version of PowerShell installed on our system. In this case its version 5.1. The SQL Agent PowerShell job step on SQL 2012 I believe uses version 4 and when I used it, it wasn’t able to load the dbatools modules.

We need to ensure we install the dbatools as administrator. This way the module is available to everyone on the system, including the SQL Agent user, not just the user installing the module. Simply run a PowerShell session as administrator and use Install-Module dbatools. If you need more assistance check out this for help.

From a testing standpoint I confirmed the following things…

  1. When a restore fails, it’s logged to the SQL Agent job’s log, I get an alert.
  2. When one of the Invoke-SqlCmd2 calls fails, it’s logged to the SQL Agent job’s log and I get an alert.
  3. When CHECKDB finds a corruption in a database, it’s logged to the SQL Agent job’s log, the SQL Server Error Log and I get an alert. For testing this I used Paul Randal’s corrupt databases which he has available here.

So in this post, we discussed a solution to common DBA problem, backup, restore and CHECKDB a set of databases. Using dbatools, you can do this with a very simple solution like I described here. I like simple. Simple is easier to maintain. Certainly there are some features I want to add to this. Specifically, I’d like to write some more verbose information into the SQL Agent job’s log or use the job step’s ability to log to a file. Using those logs I can easily review the exact runtimes of each restore and CHECKDB.

Give dbatools a try. You won’t be disappointed…really go there now!

TugaIT – Pre-conference workshop on PowerShell on Linux

Where – Thursday, May 18, 2017

Where – TUGA IT – Lisbon, Portugal

Full Day Session – “Open Source PowerShell on Linux – Skills to Manage Your Heterogenous Data Center“ 

Registration Link – https://app.weventual.com/detalheEvento.action?iDEvento=4011

  • Early Bird Price – before 03/18/2017 – 150€
  • Normal Price – before 05/01/2017 – 200€
  • Late Registration – 05/18/2017 – 250€

PowerShell is now available on Linux and Mac and you want to use it to manage your multi-platform data center. In this workshop we will introduce Open Source PowerShell and learn why this is such a groundbreaking technology shift. Then we’ll get into the essentials of using PowerShell on Linux and Mac, we’ll start with installing Powershell and building PowerShell from source, work our way into using cmdlets and bash integration, building pipelines, remoting scenarios with heterogenous operating systems and discuss Desired State Configuration. 

You will learn how to

  • Set up your environment for multi-platform management
  • Bash and PowerShell scripting fundamentals
  • Building command pipelines in Bash and PowerShell
  • Toolmaking in Powershell
  • Configure remoting in multi-platform environments
  • Configuration management basics with Desired State Configuration

Topics

  • Setting up your OpenSource PowerShell environment
  • Working with PowerShell cmdlets and bash integration
  • Comparing the PowerShell pipeline and a UNIX style text-based pipeline
  • PowerShell concepts for building more general toolmaking
  • Remoting in multi-platform environments
  • Leveraging OpenSource PowerShell in your data centers with Desired State Configuration
  • What’s next and limitations

Prerequisites 

This is a fundamentals level workshop. This workshop’s intent is to introduce you to the technologies and get you started. Attendees should have basic understanding of the Windows and Linux operating systems.

Registration Link – https://app.weventual.com/detalheEvento.action?iDEvento=4011


Speaking at SQLSaturday Chicago – 600!

Speaking at SQLSaturday Chicago!

I’m proud to announce that I will be speaking at SQL Saturday Chicago on March 11th 2017! And wow, 600 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!

My presentation is Networking Internals for the SQL Server Professional” 

NewImage

Here’s the abstract for the talk

Once data leaves your SQL Server do you know what happens or is the world of networking a black box to you? Would you like to know how data is packaged up and transmitted to other systems and what to do when things go wrong?  Are  you tired of being frustrated with the network team? In this session we introduce how data moves between systems on networks and TCP/IP internals. We’ll discuss real world scenarios showing you how your network’s performance impacts the performance of your SQL Server and even your recovery objectives.

Friend of Redgate – 2017

I’m excited to announce that I have been named a Friend of Redgate for 2017. The program targets influential people in their respective technical communities such as SQL, .NET and ALM and enables us to participate in the conversation around product and community development.

As a multi-year awardee in the program I get to see first hand the continuing dedication Redgate has to the SQL community and to making great software. I met a ton of really cool, very dedicated people along the way. Thanks for the recognition and I look forward to another great year!

Redgate makes outstanding products! While I focus mainly on the DBA side of things such as SQL Monitor, SQL Backup and SQL Prompt there are many more. I’ve used these tools for years and let’s just say they’re awesome.

Redgate isn’t just software, they’re committed to community and education. Here are some of the things they do to support technical communities:

  • Online resources – SimpleTalkSQL Server Central, and books and Free eBooks. These resources aren’t marketing fluff, it’s killer content written by real experts
  • Events – hosting events, exhibiting at events and supporting user groups across the world. One word can describe this, engaged
Thank you to Redgate for this opportunity! I look forward to participating in this program, sharing my thoughts and learning as much as I can from all involved.
FoRG 2017
If you need you’d like to talk about Redgate’s products and where they fit into your SQL Server system please feel free to contact me.
 
Follow me on Twitter: @nocentino