dbatools 1.0. The tools to break down barriers.

Words: 780

Time to read: ~ 4 minutes

Version 1.0 is coming…

20th June 2019 and dbatools will release version 1.0

Now, I don’t remember how I started with dbatools.
I know that I was a Junior DBA at that stage and I was experimenting with PowerShell.

After being initially introduced to PowerShell when it was only Version 2, and dismissing it for something that wouldn’t catch on in a Windows environment (I never said that I was prophetic!), I was ready to attone for my actions and dive deep into this wonderful scripting language.

They say that the way to improve is to do and I was looking for a project that interweaved PowerShell with SQL Server.
I can only assume that was when dbatools found me.

Now some time has moved on and things have moved on as well.
PowerShell has released verison 7 on pre-release, I’ve taken a position as a Production DBA, and dbatools are about to release version 1.0!

In that time, I’ve learned that dbatools, more than just a PowerShell module, helps break down barriers that exist in organisations.

Barrier to learning…

This may come across as a bit banal, something that has been thrown around a lot, and something that you have all heard been said before but there are so many new things out there for data professionals.

How are we supposed to keep up-to-date with developments, how are we supposed to keep abreast of the latest technologies, how are we supposed to drink from the water hose of learning if we are not taking advantage of the automation that dbatools brings?

I cannot imagine a data professional taking advantage of containers, spinning up a few, and then saying

Hold on, I have to connect SSMS to the container to restore a database on it now. Oh wait, I have to copy and paste the backup file over first. Then I have to make sure permissions are there and correct and…yeah you need to wait.

Not me

Imagine it! Imagine the speed and progress taking place.

  • Find an image: docker search and its done!
  • Download it: docker pull and its done!
  • Create the container: docker create and its done!
  • Deploy the database: “ah hold on guys, this is going to take at least an hour...”

The flow is cut.
Its speed-bumped; road blocked by a data professionals lack of automation.

dbatools can help you, it has the commands. Restore-DbaDatabase, Copy-DbaLogin, and the work is done!

It is not that there is no time to learn the new technologies, it is not using automation to give yourself time to learn.

Use dbatools, push the speed-bump down the pipeline, and leverage that new found time to learn to become more valuable!

Barriers in roles…

DevOps is the union of people, process, and products to enable continuous delivery of value to our end users

Donovan Brown

Now imagine trying to implement DevOps in a organisation where the people are siloed?

There is no collaboration, no communication because people don’t have the knowledge or the tools to provide support to anyone else.

Developers have to wait for the DBA each time they want to restore a database on a dev environment.

Operations can’t troubleshoot an issue on the database because they don’t have the permissions or the know-how to connect to an instance.

Middle management have to go to every single instance to run the same query for information about their SQL Server estates.
RDP, SSMS, New Query, Paste, Run over and over and over again.

dbatools empowers these people.

Tools can be written using dbatools that restores a masked database for developers. This can be logged and as open or as restrictive as you want.

Operations can use Install-DbaWhoIsActive and Invoke-DbaQuery to check out issues on an instance without having the bother the DBA first.

Middle management can be given a script to query all the servers from Get-DbaRegServer and Invoke-DbaQuery to run what they want. What was once a multitude of mouse clicks can be reduced to the running of a single file.

Everything logged, everything known, and everyone empowered.

dbatools breaks down these barriers…

dbatools is about to publish version 1.0 but this does not mean that the work stops.

There are knowledge seekers to help, Issues to troubleshoot, Feature Requests to pour over, Documentation to create and update, Tests to write and verify.

Do not be put off; version 1.0 is something to celebrate, not something to be intimidated by.

Join in!
Learn, laugh, and lend a hand.
Version 1.0 is released but, thankfully, numbers don’t stop at 1.

Now this is not the end. It is not even the beginning of the end. But it is, perhaps, the end of the beginning.

Winston Churchill

Updating the Account Password for my Constrained Endpoint

Words: 448

Time to read: ~ 2 minutes

A while ago, I was asked about Automation and told a story about my friend James.

In that story I talked about Constrained Endpoints and how, by using them, I could do a take the best bits of automation & delegation and not have to worry about unlocking James anymore.

Well, I was wrong

A while after I created that Constrained Endpoint, I was greeted one day by James saying he was receiving a weird error when he tried to unlock his account.

Connecting to remote server ‘server_name’ failed with the following error message : The creation of a new
Shell failed. Verify that the RunAsPassword value is correctly configured and that the Group Policy setting “Disallow
WinRM from storing RunAs credentials” is Disabled or Not Configured. To enable WinRM to store RunAs credentials,
change this Group Policy setting to Disabled. For more information, see the about_Remote_Troubleshooting Help topic.

CategoryInfo : OpenError: (‘server_name’:String) [], PSRemotingTransportException
FullyQualifiedErrorId : CannotConnectRunAsFailed,PSSessionStateBroken

Unlock James has turned into Error James.

The fact that this occurrence came the day after I had reset my password, and the fact that the error message contained the words “[v]erify that the RunAsPassword value is correctly configured” was not something that was lost on me.

Luckily, PowerShell is fabulously easy to explore with it’s Get-Help command so it was a simple case to look for commands around Session Configurations – Get-Command -Name *Session*Configuration* – and look at the help contents of the Set-PSSessionConfiguration cmdlet.

Make sure you include proper help in your functions, it’ll help you immensely when you come back to it after some time.

Get-Help -Name Set-PSSessionConfiguration -Detailed
Also available by using help Set-PSSessionConfiguration -Parameter RunAsCredential

The Fix this Time

The evidence pointed to me needing to update the RunAsCredential of the endpoint and that was the overall fix for this issue.

Set-PSSessionConfiguration -Name UnlockJamesEndpoint -RunAsCredential (Get-Credential)

I threw in my username and my new password, did a quick test to see if the endpoint was available for me ( it was ), asked James to test that it was available for him ( it was ), and I closed off the ticket.

Aesop Out

Constrained Endpoints are not a technology that I am familiar with yet. It’s nice to know that I can take a look at the error messages, use some troubleshooting processes – check out the book “How to Find a Wolf in Siberia” by Don Jones ( blog | twitter ) – and figure it out.

Then again, the technology world is filled with new technologies and if you have a job where you know everything about your technology stack then congratulations to you.

For everyone else, get used to not knowing. Network, Search, Learn. You’ll be obliviously proficient in no time!

T-SQL Tuesday 114 – Puzzle Party

Words: 1,858

Time to read: ~ 5 minutes

It’s T-SQL Tuesday time and this time we have Matthew McGiffen ( blog | twitter ) who is asking us about a Puzzle Party!

Let’s see what exactly he is asking us though.

• Present a puzzle to be solved in SQL and challenge your readers to solve it.

• Or give us a puzzle or quiz about SQL or databases.

• Show the SQL solution to a classic puzzle or game.

• Provide a method for solving a classic sort of querying puzzle people face.

• Show how newer features in SQL can be used to solve old puzzles in new ways.

• Tell us about a time you solved a problem or overcame a technical challenge that was a real puzzle.

• Or just make your own interpretation of “puzzle” and go for it!

Matthew McGiffen

I didn’t have much in the idea department for most of them so I’m thankful that Matthew left us with the last own interpretation option. So here is my attempt at a puzzle.

Invoke-LollerCoaster!

Should I Cheat?

It’s a slightly strange story but I had already done a slight “puzzle” or “trick” in PowerShell where I created a Loller Coaster Don’t ask me why, blame Andy Mallon ( blog | twitter ).

Now, that implementation used PowerShell but I thought to myself…

You know what…it uses System.Data.DataTable…that’s nearly the same thing as querying from a database. Could I get away with posting that?

Me

In the end, I decided against using the PowerShell version and said is there anyway that I could port it over to SQL Server?

PowerShell

First, you can find the PowerShell version of this in my Github account here:
https://github.com/shaneis/RandomScripts/blob/master/Invoke-Lollercoaster.ps1

I’m more proud of that than this attempt…

Look at it go!!!

SQL Server

Now for the SQL Server version.

Let’s create our table in tempdb and populate it. Could this have been made more compact? Perhaps with some fancy string manipulation and some such?

Oh yeah! But this was a quick job done during my lunch break and I wanted it done more than I wanted it perfect.

Plus, if you don’t like the below, you’re really not going to like what I use in the actual script…


USE tempdb;
GO
IF OBJECT_ID(N'dbo.LollerCoaster', N'U') IS NOT NULL BEGIN
	DROP TABLE dbo.LollerCoaster;
END;
GO
CREATE TABLE dbo.LollerCoaster (
	loller_coaster_id tinyint IDENTITY(1, 1) NOT NULL,
	loller_coaster_stage varchar(4000) NOT NULL
);
GO
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
        __)
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
         __)
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          __)
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL\
           O\)
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O\
            L\)      LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L\        LOL   LOL
             O\)    O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O\      O   O O   O
              L\)   L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L\    L     L     L
               O\) O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O\_)O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O__)O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O       O   O O   O
              L    )L     L     L
               O _/O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L         LOL   LOL
             O      )O   O O   O
              L    /L     L     L
               O  /O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O
            L        )LOL   LOL
             O      /O   O O   O
              L    /L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O          ) 
            L        /LOL   LOL
             O      /O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL            )
           O          / 
            L        /LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O          __)
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O           __)
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O            __)
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O          
            L         LOL\  LOL
             O       O   O\)   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O          
            L         LOL   LOL
             O       O   O\O   O
              L     L     L\)   L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L\    L
               O   O       O\) O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O__)O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L    )L
               O   O       O _/O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O  \O
              L     L     L    )L
               O   O       O  /O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O  \O
              L     L     L    )L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O( \O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O/  O
              L     L     L()   L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L(    L
               O   O       O\) O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O__)O
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O __)
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O  __)
                LOL         LOL
                               O
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL\
                               O\)
                                L     LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O\
                                L\)   LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L\    LOL
                                 O\) O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O\_)O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L     LOL
                                 O__)O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O
                                L    )LOL
                                 O _/O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL        )
                               O      /
                                L    /LOL
                                 O   O
                                  LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
          
        LOL
           O         
            L         LOL   LOL
             O       O   O O   O
              L     L     L     L
               O   O       O   O
                LOL         LOL
                               O      __)
                                L     LOL
                                 O   O
                                  LOL')
GO

Next we’ll need to set up some things and create a few variables

SET NOCOUNT ON;
-- Set Ctrl + T to send results to text!

DECLARE @Counter tinyint;
DECLARE @End tinyint;
DECLARE @Stage varchar(4000); -- Horribly oversized, I know and apologise.
DECLARE @Pause varchar(12);
DECLARE @Clear varchar(4000); -- again, apologies...

Then we can set the variables that we have

SET @Counter = 1;
-- I could move this after the insert and use @@ROWCOUNT
-- but I created/inserted into the table in a different session.
SET @End = (SELECT COUNT(*) FROM dbo.LollerCoaster);
-- Just 15 line feeds
SET @Clear = REPLICATE(CHAR(13), 15);

Then… the rest is a WHILE loop. 😦

RAISERROR(@Clear, 0, 1) WITH NOWAIT;

WHILE @Counter <= @End BEGIN

	SET @Stage = (SELECT loller_coaster_stage FROM dbo.LollerCoaster WHERE loller_coaster_id = @Counter);
	
	RAISERROR(@Stage, 0, 1) WITH NOWAIT;

	IF @Counter >= 21 AND @Counter <= 29 BEGIN
	    SET @Pause = '00:00:00.100';
	END; ELSE BEGIN
		SET @Pause = '00:00:00.200';
	END;

	WAITFOR DELAY @Pause;
	RAISERROR(@Clear, 0, 1) WITH NOWAIT;
	SET @Counter += 1;
END;

There’s only a few things to mention here.

  • I’m clearing the screen at the start just for cleanliness.
  • There’s a tight loop between counters 21 and 29 where we want to simulate it speeding up.
  • We’re setting a pause between each one since we want the users to actually see the differences.
  • It can be improved so much!
USE [tempdb];
GO
SET NOCOUNT ON;


DECLARE @Counter tinyint;
DECLARE @End tinyint;
DECLARE @Stage varchar(4000);
DECLARE @Pause varchar(12);
DECLARE @Clear varchar(4000);

SET @Counter = 1;
SET @End = (SELECT COUNT(*) FROM dbo.LollerCoaster);
SET @Clear = REPLICATE(CHAR(10), 15);

RAISERROR(@Clear, 0, 1) WITH NOWAIT;

WHILE @Counter <= @End BEGIN

	SET @Stage = (SELECT loller_coaster_stage FROM dbo.LollerCoaster WHERE loller_coaster_id = @Counter);
	
	RAISERROR(@Stage, 0, 1) WITH NOWAIT;

	IF @Counter >= 21 AND @Counter <= 29 BEGIN
	    SET @Pause = '00:00:00.100';
	END; ELSE BEGIN
		SET @Pause = '00:00:00.200';
	END;

	WAITFOR DELAY @Pause;
	RAISERROR(@Clear, 0, 1) WITH NOWAIT;
	SET @Counter += 1;
END;
It's a sled riding down a rollercoaster made of the word LOL repeating.
No, I don’t know why the sizes change half-way through.

Is there a UNION in PowerShell

Words: 462 487

Time to read: ~ 3 minutes

TL;DR: [System.Collections.Generic.HashSet<T>]

It has been a while ago since I’ve blogged so it seems fitting that this post will be about a question that was asked of me a while ago. The question was along the lines of “Can I join objects in PowerShell but remove duplicates?”.

So this is allowed:

1
2
3
4

But this isn’t allowed:

1
2
3
1

For me, it summed down to “Is there something like UNION or UNION ALL in PowerShell?

Luckily, this is something that I had asked before and been told the answer. So here I am, repeating the answer for you all since this is how I learn; repetition and practice.

The PowerShell type:

[System.Collections.Generic.HashSet<T>]
Don’t worry about the ‘<T>’. I didn’t figure it out either but I’ve been informed that it means a generic Type.

UPDATE: I have been reliably informed that this is a major understatement and I will update when I know more/learn more/am taught more about this.

https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.hashset-1?view=netframework-4.7.2

An Example, not a Speech.

Let’s say we have similar but different objects.

$Boom = 1
$Blast = 2
$And = 3
$Ruin = 4

Now the question that we are asked is, if there is anyway to do a UNION on these objects?

Absolutely, we’ll create a hashset object and start putting these objects into the HashSet. I know these objects are integers so I’m going to put [Int] in for the <T> type.

#Create the HashSet object.
$HashSet = [System.Collections.Generic.HashSet[Int]]::new()

#Add the objects.
foreach ($Item in $Boom,$Blast,$And,$Ruin) {
    $HashSet.Add($Item)
}
Verbose by default

Now when you add something to a HashSet using the .Add method it returns either a True or a False.

Returns
Boolean
true if the element is added to the HashSet<T> object; false if the element is already present.

https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.hashset-1.add?view=netframework-4.7.2#System_Collections_Generic_HashSet_1_Add__0_

If we then check the HashSet object, it returns both of our objects.

#Show us what you GOT!!!
$HashSet
and a 1 and a 2 and a…

If you don’t want to see the output of the .Add() method then you can push the output to one of the nulls e.g. $null = , [void] , > $null, | Out-Null, with the first two being placed at the start of the line and the last two at the end.

# Clear the HashSet.
$HashSet.Clear()

# Add items and hide output.
$null = foreach ($Item in $Boom,$Blast,$And,$Ruin) {
    $HashSet.Add($Item)
}

# See if it still worked.
$HashSet
Hide and seek…

Check for Duplicates.

Now let’s see what happens if we try to add a duplicate object to the HashSet.

#Clear the HashSet
$HashSet.Clear()

#Populate it again.
foreach ($Item in $Boom,$Blast,$And,$Boom){
    $HashSet.Add($Item)
}

#Check it again
$HashSet
No I don’t want no duplicates

HashSet sees the duplicate value, gracefully says no (False), and does not add it.

But wait, there’s more!

HashSet is something that I think DBAs will like as it is based on Sets. If you have the time, check out some of the other methods that it has.

#What else you got?
Get-Member -InputObject $HashSet
A set that contains everything except itself.

Take a look!

When does a Failing Pester Test return Green?

Words: 716
Time to read: ~ 3.5 minutes
TL;DR: Don't nest It blocks

I’ve been working more with different people in different departments more and more lately at work.

“Great” you might say and I would agree with you.

This has meant that any and all scripts that I write to give to these departments have to be tested so that they don’t fail when they are run.

“Great” you might say and, again, I would agree with you.

New-Fixture

For PowerShell scripts, I’ve been using Pester. When I want to write a new function, I run a Pester command to create a .ps1 and a .Tests.ps1 file.

It’s a simple enough setup – I checkout a new branch for the command and switch to it.

git checkout -b NewFunction

I know that Pester has a command that I can use to create a framework of a new function and a framwork of a Pester test file for that function.

Get-Command -Verb New -Module Pester |
    Where-Object Definition -like '*create*scripts*tests*'

This returns the command New-Fixture and a quick scan of the help shows that this is exactly what we are looking for.

(Get-Help New-Fixture).Synopsis

This function generates two scripts, one that defines a function
and another one that contains its tests.

(Get-Help New-Fixture).Synopsis

TDD

An effect of working with more and more departments is that I have less and less time to spend on these functions. That’s meant that I’ve had to take a hard look at what would be a “nice to have” and a “must have”.

Because of this I’ve spent more time on planning and writing the tests first. Then I write my scripts to pass these tests and only these tests.

If it is a “must have” then it gets a test. If it has a test then the script gets that functionality, or property, or parameter, etc.

If it’s a “nice to have” then it doesn’t get a test. If it doesn’t get a test then it’s not in the script. When I have time later then I’ll go back and see if I’ll add it

I have yet to have time to go back and add stuff but then I’ve yet to have a need to add any of the “nice to have”s.

You may think that thinking about what’s needed and writing the tests takes time and you’d be correct.

But anytime spent on the planning part is saved by not spending time writing code that isn’t needed and getting it to work. Overall, it’s a major time saving technique.

Back on Track

Here is what is in the .Tests.ps1 file created from the New-Fixture command.

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path) -replace '.Tests.', '.'
. "$here\$sut"
Describe "<function name>" {
It "does something useful" {
$true | Should -Be $false
}
}

Contents of .Tests.ps1 file

It’s a nice template and all you need to do is modify that It block to have your test instead.

Here is where my Pester test failed and returned a green.

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path) -replace '\.Tests\.', '.'
. "$here\$sut"

Describe "Test-FakeFunction" {
    It "does something useful" {
        It 'should pass by deafult' {
            $true | Should -Be $false
        }
    }
}

It has a helpful message for you…

Do you see it?

The Reason

In modifying the template, I forgot to take out the original It block and just put my It block inside it.

This lead to my block “pass by deafult” failed
(as it should cause of that typo) but the original, parent block it was in “does something useful” passed!

It surprised me that I had a passing and failing tests since I thought I had only written 1 test but the maintainers of Pester included a helpful little error message for us.

You are already in a test case.

In the error message…3 times

Further Action

I’m an advocate of “if you see an issue, raise it on Github”. Even if you don’t fix the issue yourself, somebody else more than likely will come along and fix it for everyone.

Am I going to raise an issue for this on Github though?

  • An issue where it was due to my bad typing?
  • An issue where they already have it raised in the error message?
  • An issue where I accidentally put an It block inside an It block when I shouldn’t have?

For this instance, I think we’re okay…

Pester Testing Self Contained Scripts

Words: 1009

Time to read: ~ 5 minutes

Update:
    2019-02-22: Added a test for Jakub’s (don’t freak out, don’t freak out) comment.
    2019-06-13: Check out Jakub’s post on the topic – Such elegance http://jakubjares.com/2019/06/09/2019-07-testing-whole-scripts/

Continue reading “Pester Testing Self Contained Scripts”