Using the PowerShell AST to find a ForEach Method

In dbachecks we enable people to see what checks are available by running Get-DbcCheck. This gives a number of properties including the ‘type’ of check. This refers to the configuration item or parameter that is required to have a value for this check to run.

For example – Any check to do with SQL Agent is of type Sqlinstance because it requires an instance to be specified but a check for SPN is of type ComputerName because it requires a computer name to run.

Automation for the win

Because I believe in automation I do not want to have to hard code these values anywhere but create them when the module is imported so we use a json file to feed Get-DbcCheck and populate the Json file when we import the module. This is done using the method that I described here and means that whenever a new check is added it is automatically available in Get-DbcCheck without any extra work.

We use code like this
First we parse the code with the AST and store that in the CheckFileAST variable, then we use the FindAll method to find any command elements that match “Describe” which conveniently gets our describes and then we can simply match the Parent object which holds some code to each function that we use to get our values to be passed to the tests Get-ComputerName, Get-Instance, Get-ClusterObject and set the type appropriately.
which when run against a check like this
will find the describe block and get the title “Backup Path Access”  and the tags BackupPathAccess, Storage, DISA, $filename and then find the Get-Instance and set the type to SqlInstance

Until Rob breaks it!

This has worked wonderfully well for 6 months or so of the life of dbachecks but this week I broke it!
The problem was the performance of the code. It is taking a long time to run the tests and I am looking at ways to improve this. I was looking at the Server.Tests file because I thought why not start with one of the smaller files.
It runs the following checks
Server Power Plan Configuration
SPNs
Disk Space
Ping Computer
CPUPrioritisation
Disk Allocation Unit
Instance Connection
and it was looping through the computer names for each check like this
I altered it to have only one loop for the computer names like so
and immediately in testing my checks for the Server Tag decreased in time by about 60% 🙂
I was very happy.
Then I added it to the dbachecks module on my machine, loaded the module and realised that my Json file for Get-DbcCheck was no longer being populated for the type because this line
was no longer true.

AST for other things

So I googled Management.Automation.Language.Ast the first result lead me to docs.microsoft There are a number of different language elements available there and I found InvokeMemberExpressionAst which will let me find any methods that have been invoked, so now I can find the loops with
When I examined the object returned I could see that I could further limit the result to get only the method for Get-ComputerName and then if I choose the Extent I can get the code of that loop
and now Get-DbcCheck is returning the right results and the checks are a little faster
You can find dbachecks on the PowerShell Gallery or install it using

 

Hitting the Wall – #TSQL2sDay

farrel-nobel-97504-unsplashPhoto by Farrel Nobel on Unsplash

Welcome to another edition of T-SQL Tuesday!

This T-SQL Tuesday is hosted by Wayne Sheffield ( blog | twitter ) and he has asked us to talk about

[…] a time when you ran up against your own brick wall, and how you worked it out or dealt with it.

Hitting The Wall

When stuck in a problem in the past, I could often be found glued to a keyboard and screen for many hours. I would try this way and that way, I would Google and read Stack Overflow looking for ways around the particular issue I was seeing trying different things and finally I would get frustrated and fed up and stop.

Maybe I would go and walk the dog, maybe just sit somewhere else but I would often find that I had an idea how to solve my problem and quickly go back to the keyboard frustrated that I hadnt thought about this earlier and frequently rinse and repeat.

A Different Way

It took me many years to realise this and I wish I had done so sooner but once I made the connection that leaving the problem to one side for a little while meant that I often found a way to a solution for a problem I started setting a time limit.

30 minutes

If I have been stuck on a problem for 30 minutes, I (mostly, I still sometimes fail at this) stop, take a break, go for a walk or do something different and the number of times that I arrive if not at a solution then at a path to a solution is remarkable.

Ask

The other thing to do at this point in the troublesome problem solving is to ask. Twitter, Google, Slack, Stack Overflow. These are all excellent resources where you can quickly find people who are willing and capable of helping.

Don’t be like me and take years to work this out 🙂

A PowerShell Conference In A Book

A Question

Shortly after the European PowerShell Conference and the PowerShell and Devops 2018 summit in the USA Mike Robbins b | t contacted me with a question.

Interested in writing a chapter in a PowerShell book?
I was intrigued and read on.

A Conference in a Book

There was more to this book than just writing about PowerShell though. Mike was suggesting that a group of wonderful PowerShell experts (Here’s a Twitter list) got together and created a conference in a book.

The book is designed as a conference in a book where each chapter is written independently with content similar to what you would present in a 45 minute presentation.

That’s a neat idea, people who couldn’t come to one of the conferences would be able to get an experience a little bit like attending a conference but in book form.

OK there would be no networking, evening entertainment or instance responses to questions but a bundle of useful information that you can take with you and read anywhere.

It’s All For Charity

(I hope older UK viewers read that in this voice 🙂 )

The bit that clinched it for me was this though

We’re donating all of the royalties from the book to the DevOps Collective Scholarship program https://leanpub.com/causes/devopscollective.

All the money raised by buying this book will go to the DevOps Collective OnRamp Scholarship program.

This scholarship provides

  • ticket to PowerShell and DevOps Global Summit OnRamp track specifically designed for entry-level professionals
  • five nights lodging
  • domestic airfare
  • buddy programme
and half of the slots are reserved for under-represented groups.
I really approve of this idea, without the help and support of the SQL and PowerShell technical communities I would not be where I am today and this will help to bring other people in at an early stage in their career. I am proud that I can give a little back.

Fabulous Editors

So I said yes.

I then had to sit down and write some words. I wrote about how we created dbachecks, the challenges we faced and how we overcame them.

One of my biggest challenges was writing in the wrong English! The book is written in American English and there are zeds where there should be esses and missing u’s in words! My spell checker was covered in red squiggles! The second challenge was getting the code to fit the column limit for the book. I show a lot of the AST code that we use to validate that dbachecks code will work correctly and it doesnt split to 80 characters very easily.

Luckily I had 3 wonderful, patient editors to help me with all of this. Mike Robbins , Michael T Lombardi and Jeff Hicks each helped me to make the chapter read more fluently, make sense and be spelled correctly!

Thank you very much you three for all the work you have put into this book.

Help Yourself and Others

If you want to attend a PowerShell conference in book form, want 30 chapters of fabulous PowerShell material and want to help grow and diversify our industry then look no further you can get the book here

book

 

You can also find all of the authors twitters and websites below, You should go and see what they are sharing there as well.

Author Website
Mike F Robbins https://mikefrobbins.com
Jeff Hicks https://jdhitsolutions.com
Michael Lombardi https://appoint.ly/t/michaeltlombardi
Adam Murry https://tikabu.com.au/blog/
Anthony Nocentino http://www.centinosystems.com
Brandon Olin https://devblackops.io
Brian Bunke https://www.brianbunke.com
Don Jones https://donjones.com
Doug Finke https://dfinke.github.io
Emin Atac https://p0w3rsh3ll.wordpress.com
Fred Weinmann https://allthingspowershell.blogspot.com
Graham Beer https://graham-beer.github.io
Irwin Strachan https://pshirwin.wordpress.com
James Petty https://scriptautomaterepeat.com
Jeremy Murrah https://murrahjm.github.io
Justin Sider https://invoke-automation.blog
Luc Dekens http://www.lucd.info
Mark Kraus https://get-powershellblog.blogspot.com
Mark Wragg https://wragg.io
Mike Kanakos https://www.networkadm.in
Mike Shepard https://powershellstation.com
Patrick Gruenauer https://sid-500.com
Prateek Singh https://ridicurious.com
Rob Pleau https://ephos.github.io
Thomas Lee https://tfl09.blogspot.com
Thomas Rayner https://workingsysadmin.com
Thom Schumacher https://powershellposse.com
Tim Curwick https://MadWithPowerShell.com
Tim Warner https://timwarnertech.com
Tommy Maynard https://tommymaynard.com
Tore Groneng https://asaconsultant.blogspot.com
Wesley Kirkland https://wesleyk.me

A PowerShell Pester Check for parsing SQL scripts

I like to write Pester checks to make sure that all is as expected! This is just a quick post as much to help me remember this script 🙂

This is a quick Pester test I wrote to ensure that some SQL Scripts in a directory would parse so there was some guarantee that they were valid T-SQL. It uses the SQLParser.dll and because it was using a build server without SQL Server I have to load the required DLLs from the dbatools module (Thank you dbatools 🙂 )

It simply runs through all of the .sql files and runs the parser against them and checks the errors. In the case of failures it will output where it failed in the error message in the failed Pester result as well.

You will need dbatools module installed on the instance and at least version 4 of the Pester module as well

dbachecks – Dark Mode Historical Validation PowerBi

in my last post I showed how you can save the results of dbachecks to a database and created a PowerBi report. Inspired by Frank Henninger in the #dbachecks slack channel and Shawn Melton who explained the difficulties with red/green colour blind I then created this one 🙂

dark mode

You can find it in my GitHub and have a play with it below

Happy Validating!

dbachecks – Save the results to a database for historical reporting

I gave a presentation at SQL Day in Poland last week on dbachecks and one of the questions I got asked was will you write a command to put the results of the checks into a database for historical reporting.

The answer is no and here is the reasoning. The capability is already there. Most good PowerShell commands will only return an object and the beauty of an object is that you can do anything you like with it. Your only limit is your imagination 🙂 I have written about this before here. The other reason is that it would be very difficult to write something that was easily configurable for the different requirements that people will require. But here is one way of doing it.

Create a configuration and save it

Let’s define a configuration and call it production. This is something that I do all of the time so that I can easily run a set of checks with the configuration that I want.

Now I can export that configuration to a json file and store on a file share or in source control using the code below. This makes it easy to embed the checks into an automation solution
and then I can use it with
01 - Invoke-DbcCheck
I would use one of the Show parameter values here if I was running it at the command line, probably fails to make reading the information easier

Add results to a database

This only gets us the test results on the screen, so if we want to save them to a database we have to use the PassThru parameter for Invoke-DbcCheck. I will run the checks again, save them to a variable

Then I can use the dbatools Write-DbaDatatable command to write the results to a table in a database. I need to do this twice, once for the summary and once for the test results

and I get two tables one for the summary

02 - summary

and one for the details

03 - detail
This works absolutely fine and I could continue to add test results in this fashion but it has no date property so it is not so useful for reporting.

Create tables and triggers

This is one way of doing it. I am not sure it is the best way but it works! I always look forward to how people take ideas and move them forward so if you have a better/different solution please blog about it and reference it in the comments below

First I created a staging table for the summary results

and a destination table with a primary key and a date column which defaults to todays date

and added an INSERT trigger to the staging table

and for the details I do the same thing. A details table

A stage table

with a trigger

Then I can use Write-DbaDatatable with a couple of extra parameters, FireTriggers to run the trigger, Truncate and Confirm:$false to avoid any confirmation because I want this to run without any interaction and I can get the results into the database.

detail with stage

Which means that I can now query some of this data and also create PowerBi reports for it.

To enable me to have results for the groups in dbachecks I have to do a little bit of extra manipulation. I can add all of the checks to the database using

But because the Ola Hallengren Job names are configuration items I need to update the values for those checks which I can do as follows

You can get a sample Power Bi report in my Github which also has the code from this blog post

Then you just need to open in PowerBi Desktop and

Click Edit Queries
Click Data Source Settings
Click Change Source
Change the Instance and Database names

09 - PowerBi

Then have an interactive report like this. Feel free to click around and see how it works. Use the arrows at the bottom right to go full-screen. NOTE – it filters by “today” so if I haven’t run the check and the import then click on one of the groups under “Today’s Checks by Group”

This enables me to filter the results and see what has happened in the past so I can filter by one instance
05 - filter by instance
or I can filter by a group of tests
07 - filter by instance
or even by a group of tests for an instance
08 - filter by instance and insance

Hopefully, this will give you some ideas of what you can do with your dbachecks results. You can find all of the code and the PowerBi in my GitHub

Happy Validating!

dbachecks – Improved Descriptions

With the latest release of dbachecks we have added a new check for testing that foreign keys and constraints are trusted thanks to Cláudio Silva b | t

To get the latest release you will need to run

You should do this regularly as we release new improvements frequently.

We have also added better descriptions for the checks which was suggested by the same person who inspired the previous improvement I blogged about here

Instead of the description just being the name of the check it is now more of a, well, a description really 🙂

This has the added effect that it means that just running Get-DbcCheck in the command line will not fit all of the information on a normal screen

01 - get-dbccheck.png

You can use the Format-Table command (or its alias ft at the command line) and select the properties to display using

02 - get-dbccheck format table

or you can use Format-List (or its alias fl at the command line)

03 get-dbccheck format list.png
Or you can use Out-GridView (or its alias ogv at the command line) (Incidentally, could you also thumbs up this issue on Github to get Out-GridView functionality in PowerShell 6)
04 - get-dbacheck ogv
Happy Validating !

dbachecks – Which Configuration Item For Which Check ?

I love showing dbachecks to people. It’s really cool seeing how people will use it and listening to their experiences. I was showing it to a production DBA a month or so ago and he said

How Do I Know Which Checks There Are?

OK you just need to run

and it will show you

01 - get-dbcchecks.png

It will show you the group, the type (does it need a computer name or an instance name), The description, the unique tag for running just that check and all the tags that will run that check

OK he said, you talked about configurations

How Do I Know Which Configurations There Are?

So to do that you just need to run

and it will show you

02 - dbcconfig.png

You can see the name, the current value and the description

Ah thats cool he said so

How Do I Know Which Configuration Is For Which Check?

Well, you just…. , you know…… AHHHHHHH

Ping – light bulb moment!

It’s always really useful to give something you have built to people who have never seen it before and then listen to what they say. Their new eyes and different experiences or expectations will give you lots of insight

None of the amazing contributors to dbachecks had thought of this scenario so I decided to fix this. First I asked for an issue to be raised in GitHub because an issue can be an improvement or a suggestion not just a bug.

Then I fixed it so that it would do what was required. Thank you Nick for this feedback and for helping to improve dbachecks

I improved Get-DbcCheck so that now it shows the configuration item related to each check

It is easier to see (and sort or search) if you use Out-GridView

03 - New dbccheck.png

So now you can see which configuration can be set for each check!

 

Happy Validating!

Creating SQL Server Containers for versions 2012-2017

I am working on my dbatools and dbachecks presentations for SQL Saturday Finland, SQLDays, SQL Saturday Cork and SQLGrillen I want to show the two modules running against a number of SQL Versions so I have installed

  • 2 Domain Controllers
  • 2 SQL 2017 instances on Windows 2016 with an Availability Group and WideWorldImporters database
  • 1 Windows 2016 jump box with all the programmes I need
  • 1 Windows 2016 with containers

using a VSTS build and this set of ARM templates and scripts

I wanted to create containers running SQL2017, SQL2016, SQL2014 and SQL2012 and restore versions of the AdventureWorks database onto each one.

Move Docker Location

I redirected my docker location from my C:\ drive to my E:\ drive so I didnt run out of space. I did this by creating a daemon.json file in C:\ProgramData\docker\config and adding

and restarting the docker service which created folders like this
01 - folders.png
Then I ran
to create a volume to hold the backups that I could mount on the containers

AdventureWorks Backups

I downloaded all the AdventureWorks backups from GitHub and copied them to E:\containers\volumes\sqlbackups\_data

Getting the Images

To download the SQL 2017 image from the DockerHub I ran

and waited for it to download and extract

I also needed the images for other versions. My good friend Andrew Pruski b | t has versions available for us to use on his Docker Hub  so it is just a case of running

and waiting for those to download and extract (This can take a while!)

Create the containers

Creating the containers is as easy as

so all I needed to run to create 4 SQL containers one of each version was

and just a shade over 12 seconds later I have 4 SQL instances ready for me 🙂

02 - creating containers.png

03 - Containers at the ready.png

Storing Credentials

This is not something I would do in a Production environment but I save my credentials using this method that Jaap Brasser b | t shared here

which means that I can get the credentials in my PowerShell session (as long as it is the same user that created the file) using

Restoring the databases

I restored all of the AdventureWorks databases that each instance will support onto each instance, so 2017 has all of them whilst 2012 only has the 2012 versions.

First I needed to get the filenames of the backup files into a variable

and the container connection strings, which are the hostname and the port number

then I can restore the databases using dbatools using a switch statement on the version which I get with the NameLevel property of Get-DbaSqlBuildReference-
I need to create the file paths for each backup file by getting the correct backups and appending the names to C:\SQLBackups which is where the volume is mounted inside the container
As Get-DbaDatabase gives the container ID as the Computer Name I have highlighted each container below
04 - databases.png
That is how easy it is to create a number of SQL containers of differing versions for your presentations or exploring needs
Happy Automating!

Visual Studio Code Live Sharing Set-Up

There was an announcement on the Visual Studio Code blog about the public preview of Live Share. This enables you to easily collaborate on code by securely sharing your coding session.

It is remarkably easy to set up 🙂

Installation

Open Visual Studio Code, open the Extensions side bar (CTRL + SHIFT + X)

01 - open extensions

Search for Live Share

02 - search.png

Click Install and then reload when it has done

03 - reload.png

You will notice in the bottom bar it will say finishing the installation and if you open the terminal (CTRL + ‘) and click on Output and change the drop down on the right to Visual Studio Live Share you can see what it is doing

04 - finishing installation.png

It is installing the dependancies as shown below

[Client I] Installing dependencies for Live Share…
[Client I] Downloading package ‘.NET Core Runtime 2.0.5 for win7-x86’
[Client I] Download complete.
[Client I] Downloading package ‘OmniSharp for Windows (.NET 4.6)’
[Client I] Download complete.
[Client I] Installing package ‘.NET Core Runtime 2.0.5 for win7-x86’
[Client V] Extracted packed files
[Client I] Validated extracted files.
[Client I] Moved and validated extracted files.
[Client I] Finished installing.
[Client I] Installing package ‘OmniSharp for Windows (.NET 4.6)’
[Client V] Extracted packed files
[Client I] Validated extracted files.
[Client I] Finished installing.
[Client I] No workspace id found.
Incidentally, this will also show the location of the log file

You will see in the bottom bar it will now say sign in

06 - sign in.png

Clicking that will open a browser and give you a choice of accounts to sign in with, your GitHub or your Microsoft ID

07 - sign in.png

Choose the one that you want to use and do your 2FA.

08 - 2FA.png

You do have 2FA on your Microsoft and GitHub (and all the other services)? If not go and set it up now – here for Microsoft and here for GitHub 

Once you have signed in you will get this notification which you can close

09 - close this notification.png

The icon in the bottom will change and show your account name and if you click it it will open the menu

09 - sharing menu.png

Sharing

To share your session you click on the Share icon in the bottom bar or the Start collaboration session in the menu above. The first time you do this there will be a pop-up as shown

05 - firewall popup.png

You can decide which way you (or your organisation) want to share. I chose to accept the firewall exception.

10 - invite link.png

The invite link is in your clipboard ready to share with your friends and colleagues (other open source contributors ??)

They can either open the link in a browser

11 - join via browser.png

or by using the Join Collaboration Session in the menu in VS Code

12 - Join via VS COde.png

Once they do the sharer will get a notification

13 - notification of sharing.png

and the person who has joined will have the same workspace opened in their Visual Studio Code

14 -shared workspace.png

You can then collaborate on your code and share the session. In the video below the left hand side is running in my jump box in Azure and the right hand side on my laptop and you can see that if you highlight code in one side it is shown in the other and if you alter it in one side it is changed in the other. I also saved that file in the joined session rather than from the session that initialised the sharing and it then saved in both sessions 🙂

So that shows how easy it is to install and to use. You can dive deeper using the documentation.

 

Happy Collaborating 🙂