Pulling mussels from a shell

Happy Poet’s 📜 Day!

“I’ve been too long, I’m glad to be back”

So, after a 2-week hiatus we are glad to be back and all powered⚡️up and ready to get back on our continuous journey ✈️in learning. In our most recent learning, we have spent time🕰️ in the clouds ☁️☁️ (in particular AWS) so we thought we might circle⭕ back to an oldie but goodie with PowerShell⚡️🐚.  Why PowerShell⚡️🐚 you might ask? That’s seems so yesterday. However, it’s still very much still today. Microsoft has been very much committed to PowerShell⚡️🐚 especially in regards to managing Windows. Every application from Exchange to SQL to Active Directory is built with PowerShell⚡️🐚 as a basis for administration. In addition, Cloud ☁️ solutions such as Office 365 and Azure integrate with PowerShell⚡️🐚.

When we first started with PowerShell⚡️🐚 around circa ~2012 with version 3 and then of course with version 4 which debuted just a year later it was all the rage. Just a quick a review for those not familiar with PowerShell⚡️🐚. PowerShell⚡️🐚 is a management framework that combines a command-line shell 🐚 and scripting language allowing IT professionals🧑‍💻the ability to perform real‑world🌎 tasks such as gathering remote computer 🖥️ information, automate repetitive tasks, and troubleshooting system problems. Initially, PowerShell⚡️🐚 was built for Windows only running🏃‍♂️ on top of the Windows .NET Framework but in 2016 Microsoft made the strategic decision and pivoted to offer PowerShell⚡️🐚  as Open Source through PowerShell Core running🏃‍♂️ on top of course .NET Core which is an open source framework that runs 🏃 on Linux🐧, macOS🍎 and Windows .

So where shall we begin? In the past, we have found when reviewing a familiar technology solution that the  best place to start is with the basics and we found the perfect course on Pluralsight through Michael Bender’s PowerShell: Getting Started. Michael’s excellent course provides a strong fundamental knowledge of PowerShell⚡️🐚 with a personal guarantee that upon completion of the course you will be able to “hit the ground running 🏃 with usable PowerShell⚡️🐚 ninja🤺 skills.”

In the course introduction, Michael succinctly discusses why PowerShell⚡️🐚 is more than a scripting language and a command line interface but an execution engine that provides the ability for you to interface with your environment using a variety of tools 🔧. First, he touches on the traditional Windows PowerShell console that allows us to run many different commands i.e. Get‑Service. Then he dives into PowerShell Core which pretty much lets you do much of the same things but with the flexibility to run across multiple platforms. Then he discusses the tools🔧 we can use to developed PowerShell⚡️🐚 scripts with the legacy Windows PowerShell⚡️🐚 ISE (Integrated Scripting Engine) and the newer Visual Studio Code. Next, he discusses Windows Admin Center which uses PowerShell⚡️🐚 on the back-end to do all of the administration on Windows Server and also integrates with Microsoft Azure☁️.

Now after a great introduction, we were ready to dive right into the PowerShell⚡️🐚 Basics. At a very basic level with PowerShell⚡️🐚 follows a Verb‑Noun syntax. In other words, you do something to something.  For example, if you wanted to get information about the verbs that are available for use in PowerShell⚡️🐚. You would use a cmdlet called “Get‑Verb”. Another important piece to PowerShell⚡️🐚 commands is parameter(s). Parameters are used to pass information into PowerShell⚡️🐚 commands so that we have information that we can use for the command to use to do its work.

Ex:  Get‑Service ComputerName

Next, Michael introduces us to three of the most important commands that everyone should know which of course is Get‑Command, Get‑Help, and Get‑Member. These commands are so significant because they allow you to find the answers you need to work within PowerShell⚡️🐚. So, there is no need to Google or Bing to find the right syntax. Everything is right there in the console. Also, it’s really the best way to learn how to do something within PowerShell⚡️🐚.

·         Get‑Command is used to search for the installed commands within PowerShell⚡️🐚.

·         Get‑Help allows us to see how we use a specific command that we found so it displays the help information.

·         Get‑Member allows us to get the properties and methods of objects that are the output of a specific command

PowerShell⚡️🐚 is an object‑oriented language. Unlike other scripting languages that rely on syntax to get things done, PowerShell⚡️🐚 uses objects as its output and objects have properties that make them up and they have methods that you can perform actions against them. The best way to visualize objects in PowerShell⚡️🐚 is to view the data in a table format. PowerShell⚡️🐚 places all of the data from commands into a collection or a table to store that data.

Next, we took a deep dive with pipeline in PowerShell⚡️🐚 which is really where the real power in PowerShell⚡️🐚 comes in. PowerShell⚡️🐚 treats all data as objects that can be used to their full potential in PowerShell⚡️🐚. So pipe-lining PowerShell⚡️🐚 is a way to send the output of one command into a second command, and this allows you to do more complex work like sort or filter something, and then from that output, we can do something.

Ex: Get-Service | where {$_.Status -eq “Stopped”}

Then the course further discusses one of the most common use cases for PowerShell⚡️🐚 which is gathering system information. In PowerShell⚡️🐚 there are several options. The first option is Windows Management Instrumentation (WMI). WMI is based on the Common Information Model, an open standard that defines how managed elements in an IT environment are represented as a common set of objects, as well as the relationship between them.  The second option is CIM. CIM was originally introduced in PowerShell⚡️🐚 v3 to work with WMI.  CIM cmdlets are now the de facto standard and the WMI cmdlets are now considered legacy as there has been no recent development or enhancements to WMI. When WMI Cmdlets are being called information is accessed through namespaces in the WMI repository. CIM is namespace that for the specifics classes that we’re looking for. An example of a classes is Win32_Processor which contains information like device ID and name for our processors. This information is stored as properties that are accessible from the objects output by the command.

Now that we are comfortable using PowerShell⚡️🐚 on local systems, Michael now discusses how we can use PowerShell⚡️🐚 to connect remote systems which is generally how PowerShell⚡️🐚 is used as much of our troubleshooting and problem resolution happens remotely. PowerShell⚡️🐚 has a few options for remoting, WMI and Windows Remote management (WinRM).  WinRM is a Web Service for Management Protocol (WS Man) which allows users to run PowerShell⚡️🐚 commands on remote computers 🖥️. Both options are available on Windows PowerShell and PowerShell Core. PowerShell⚡️🐚 remoting allows you to send commands to a remote machine on your network. WinRM is responsible for maintaining the connections between these two systems. The computers 🖥️ you want to connect to need must have listener set up so that WinRM knows to listen for the power shell connections. By default, Windows clients don’t have PowerShell⚡️🐚 remoting turned on. So, it must be enabled if you plan to use PowerShell⚡️🐚 for remote Administration.

Enable-PSRemoting needs to be enabled on the target machine

Next, you need to give the user access to PowerShell⚡️🐚  remoting Enter-PSSession

This allows you to modify the session permissions and this will allow the remote connection to happen for PowerShell core. You need to set up the remote system to be an endpoint for power shell connections. This is done by installing a script located in the PS Home Directory

To put the finishing touches on this excellent introductory course Michael walks us through on how to Build a User Inventory Script with PowerShell⚡️🐚

By default, PowerShell⚡️🐚 ‘s execution policy is set to Restricted; which means that scripts will not run.

The Set-ExecutionPolicy cmdlet enables you to determine which Windows PowerShell⚡️🐚 scripts will be allowed to run on your computer.

Windows PowerShell⚡️🐚 has four different execution policies:

• Restricted – No scripts can be run. Windows PowerShell⚡️🐚 can be used only in interactive mode.
• AllSigned – Only scripts signed by a trusted publisher can be run.
• RemoteSigned – Downloaded scripts must be signed by a trusted publisher before they can be run.
• Unrestricted – No restrictions; all scripts can be run.

After completing this amazing course, we are now armed with foundational knowledge on how to use PowerShell⚡️🐚. So,next we decided to continue our review of PowerShell⚡️🐚 and how we can utilize it with SQL Server to perform common DBA and Developer tasks. Once again, we turned to Pluralsight through SQL Server MVP Robert C. Cain fantastic course on PowerShell and SQL Server

Robert’s course provides the fundamentals of using PowerShell⚡️🐚 to manage SQL Servers. The course is designed as a six-part series covering basic DBA Tasks using just PowerShell⚡️🐚, an introduction to SQL Management Objects (SMO) and the SQL Provider, Basic DBA tasks using both SMO and SQL Provider, Development using SQL Provider, Development using SMO, and Real-World🌎 Examples, SQL PS, PowerShell⚡️🐚  Jobs

After providing us with a simple PowerShell⚡️🐚 email function “Send Easy Mail” that can be used with SQL Server notifications or alerts we take a look how to manage the SQL Server Services which we obviously find status of their services through “Get-Service” cmdlet. When using the Get-Service cmdlet you need to pass in several parameters.

Get-service -ComputerName $server | where {($_.name -like “MSSQL$*” -or $_.name -like “MSSQLSERVER” -or $_.name -like “SQL Server (*”)}

Next, we took a look at counters. As Database professionals many of us keep a customized list of counters to monitor our database environment. PowerShell⚡️🐚 makes it extremely easy to get counter information using the Get-Counter cmdlet.

A great example on we can use counters with PowerShell⚡️🐚 can be found on MSSQL Tips. WMI which we learned earlier stands for Windows Management Instrumentation.  WMI and PowerShell⚡️🐚 can be used for a common task like monitoring how much disk space you have available on the system. 

Another great example can be found on MSSQL Tips

PowerShell⚡️🐚 also makes it very easy to get information out of the event log using Get-EventLog. 

See MSSQL Tips

Next, Robert introduces us to SQL Provider and the SQL Management Object (SMO). In order to use SMO effectively you will need to have a basic understanding of objects most of which received during Michael’s course

Just to briefly review, objects are based on classes, classes are generally referred to as the blueprint for creating an object. In PowerShell⚡️🐚, you would have instantiated an instance of that class. Instantiation refers to the process of creating a single object from the class. An instance in object terms refers to that particular object which you just instantiated. As we know, objects have properties. These properties hold information about the current state of an object. Objects can also have methods and method is similar to how stored procedure works. In other words, it will only work inside of its current database. Whenever you organize a group of objects together it’s known as a collection. For example, a database object would have a collection of table objects.  Another important concept to grasp is objects can contain other objects and collections.

SMO is a set of. NET libraries specifically designed for working with SQL Server. These libraries stored in. NET DLLs are loaded with classes. From these classes you can create objects that parallel things in SQL Server.

The SQL Provider is a PSSnapin which is a compiled module written PowerShell⚡️🐚. The SQL Provider PSSnapin is collection of cmdlets specific to SQL Server.

By default, neither the SQL Provider or the SMO libraries are loaded into PowerShell⚡️🐚. So, they need to be added manually by executing the following syntax:

Install-Module -Name SqlServer

or

Install-Module -Name SqlServer -AllowClobber

As a basic example, we can use SQL Provider to connect to a SQL Server and find what instances are on that server. We can use Get-ChildItem cmdlet to return the instance information and then put that into an array. Then we can utilize foreach and for each object in these instances that’s returned so we can do our work against each child name contained in our instances array. SMO is a. NET library that Microsoft designed for working with SQL Server. At the very core of SMO, everything is a server object and an object is something as it exists. For example, a server object has a corresponding server associated with it. Each server has things on it such as backup devices, credentials and databases, etc. In addition to objects, SMO has collections which are basically an array of objects. The SMO model considers each instance to be an individual server. Because SMO starts at the instance level, there’s no really good way using SMO to go out and query all of your machines and find out what instances you’re running🏃‍♂️. However, we can use ADO. NET to get all our servers we want to manage.

After covering SQL Provider and SMO, Robert then show us through his custom script how we can use SQL Provider and SMO to perform tasks a DBA might do.  Next, we looked at the relationship between SMO and the SQL Provider.

After performing DBA tasks with SQL Provider and SMO, we learned how to develop against SQL Server using just the SQL Provider. See Scripts

Next, we were provided the challenge of doing the same tasks in the previous module like creating databases but this time🕰️ using SMO. See Scripts

Finally, as we wrapped up Robert’s excellent series, we were provided real world🌎 example of using PowerShell⚡️🐚. Our mission was to find tables that contain Text, end text and image data types. See Scripts

As an encore, the course covers SQLPS. SQLPS is a special customized shell just for working with SQL Server. It was initially written between PowerShell⚡️🐚 v1 and PowerShell⚡️🐚 v2, so it’s bit of amalgamation.  It most cases SQLPS should be avoided as it not the full PowerShell⚡️🐚 environment and especially should be avoided for SQL Server Agent Scheduled jobs as you should call to the full shell when executing SQL Server Scheduled Jobs. We really enjoyed our time🕰️ re-learning PowerShell⚡️🐚 and we were glad to be back on our learning Journey.

“The Magical Mystery Tour Is coming to take you away… Coming to take you away”

Thanks –

–MCS