VSCode – PowerShell extension 1.4.0 new command Out-CurrentFile

Yesterday David Wilson announced version 1.4.0 of the PowerShell extension for VSCode

He also pointed out that there have been over 1 million installs of the extension. ūüôā

If you want to install the PowerShell extension you can hit F1 in VSCode and type

ext install PowerShell

or CTRL + SHIFT + X to open the extensions side bar and search for PowerShell and click the green install button.

There are a few enhancements in this release which you can read about here but I noticed the New File API and Out-CurrentFile command that were contributed by Doug Finke.

If your focus is in the editor in VSCode, you can simply CTRL + N and create a new file. You can alter the language that the file uses with CTRL + K, M (that’s CTRL and K and then M not CTRL and K and M!) or set the default new file language as I described here.

01 -new file.gif

If you are using VSCode as your daily PowerShell command line though, you would have to alter your focus from the terminal panel into the editor to do this. Now though you have

$psEditor.Workspace.NewFile()

which enables you to create a new file from the terminal

02- Another new file.gif

The¬†Out-CurrentFile command sends the output of a command through Out-String to a new file. This makes it much easier to keep the results of some commands. You don’t have to pipe them to clip or highlight and CTRL + C to copy them and then open a¬† file and paste them. Of course you can use Out-File and then open the file but this is another way.

Lets see how it works. In this example, I want to export the T-SQL for creating the logins on an instance and add some comments to the code before saving it somewhere safely. I am going to use the Export-SQLLogin command from the dbatools module. MVP Cl√°udio Silva has written a great post on that command today.

First create a new file

$psEditor.Workspace.NewFile()

and then

Export-SqlLogin -SqlInstance . | Out-CurrentFile 

In the gif above

  • I create a new file,
  • Export the logins to it
  • change the language of the file to T-SQL
  • remove the string quotes and
  • add some comments.

All without leaving VSCode, just another reason that my productivity is increased using VSCode!

Unfortunately, it doesn’t work so well with Pester. (Of course I was going to try Pester!). This makes sense though, as Pester uses Write-Host to display the test results so nothing is going to the output stream so

Invoke-Pester '.\SQL Grillen\Demo Number 3.Tests.ps1' | Out-CurrentFile

returns this

04 - pester.PNG

and if you use the -PassThru parameter then you get the $Tests object as a string so

 Invoke-Pester '.\SQL Grillen\Demo Number 3.Tests.ps1'-Show Summary -PassThru | Out-CurrentFile

shows

05 - pester object.PNG

Which isn’t what I would need but I have other ways of working with Pester output.

Advertisements

dbatools at #SQLSatDublin

This weekend¬†SQL Saturday Dublin occurred. For those that don’t know SQL Saturdays are free conferences with local and international speakers providing great sessions in the Data Platform sphere.

Chrissy LeMaire and I presented our session PowerShell SQL Server: Modern Database Administration with dbatools. You can find slides and code here . We were absolutely delighted to be named Best Speaker which was decided from the attendees average evaluation.

Chrissy also won the Best Lightning talk for her¬†5 minute (technically 4 minutes and 55 seconds)¬†presentation on dbatools as well ūüôā

We thoroughly enjoy giving this presentation and I think it shows in the feedback we received.

Feedback

History

We start with a little history of dbatools, how it started as one megalithic script Start-SQLMigration.ps1 and has evolved into (this number grows so often it is probably wrong by the time you read this) over 240 commands from 60 contributors

Requirements

We explain the requirements. You can see them here on the download page.

The minimum requirements for the Client are

  • PowerShell v3
  • SSMS / SMO 2008 R2

which we hope will cover a significant majority of peoples workstations.

The minimum requirements for the SQL Server are

  • SQL Server 2000
  • No PowerShell for pure SQL commands
  • PowerShell v2 for Windows commands
  • Remote PowerShell enabled for Windows commands

As you can see the SQL server does not even need to have PowerShell installed (unless you want to use the Windows commands). We test our commands thoroughly using a test estate that encompasses all versions of SQL from 2000 through to 2017 and whenever there is a vNext available we will test against that too.

We recommend though that you are using PowerShell v5.1 with SSMS or SMO for SQL 2016 on the client

Installation

We love how easy and simple the installation of dbatools is. As long as you have access to the internet (and permission from your companies security team to install 3rd party tools. Please don’t break your companies policies) you can simply install the module from the PowerShell Gallery using

Install-Module dbatools

If you are not a local administrator on your machine you can use the -Scope parameter

Install-Module dbatools -Scope CurrentUser

Incidentally, if you or your security team have concerns about the quality or trust of the content in the PowerShell Gallery please read this post which explains the steps that are taken when code is uploaded.

If you cannot use the PowerShell Gallery then you can use this line of code to install from GitHub

Invoke-Expression (Invoke-WebRequest https://dbatools.io/in)

There is a video on the download page showing the installation on a Windows 7 machine and also some other methods of installing the module should you need them.

Website

Next we visit the website dbatools.io¬†and look at the front page. We have our regular joke about how Chrissy doesn’t want to present on migrations but I think they are so cool so she makes me perform the commentary on the video. (Don’t tell anyone but it also helps us to get in as many of the 240+ commands in a one hour session as well ūüėČ ). You can watch the video on the front page.¬†You definitely should as you have never seen migrations performed so easily.

Then we talk about the comments we have received from well respected people from both SQL and PowerShell community members so you can trust that its not just some girl with hair and some bloke with a beard saying that its awesome.

Contributors

Probably my favourite page on the web-site is the team page showing all of the amazing fabulous wonderful people who have given their own time freely to make such a fantastic free tool. If we have contributors in the audience we do try to point them out. One of our aims with dbatools is to enable people to receive the recognition for the hard work that they put in and we do this via the team page, our LinkedIn company page as well as by linking back to the contributors in the help and the web-page for every command. I wish I could name check each one of you.

Thank You each and every one !!

Finding Commands

We then look at the command page and the new improved search page and demonstrate how you can use them to find information about the commands that you need and the challenges in keeping this all maintained during a period of such rapid expansion.

Demo

Then it is time for me to say this phrase. “Strap yourselves in, do up your seatbelts, now we are going to show 240 commands in the next 40 minutes. Are you ready!!”

Of course, I am joking, one of the hardest things about doing a one hour presentation on dbatools is the sheer number of commands that we have that we want to show off. Of course we have already shown some of them in the migration video above but we still have a lot more to show and there are a lot more that we wish we had time to show.

Backup and Restore

We start with a restore of one database and a single backup file using Restore-DbaDatabase showing you the easy to read warning that you get if the database already exists and then how to resolve that warning with the WithReplace switch

Then how to use it to restore an entire instance worth of backups to the latest available time by pointing Restore-DbaDatabase at a folder on a share

Then how to use Get-DbaDatabase to get all of the databases on an instance and pass them to Backup-DbaDatabase to back up an entire instance.

We look at the Backup history of some databases using Get-DbaBackupHistory and Out-GridView and examine detailed information about a backup file using Read-DbaBackupHeader.

We give thanks to Stuart Moore for his amazing work on these and several other backup and restore commands.

SPN’s

After a quick reminder that you can search for commands at the command line using Find-DbaCommand, we talk about SPNs and try to find someone, anyone, who actually likes working with SQL Server and SPNs and resolving the issues!!

Then we show Drew’s SPN commands Get-DbaSpn, Test-DbaSpn, Set-DbaSpn¬† and Remove-DbaSpn¬†

Holiday Tasks

We then talk about the things we ensure we run before going on holiday to make sure we leave with a warm fuzzy feeling that everything will be ok until we return :-

  • Get-DbaLastBackup will show the last time the database had any type of backup.
  • Get-DbaLastGoodCheckDb which shows the last time that a database had a successful DBCC CheckDb and how we can gather the information for all the databases on all of your instances in just one line of code
  • Get-DbaDiskSpace¬†which will show the disk information for all of the drives including mount points and whether the disk is in use by SQL

Testing Your Backup Files By Restoring Them

We ask how many people test their backup files every single day and Dublin wins marks for a larger percentage than some other places we have given this talk. We show Test-DbaLastBackup in action so that you can see the files being created because we think it looks cool (and you can see the filenames!) Chrissy has written a great post about how you can set up your own dedicated backup file test server

Free Space

We show how to gather the file space information using Get-DbaDatabaseFreespace and then how you can put that (or the results of any PowerShell command) into a SQL database table using Out-DbaDataTable and Write-DbaDataTable

SQL Community

Next we talk about how we love to take community members blog posts and turn them into dbatools commands.

We start with Jonathan Kehayias’s post about SQL Server Max memory (http://bit.ly/sqlmemcalc) and show Get-DbaMaxMemory¬†, Test-DbaMaxMemory¬†and Set-DbaMaxMemory

Then¬†Paul Randal’s blog post about Pseudo-Simple Mode¬†which inspired¬† Test-DbaFullRecoveryModel

We talked about getting backup history earlier but now we talk about Get-DbaRestoreHistory a command inspired by Kenneth Fishers blog post to show when a database was restored and which file was used.

Next a command from Thomas LaRock which he gave us for testing linked servers Test-DbaLinkedServerConnection.

Glenn Berrys diagnostic information queries  are available thanks to André Kamman and the commands Invoke-DbaDiagnosticQuery and Export-DbaDiagnosticQuery. The second one will output all of the results to csv files.

Adam Mechanic’s sp_whoisactive is a common tool in SQL DBA’s toolkit and can now be installed using Install-DbaWhoIsActive and run using Invoke-DbaWhoIsActive.

Awesome Contributor Commands

Then we try to fit in as many commands that we can from our fantastic contributors showing how we can do awesome things with just one line of PowerShell code

The awesome Find-DbaStoredProcedure which you can read more about here which in tests searched 37,545 stored procedures on 9 instances in under 9 seconds for a particular string.

Find-DbaOrphanedFile which enables you to identify the files left over from detaching databases.

Don’t know the SQL Admin password for an instance? Reset-SqlAdmin can help you.

It is normally somewhere around here that we finish and even though we have shown 32 commands (and a few more encapsulated in the Start-SqlMigration command) that is less than 15% of the total number of commands in the module!!!

Somehow, we always manage to fit all of that into 60 minutes and have great fun doing it. Thank you to everyone who has come and seen our sessions in Vienna, Utrecht, PASS PowerShell Virtual Group, Hanover, Antwerp and Dublin.

More

So you want to know more about dbatools ? You can click the link and explore the website

You can look at source code on GitHub

You can join us in the SQL Community Slack in the #dbatools channel

You can watch videos on YouTube

You can see a list of all of the presentations and get a lot of the slides and demos

If you want to see the slides and demos from our Dublin presentation you can find them here

Volunteers

Lastly and most importantly of all. SQL Saturdays are run by volunteers so massive thanks to Bob, Carmel, Ben and the rest of the team who ensured that SQL Saturday Dublin went so very smoothly

 

 

VS Code – Automatic Dynamic PowerShell Help

VS Code is my coding tool of choice. I love that one lightweight editor can do so much and as PowerShell is usually the language that I write in I really love the PowerShell extension

Help

When you write a PowerShell function that is going to be used by someone other than you, you don’t want to be the guy or gal that has to support it indefinitely. You should write good help to enable your users to simply type

Get-Help NAMEOFCOMMAND

and get all of the help that they need to use the command

If we look at one of my favourite dbatools commands Get-DbaLastGoodCheckDB we can see this in action.

Get-Help Get-DbaLastGoodCheckDb -full

This returns

NAME
Get-DbaLastGoodCheckDb

SYNOPSIS
Get date/time for last known good DBCC CHECKDB

SYNTAX
Get-DbaLastGoodCheckDb [-SqlInstance] [[-SqlCredential] ] [-Silent] []

DESCRIPTION
Retrieves and compares the date/time for the last known good DBCC CHECKDB, as well as the creation date/time for the database.

This function supports SQL Server 2005+

Please note that this script uses the DBCC DBINFO() WITH TABLERESULTS. DBCC DBINFO has several known weak points, such as:
– DBCC DBINFO is an undocumented feature/command.
– The LastKnowGood timestamp is updated when a DBCC CHECKFILEGROUP is performed.
– The LastKnowGood timestamp is updated when a DBCC CHECKDB WITH PHYSICAL_ONLY is performed.
– The LastKnowGood timestamp does not get updated when a database in READ_ONLY.

An empty ($null) LastGoodCheckDb result indicates that a good DBCC CHECKDB has never been performed.

SQL Server 2008R2 has a “bug” that causes each databases to possess two dbi_dbccLastKnownGood fields, instead of the normal one.
This script will only displaythis function to only display the newest timestamp. If -Verbose is specified, the function will announce every time
more than one dbi_dbccLastKnownGood fields is encountered.

PARAMETERS
-SqlInstance
The SQL Server that you’re connecting to.

Required? true
Position? 1
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false

-SqlCredential Credential object used to connect to the SQL Server as a different user

Required? false
Position? 2
Default value
Accept pipeline input? false
Accept wildcard characters? false

-Silent []
Use this switch to disable any kind of verbose messages

Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

INPUTS

OUTPUTS

NOTES

Copyright (C) 2016 Jakob Bindslet (jakob@bindslet.dk)

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see .

————————– EXAMPLE 1 ————————–

PS C:\>Get-DbaLastGoodCheckDb -SqlInstance ServerA\sql987

Returns a custom object displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated, DaysSinceLastGoodCheckDb, Status and
DataPurityEnabled

————————– EXAMPLE 2 ————————–

PS C:\>Get-DbaLastGoodCheckDb -SqlInstance ServerA\sql987 -SqlCredential (Get-Credential sqladmin) | Format-Table -AutoSize

Returns a formatted table displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated, DaysSinceLastGoodCheckDb, Status
and DataPurityEnabled.
Authenticates with SQL Server using alternative credentials.

RELATED LINKS
DBCC CHECKDB:
https://msdn.microsoft.com/en-us/library/ms176064.aspx
http://www.sqlcopilot.com/dbcc-checkdb.html
Data Purity:
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run/
https://www.mssqltips.com/sqlservertip/1988/ensure-sql-server-data-purity-checks-are-performed/

So anyone who needs to use the command can see what it is, a full description, what each parameter is for, some examples and some links to more information

So what I used to do was put a snippet of code like this at the top of my function and then fill in the blanks

<#
.SYNOPSIS
Short description
.DESCRIPTION
Long description
.EXAMPLE
An example
.PARAMETER
Parameter Help
.NOTES
General notes
.LINK
Link to more information
#>

The latest release of the PowerShell extension for VS Code has made that process so much simpler ūüôā Thank you David and Keith

Now you can simply type <# and your help will be dynamically created. You will still have to fill in some of the blanks but it is a lot easier.

Here it is in action in its simplest form

Pester - Simple.gif

But it gets better than that. When you add parameters to your function code they are added to the help as well. Also, all you have to do is to tab between the different entries in the help to move between them

02 - detailed.gif

Now when we run

Get-Help Invoke-AmazingThings -Full

We get this

03 help.PNG

Nice and easy and a great feature added to the VS Code PowerShell extension

Write Good Help.png

Pester for Presentations – Ensuring it goes ok

Whilst I was at PSCONFEU I presented a session on writing pester tests instead of using checklists. You can see it here

During the talk I showed the pester test that I use to make sure that everything is ready for my presentation. A couple of people have asked me about this and wanted to know more so I thought that I would blog about it.

Some have said that I might be being a little OCD about it ūüėČ I agree that it could seem like that but there is nothing worse than having things go wrong during your presentation. It makes your heart beat faster and removes the emphasis from the presentation that you give.

When it is things that you as a presenter could have been able to foresee, like a VM not being started or a database not being restored to the pre-demo state or being logged in as the wrong user then it is much worse

I use Pester to ensure that my environment for my presentation is as I expect and in fact, in Hanover when I ran through my Pester test for my NUC environment I found that one of my SQL Servers had decided to be in a different time zone and therefore the SQL Service would not authenticate and start. I was able to quickly remove the references to that server and save myself from a sea of red during my demos

For those that don’t know.¬†Pester is a PowerShell module¬†for Test Driven Development

Pester provides a framework for running unit tests to execute and validate PowerShell commands from within PowerShell. Pester consists of a simple set of functions that expose a testing domain-specific language (DSL) for isolating, running, evaluating and reporting the results of PowerShell commands

If you have PowerShell version 5 then you will have Pester already installed although you should update it to the latest version. If not you can get Pester from the PowerShell Gallery follow the instructions on that page to install it. This is a good post to start learning about Pester

What can you test? Everything. Well, specifically everything that you can write a PowerShell command to check. So when I am setting up for my presentation I check the following things. I add new things to my tests as I think of them or as I observe things that may break my presentations. Most recently that was ensuring that my Visual Studio Code session was running under the correct user. I did that like this

Describe "Presentation Test" {
    Context "VSCode" {
        It "Should be using the right username" {
            whoami | Should Be 'TheBeard\Rob'
       }
    }
}

01 - username.PNG

I think about the things that are important to me for my presentation.  I want to ensure that I only have one VS Code window open to avoid that situation where I am clicking through windows looking for the correct window. I can do that using Get-Process

It "Should have Code Insiders Open" {
(Get-Process 'Code - Insiders' -ErrorAction SilentlyContinue)| Should Not BeNullOrEmpty
}
        It "Should have One VS Code Process" {
            (Get-Process 'Code - Insiders' -ErrorAction SilentlyContinue).Count | Should Be 1
        }

I use -ErrorAction SilentlyContinue so that I don’t get a sea of red when I run the tests. Next I want to check my PowerPoint is ready for my presentation

        It "Should have PowerPoint Open" {
            (Get-Process POWERPNT  -ErrorAction SilentlyContinue).Count | Should Not BeNullOrEmpty
        }
       It "Should have One PowerPoint Open" {
            (Get-Process POWERPNT  -ErrorAction SilentlyContinue).Count | Should Be 1
        }
        It "Should have the correct PowerPoint Presentation Open" {
            (Get-Process POWERPNT  -ErrorAction SilentlyContinue).MainWindowTitle| Should Be 'dbatools - SQL Server and PowerShell together - PowerPoint'
        }

Again I use Get-Process. I check if PowerPoint is open, if there is one PowerPoint open and I use the MainWindowTitle property to check that it is the right PowerPoint presentation after nearly starting a presentation for SqlServer module with the dbatools slides!

I don‚Äôt want any distractions when I am presenting. I have the sort of friends who will notice if I get notifications for twitter popping up on my screen and repeatedly send tweets to make people laugh. (I admit, I’m one of those friends – I do this too!)

02 - Friends!!.PNG

Now I cannot get a test for quiet hours working. You can apparently use a Registry key, which of course you can check with PowerShell but I was unable to get it working. I haven’t looked at testing for Presentation Mode ¬†but I test that those programmes are shut down, again using Get-Process

        It "Mail Should be closed" {
            (Get-Process HxMail -ErrorAction SilentlyContinue).Count | Should Be 0
        }
        It "Tweetium should be closed" {
            (Get-Process WWAHost -ErrorAction SilentlyContinue).Count | Should Be 0
        }
        It "Slack should be closed" {
            (Get-Process slack* -ErrorAction SilentlyContinue).Count | Should BE 0
        }

I am generally presenting with SQL Server so I need to make sure that SQL Server is running. I do this with Get-Service

Context "Local SQL" {
        It "DBEngine is running" {
            (Get-Service mssqlserver).Status | Should Be Running
        }
        It "SQL Server Agent is running" {
            (Get-Service sqlserveragent).Status | Should Be Running
        }
        It "DAVE DBEngine is running" {
            (Get-Service mssql*Dave).Status | Should Be Running
        }
        It "DAVE Agent is running" {
            (Get-Service sqlagent*dave).Status | Should Be Running
        }
    }

In this example I am testing that the SQL Service and the Agent service are running on both of my local instances.

I use a NUC running Hyper-V to enable me to show a number of SQL Servers running in a domain environment so I need to be able to test those too. I set the values of the servers I need into a variable and check that the VM is running and that they respond to ping

 Context "VM State" {
        $NUCServers = 'BeardDC1','BeardDC2','LinuxvNextCTP14','SQL2005Ser2003','SQL2012Ser08AG3','SQL2012Ser08AG1','SQL2012Ser08AG2','SQL2014Ser12R2','SQL2016N1','SQL2016N2','SQL2016N3','SQLVnextN1','SQL2008Ser12R2'
        $NUCVMs = Get-VM -ComputerName beardnuc | Where-Object {$_.Name -in $NUCServers}
            foreach($VM in $NUCVms)
                {
                $VMName = $VM.Name
                  It "$VMName Should be Running"{
                    $VM.State | Should Be 'Running'
                  }
			    }
    }
Context "THEBEARD_Domain" {
            $NUCServers = 'BeardDC1','BeardDC2','LinuxvNextCTP14','SQL2005Ser2003','SQL2012Ser08AG3','SQL2012Ser08AG1','SQL2012Ser08AG2','SQL2014Ser12R2','SQL2016N1','SQL2016N2','SQL2016N3','SQLVnextN1','SQL2008Ser12R2'
            foreach($VM in $NUCServers)
                {
                                 It "$VM Should respond to ping" {
				(Test-Connection -ComputerName $VM -Count 1 -Quiet -ErrorAction SilentlyContinue) | Should be $True
				}
                }
    }

I also need to check if the SQL Service and the Agent Service is running on each server

  Context "SQL State" {
        $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*'  -and $_.State -eq 'Running'}).Name
        foreach($Server in $SQLServers)
        {
          $DBEngine = Get-service -ComputerName $Server -Name MSSQLSERVER
           It "$Server  DBEngine should be running" {
                $DBEngine.Status | Should Be 'Running'
            }
           It "$Server DBEngine Should be Auto Start" {
            $DBEngine.StartType | Should be 'Automatic'
           }
              $Agent= Get-service -ComputerName $Server -Name SQLSERVERAGENT
              It "$Server Agent should be running" {
                  $Agent.Status | Should Be 'Running'
           }
           It "$Server Agent Should be Auto Start" {
            $Agent.StartType | Should be 'Automatic'
           }
        }
        It "Linux SQL Server should be accepting connections" {
            $cred = Import-Clixml C:\temp\sa.xml
            {Connect-DbaSqlServer -SqlServer LinuxvnextCTP14 -Credential $cred -ConnectTimeout 60} | Should Not Throw
        }

    }
}

I check that the Linux SQL Server is available by storing the credential using Export-CliXML  and then use that credential with Connect-DbaSqlServer from dbatools

Using a NUC means I sometimes have fun with networking so I have a couple of tests for that too. Testing for the correct DNS Servers and gateways

    It "Should have DNS Servers for correct interface" {
        (Get-DnsClientServerAddress -InterfaceAlias 'Ethernet 3').Serveraddresses | Should Be @('10.0.0.1','10.0.0.2')
    }
    It "Should have correct gateway for alias"{
        (Get-NetIPConfiguration -InterfaceAlias 'Ethernet 3').Ipv4DefaultGateway.NextHop | Should Be '10.0.0.10'
    }

All of those are generic tests that have evolved over time and are run for every presentation but when I have specific things you require for a single presentation I test for those too.

For Example, later this week Cláudio Silva and I are presenting on dbatools at TUGAIT  We are showing the Test-DbaMaxMemory  , Get-DbaMaxMemory and Set-DbaMaxMemory commands so we need to ensure that the Max Memory for some servers is (In) Correctly set. I use Connect-DbaSqlServer to create an SMO Server object and test that

    It "Max Memory on SQl2012SerAG1 2 and 3 should be 2147483647" {
        (Connect-DbaSqlServer SQL2012Ser08AG1).Configuration.MaxServerMemory.RunValue | Should Be 2147483647
        (Connect-DbaSqlServer SQL2012Ser08AG2).Configuration.MaxServerMemory.RunValue | Should Be 2147483647
        (Connect-DbaSqlServer SQL2012Ser08AG3).Configuration.MaxServerMemory.RunValue | Should Be 2147483647
    }

We are also showing the Test-DbaIdentityUsage command so a column needs to be pre-prepared in AdventureWorks2014 to be able to show the error

    It "ShiftID LastValue Should be 255" {
        $a = Test-DbaIdentityUsage -SqlInstance ROB-XPS -Databases AdventureWorks2014 -NoSystemDb
        $a.Where{$_.Column -eq 'ShiftID'}.LastValue | should Be 255
    }

To ensure that we have orphaned files available for the Find-DbaOrphanedFile command I use this

    It "has Orphaned Files ready"{
        (Find-DbaOrphanedFile -SqlServer SQL2016N2).Count | Should Be 30
    }

There are any number of things that you may want to test to ensure that, as best as possible, the demo gods are not going to come and bite you in the middle of your presentation.

  • Files or Folders exist (or dont exist)
  • Databases, Agent Jobs, Alerts
  • Operators, Logins
  • SSIS packages, SSRS Reports
  • PowerBi files
  • Azure connectivity
  • Azure components

The list is endless, just look at what you require for your presentation.

Anything you can check with PowerShell you can test with Pester so build up your Pester presentation tests and reduce the reliance on the demo gods! I’ll still leave this here just to be safe!!

pray to the demo gods.jpg

Pester Test Inception and the Show Parameter

My fantastic friend Andre Kamman b | t  and I presented at PSConfEu last week

C_EDtK0XoAA1PL7 (2).jpg

and whilst we were there we were chatting about running Pester Tests. He wanted to know how he could run a Pester Test and not lose the failed tests as they scrolled past him. In his particular example we were talking about running hundreds of tests on thousands of databases on hundreds of servers

01 - pesters.gif

I guess it looks something like that!!

I explained about the -Show parameter which allows you to filter the results that you see. Using Get-Help Invoke-Pester you can see this

   -Show
Customizes the output Pester writes to the screen. Available options are None, Default,
Passed, Failed, Pending, Skipped, Inconclusive, Describe, Context, Summary, Header, All, Fails.

The options can be combined to define presets.
Common use cases are:

None – to write no output to the screen.
All – to write all available information (this is default option).
Fails – to write everything except Passed (but including Describes etc.).

A common setting is also Failed, Summary, to write only failed tests and test summary.

This parameter does not affect the PassThru custom object or the XML output that
is written when you use the Output parameters.

Required?                    false
Position?                    named
Default value                All
Accept pipeline input?       false
Accept wildcard characters?  false

So there are numerous options available to you. Lets see what they look like

I will use a dummy test which creates 10 Context blocks and runs from 1 to 10 and checks if the number has a remainder when divided by 7

Describe "Only the 7s Shall Pass" {
    $Servers = 0..10
    foreach($Server in $servers)
    {
        Context "This is the context for $Server" {
        foreach($A in 1..10){
            It "Should Not Pass for the 7s" {
                $A % 7 | Should Not Be 0
                }
            }
        }
    }
}

Imagine it is 10 servers running 10 different tests

For the Show parameter All is the default, which is the output that you are used to

02 - All.gif

None does not write anything out. You could use this with -Passthru which will pass ALL of the test results to a variable and if you added -OutputFile and -OutputFormat then you can save ALL of the results to a file for consumption by another system. The -Show parameter only affects the output from the Invoke-Pester command to the host not the output to the files or the variable.

Header only returns the header from the test results and looks like this ( I have included the none so that you can see!)

03 - none and header.PNG

Summary, as expected returns only the summary of the results

04 - summary.PNG

You can use more than one value for the Show parameter so if you chose Header, Summary, Describe you would get this

05 - headerdesscribe sumnmary.PNG

You could use Failed to only show the failed tests which looks like this

06 - failed.PNG

but Andre explained that he also want to be able to see some progress whilst the test was running. If there were no failures then he would not se anything at all.

So Fails might be the answer (or Failed and Summary but that would not show the progress)

07 - fails.PNG

Fails shows the Header, Describe, Context  and also shows the Summary.

However we carried on talking. PSConfEU is a fantastic place to talk about PowerShell ūüôā and wondered what would happen if you invoked Pester from inside a Pester test. I was pretty sure that it would work as Pester is just PowerShell but I thought it would be fun to have a look and see how we could solve that requirement

So I created 3 “Internal Tests” these are the ones we don’t want to see the output for. I then wrote an overarching Pester test to call them. In that Pester test script I assigned the results of¬†each¬†test to a variable which. When you examine it you see

08 - Pester Object.PNG

The custom object that is created shows the counts of all different results of the tests, the time it took and also the test result.

So I could create a Pester Test to check the Failed Count property of that Test result

$InternalTest1.FailedCount | Should Be 0

To make sure that we don’t lose the results of the tests we can output¬† them to a file like this

$InternalTest1 = Invoke-Pester .\Inside1.Tests.ps1 -Show None -PassThru -OutputFile C:\temp\Internal_Test1_Results.xml -OutputFormat NUnitXml

So now we can run Invoke-Pester and point it at that file and it will show the progress and the final result on the screen.

09 finale.PNG

You could make use of this in different ways

  • Server 1
    • Database1
    • Database2
    • Database3
    • Database4
  • Server 2
    • Database1
      Database2
      Database3
      Database4
  • Server 3
    • Database1
      Database2
      Database3
      Database4

Or by Test Category

  • Backup
    • Server1
    • Server 2
    • Server 3
    • Server 4
  • Agent Jobs
    • Server 1
    • Server 2
    • Server 3
    • Server 4
  • Indexes
    • Server 1
    • Server 2
    • Server 3
    • Server 4

Your only limitation is your imagination.

As we have mentioned PSConfEU you really should check out the videos on the youtube channel All of the videos that were successfully recorded will be on there. You could start with this one and mark your diaries for April 16-20 2018

 

 

PowerShell Function – Validating a Parameter Depending On A Previous Parameter’s Value

I was chatting on the SQL Community Slack¬†with my friend Sander Stad b | t¬†about some functions he is writing for the amazing PowerShell SQL Server Community module dbatools. He was asking my opinion as to how to enable user choice or options for Agent Schedules and I said that he should validate the input of the parameters. He said that was difficult as if the parameter was Weekly the frequency values required would be different from if the parameter was Daily or Monthly. That’s ok, I said, you can still validate the parameter.

You can read more about Parameters either online here or here or by running

Get-Help About_Parameters
Get-Help About_Functions_Parameters

You can also find more help information with

Get-Help About_*Parameters*

01 more help.PNG

This is not a post about using Parameters, google for those but this is what I showed him.

Lets create a simple function that accepts 2 parameters Word and Number

 function Test-validation
{
    Param
    (
         [string]$Word,
         [int]$Number
    )
Return "$Word and $Number"
} 

We can run it with any parameters

02 any parameters

If we wanted to restrict the Word parameter to only accept Sun, Moon or Earth we can use the ValidateSetAttribute as follows

 function Test-validation
{
    Param
    (
        [ValidateSet("sun", "moon", "earth")]
        [string]$Word,
        [int]$Number
    )
Return "$Word and $Number"
}

Now if we try and set a value for the $Word parameter that isn’t sun moon or earth then we get an error

03 parameter error.PNG

and it tells us that the reason for the error is that TheBeard! does not belong to the set sun, moon, earth.

But what Sander wanted was to validate the value of the second parameter depending on the value of the first one. So lets say we wanted

  • If word is sun, number must be 1 or 2
  • If word is moon, number must be 3 or 4
  • If word is earth, number must be 5 or 6

We can use the ValidateScriptAttribute  to do this. This requires a script block which returns True or False. You can access the current parameter with $_ so we can use a script block like this

{
    if($Word -eq 'Sun'){$_ -eq 1 -or $_ -eq 2}
    elseif($Word -eq 'Moon'){$_ -eq 3 -or $_ -eq 4}
    elseif($Word -eq 'earth'){$_ -eq 5 -or $_ -eq 6}
}

The function now looks like

function Test-validation
{
    Param
    (
        [ValidateSet("sun", "moon", "earth")]
        [string]$Word,
        [ValidateScript({
            if($Word -eq 'Sun'){$_ -eq 1 -or $_ -eq 2}
            elseif($Word -eq 'Moon'){$_ -eq 3 -or $_ -eq 4}
            elseif($Word -eq 'earth'){$_ -eq 5 -or $_ -eq 6}
        })]
        [int]$Number
    )
Return "$Word and $Number"
}

It will still fail if we use the wrong “Word” in the same way but now if we enter earth and 7 we get this

04 parameter error.PNG

But if we enter sun and 1 or moon and 3 or earth and 5 all is well

05 working

I would add one more thing. We should always write PowerShell functions that are easy for our users to self-help. Of course, this means write good help for the function. here is a great place to start from June Blender

06 June.PNG

In this example, the error message

Test-validation : Cannot validate argument on parameter ‘number’. The ”
if($word -eq ‘Sun’){$_ -eq 1 -or $_ -eq 2}
elseif($word -eq ‘Moon’){$_ -eq 3 -or $_ -eq 4}
elseif($word -eq ‘earth’){$_ -eq 5 -or $_ -eq 6}
” validation script for the argument with value “7” did not return a result of True. Determine why the validation script failed, and then try the
command again.
At line:1 char:39
+ Test-validation -Word “earth” -number 007
+                                       ~~~
+ CategoryInfo          : InvalidData: (:) [Test-validation], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,Test-validation

is not obvious to a none-coder so we could make it easier. As we are passing in a script block we can just add a comment like this. I added a spare line above and below to make it stand out a little more

function Test-validation
{
    Param
    (
        [ValidateSet("sun", "moon", "earth")]
        [string]$Word,
        [ValidateScript({
            #
            # Sun Accepts 1 or 2
            # Moon Accepts 3 or 4
            # Earth Accepts 5 or 6
            #
            if($Word -eq 'Sun'){$_ -eq 1 -or $_ -eq 2}
            elseif($Word -eq 'Moon'){$_ -eq 3 -or $_ -eq 4}
            elseif($Word -eq 'earth'){$_ -eq 5 -or $_ -eq 6}
        })]
        [int]$Number
    )
Return "$Word and $Number"
}

Now if you enter the wrong parameter you get this

07 more help.PNG

which I think makes it a little more obvious

 

Setting the default file type for a new file in VS Code

Just a short post today. When you open a new file in VS Code (Using CTRL + N) it opens by default as a plain text file.

To change the language for the file use CTRL +K, M.

That’s CTRL and K together and then M afterwards separately.

then you can choose the language for the file. It looks like this

01 - Change language

However, if you just want your new file to open as a particular language every time you can change this in the settings.

Click File –> Preferences –> Settings

or by clicking CTRL + ,

02 - Open Preferences.PNG

This opens the settings.json file. Search in the bar for default and scroll down until you see file

03 - File defaults.PNG

If you hover over the setting that you want to change, you will see a little pencil. Click on that and then Copy to Settings which will copy it to your user settings in the right hand pane.

NOTE – You will need to enter powershell and not PowerShell. For other languages, click on the language in the bottom bar and look at the value in the brackets next to the language name

04 - langauge.PNG

Once you have entered the new settings save the file (CTRL + S) and then any new file you open will be using the language you have chosen

It looks like this

05 - Change settings.gif

and now every new file that you open will be opened as a PowerShell file (or whichever language you choose)

You will still be able to change the language with CTRL K, m

Just to be clear, because people sometimes get this wrong. That’s CTRL and K, let go and then M. You will know you are doing correctly when you see

(CTRL + K) was pressed waiting for second key of chord……

06 - waiting for key

If you get it wrong and Press CTRL + K + M then you will open the Extensions search for keymaps.

 

07 - incorrect.PNG

This is a brilliant feature enabling you to copy key mappings for the programmes you use all the time and save you from learning the Code key mappings. You can find the keymaps in the Extensions Marketplace as well as by pressing CTRL + K + M