In my previous posts about writing your first Pester Test and looping through instances I described how you can start to validate that your SQL Server is how YOU want it to be.
Unavailable machines
Once you begin to have a number of tests for a number of instances you want to be able to handle any machines that are not available cleanly otherwise you might end up with something like this.
In this (made up) example we loop through 3 instances and try to check the DNS Server entry is correct but for one of them we get a massive error and if we had created a large number of tests for each machine we would have a large number of massive errors.
Empty Collection
If we don’t successfully create our collection we might have an empty collection which will give us a different issue. No tests
If this was in amongst a whole number of tests we would not have tested anything in this Describe block and might be thinking that our tests were OK because we had no failures of our tests. We would be wrong!
Dealing with Empty Collections
One way of dealing with empty collections is to test that they have more than 0 members
if ($instances.count -gt 0) {
$instances.ForEach{
## Tests in here
}
}
else {Write-Warning "Uh-Oh - The Beard is Sad! - The collection is empty. Did you set `$Instances correctly?"}
Dealing with Unavailable Machines
If we want to make sure we dont clutter up our test results with a whole load of failures when a machine is unavailable we can use similar logic.
First we could check if it is responding to a ping (assuming that ICMP is allowed by the firewall and switches) using
Test-Connection -ComputerName $computer -Count 1 -Quiet -ErrorAction SilentlyContinue
This will just try one ping and do it quietly only returning True or False and if there are any errors it shouldn’t mention it
In the example above I am using PSRemoting and we should make sure that that is working too. So whilst I could use
Test-WSMan -ComputerName $computer
this only checks if a WSMAN connection is possible and not other factors that could be affecting the ability to run remote sessions. Having been caught by this before I have always used this function from Lee Holmes (Thank you Lee) and thus can use
$instances.ForEach{
$computer = $_.Split('\')[0]# To get the computername if there is an instance name
# Check if machine responds to ping
if (!(Test-Connection-ComputerName $computer-Count 1-Quiet -ErrorAction SilentlyContinue))
{Write-Warning "Uh-Oh - $Computer is not responding to a ping - aborting the tests for this machine"; Return}
# Check if PSremoting is possible for this machine
# Requires Test-PSRemoting by Lee Holmes http://www.leeholmes.com/blog/2009/11/20/testing-for-powershell-remoting-test-psremoting/
if (!(Test-PsRemoting$computer))
{Write-Warning "Uh-Oh - $Computer is not able to use PSRemoting - aborting the tests for this machine"; Return}
Describe "Testing Instance $($_)" {
## Put tests in here
}
Which is much better I think 
Let dbatools do the error handling for you
If your tests are only using the dbatools module then there is built in error handling that you can use. By default dbatools returns useful messages rather than the exceptions from PowerShell (You can enable the exceptions using the -EnableExceptions parameter if you want/need to) so if we run our example from the previous post it will look like
which is fine for a single command but we don’t really want to waste time and resources repeatedly trying to connect to an instance if we know it is not available if we are running multiple commands against each instance.
dbatools at the beginning of the loop
We can use Test-DbaConnection to perform a check at the beginning of the loop as we discussed in the previous post
$instances.ForEach{
if (!((Test-DbaConnection-SqlInstance $_ -WarningAction SilentlyContinue).ConnectSuccess))
{Write-Warning "Uh-Oh - we cannot connect to $_ - aborting the tests for this instance"; Return}
Round Up
In this post we have covered some methods of ensuring that your Pester Tests return what you expect. You don’t want empty collections of SQL Instances making you think you have no failed tests when you have not actually run any tests.
You can do this by checking how many instances are in the collection
You also dont want to keep running tests against a machine or instance that is not responding or available.
You can do this by checking a ping with Test-Connection or if remoting is required by using the Test-PSRemoting function from Lee Holmes
If you want to use dbatools exclusively you can use Test-DbaConnection
Here is a framework to put your tests inside. You will need to provide the values for the $Instances and place your tests inside the Describe Block
if ($instances.count -gt 0) {
$instances.ForEach{
$TestConnection = Test-DbaConnection-SqlInstance $_ -WarningAction SilentlyContinue
# Check if machine responds to ping
if (!($TestConnection.IsPingable))
{Write-Warning "Uh-Oh - The Beard is Sad! - - $_ is not responding to a ping - aborting the tests for this instance"; Return}
# Check if we have remote access to the machine
if (!($TestConnection.PsRemotingAccessible))
{Write-Warning "Uh-Oh - The Beard is Sad! - - $_ is not able to use PSRemoting - aborting the tests for this instance"; Return}
# Check if we have SQL connection to the Instance
if (!($TestConnection.ConnectSuccess))
{Write-Warning "Uh-Oh - The Beard is Sad! - - we cannot connect to SQL on $_ - aborting the tests for this instance"; Return}
Describe "Testing Instance $($_)" {
## Now put your tests in here - seperate them with context blocks if you want to
Context "Networks" { }
}
}
}
else
## If the collection is empty
{Write-Warning "Uh-Oh - The Beard is Sad! - The collection is empty. Did you set `$Instances correctly?"}