Quick update to my last post on Pester Testing your SQL Server Configurations.

While this worked great for getting started, it quickly become clear that having the hard coded server value at the top of the test file was limiting to say the least. Thanks to the help of Dave Wyatt at powershell.org i switched to using a parameter in the test file allowing us to run the test against any server, which meant i could run it against each of the dev/uat/staging/int and ensure they all meet my standards.

While i was at it, i updated the test file to be able to work on named instances, this just needed a check on the $ServerName to see if it contains a \ for the named instance.

The top of the test script now looks like:

<# SQL Configuration Tests #>
Param (
[string]$ServerName
)

# Work out if its a named instance and set $ServerName to Server name (invoke-commands) and $Server to full servername and instance (invoke-sqlcmd)
IF(!($Server.contains("\")))
    {
        $ServerName = $Server
    }
ELSE
    {
        ## named instance needs to be split out
        $ServerInstance = $Server.Split("{\}")
        $ServerName = $ServerInstance.Item(0)
    }

$Session = New-PSSession -ComputerName $ServerName

Import-Module sqlserver

Describe "Trade SQL Configuration Tests" {

Instead of

<# SQL Configuration Tests #>
$ServerName = 'Server01'
$Session = New-PSSession -ComputerName $ServerName

Import-Module sqlserver

Describe "SQL Configuration Tests" {

and to call the tests you would use

Invoke-Pester -Script @{Path = "$testsFolder\Trade SQL Configuration.Tests.ps1"; Parameters = @{Server = 'DEV-SQL02\Instance2'}}
Advertisements