After spending far to long last week trying to work out why something wasn’t working (hint it was a configuration issue!), i decided its time to bring Pester into play (If you want to learn Pester i strongly suggest looking at Testing PowerShell with Pester by Robert Cain on Pluralsight).

Its pretty easy to get started

  • Document your configuration (hopefully you already have this!)
  • Create a test for each configuration change
NOTE: Some may say you dont need that document of the configuration as you have the tests, that's up to you to decide. I prefer keeping both.

If you don’t have any an ideas what your configuration is try looking at something like sp_blitz to get you started (it wont check the windows level, but it covers most SQL instance level configurations and database settings).

My configuration was something like below

  • SQL Services
    • SQL Server engine should be running
  • Windows Configuration
    • DTC Enabled
  • SQL Server Configuration
    • testDB should be online and in multi-user mode
    • CLR Enabled
    • Login needs specific instance level permissions
  • Database Configuration
    • owned by SA
    • Trustworthy on

I had a few clear levels of test, so broke them into Contexts, (Again i can see a point of breaking this into individual scripts, but for the sake of simplicity there are all in one.)

On a first attempt I ended up with something like below, which hopefully you can use a starting point to build out tests for your own environments / get started with Pester.

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

Import-Module sqlserver

Describe "SQL Configuration Tests" {

   Context 'SQL Server Services' {

        It "The SQL Server service should be running" {
            (Invoke-Command -Session $Session {Get-Service -Name MSSQLSERVER}).status |
            Should be 'Running'
   } # Context end SQL Server Installation Checks

   Context 'MSDTC Configuration' {

    $RegPath = “HKLM:\SOFTWARE\Microsoft\MSDTC\”
    $RegSecurityPath = “$RegPath\Security”

        It "DTC should have No Authentication Required" {
            (Invoke-Command -Session $Session {Get-ItemProperty –path $args[0] –name “AllowOnlySecureRpcCalls”} -ArgumentList $RegPath).AllowOnlySecureRpcCalls |
            Should be 0
            (Invoke-Command -Session $Session {Get-ItemProperty –path $args[0] –name “TurnOffRpcSecurity”} -ArgumentList $RegPath).TurnOffRpcSecurity |
            Should be 1
        It "DTC Account Name should Contain NT AUTHORITY" {
            (Invoke-Command -Session $Session {Get-ItemProperty –path $args[0] –name “AccountName”} -ArgumentList $RegSecurityPath).AccountName |
            Should be "NT AUTHORITY\NetworkService"
        It "DTC Network DTC Access should be enabled" {
            (Invoke-Command -Session $Session {Get-ItemProperty –path $args[0] –name “NetworkDtcAccess”} -ArgumentList $RegSecurityPath).NetworkDtcAccess |
            Should be 1
        It "DTC Allow Remote Clients should be enabled" {
            (Invoke-Command -Session $Session {Get-ItemProperty –path $args[0] –name “NetworkDtcAccessClients”} -ArgumentList $RegSecurityPath).NetworkDtcAccessClients |
            Should be 1
        It "DTC Allow Inbound should be enabled" {
            (Invoke-Command -Session $Session {Get-ItemProperty –path $args[0] –name “NetworkDtcAccessInbound”} -ArgumentList $RegSecurityPath).NetworkDtcAccessInbound |
            Should be 1
        It "DTC Allow outbound should be enable" {
            (Invoke-Command -Session $Session {Get-ItemProperty –path $args[0] –name “NetworkDtcAccessOutbound”} -ArgumentList $RegSecurityPath).NetworkDtcAccessOutbound |
            Should be 1
        It "DTC Enable SNA LU 6.2 Transactions should be enabled" {
            (Invoke-Command -Session $Session {Get-ItemProperty –path $args[0] –name “LuTransactions”} -ArgumentList $RegSecurityPath).LuTransactions |
            Should be 1
        It "DTC NetworkDtcAccessTransactions should be enabled" {
            (Invoke-Command -Session $Session {Get-ItemProperty –path $args[0] –name “NetworkDtcAccessTransactions”} -ArgumentList $RegSecurityPath).NetworkDtcAccessTransactions |
            Should be 1

   } # Context end Windows Configuration

   Context 'SQL Server Configuration' {
        It "database should be available" {
            $testDBQuery = "SELECT 1 as 'Answer' FROM sys.databases WHERE name = 'testDB' AND state_desc = 'ONLINE'"
            (Invoke-Sqlcmd -ServerInstance $ServerName -query $testDBQuery).Answer |
            Should be "1"
        It "CLR should be enabled" {
            $clrQuery = "SELECT value_in_use as 'Answer' FROM sys.configurations WHERE name = 'clr enabled'"
            (Invoke-Sqlcmd -ServerInstance $ServerName -query $clrQuery).Answer |
            Should be "1"
        It "testDB_user should have VIEW SERVER STATE" {
            $testDBPublicQuery = "SELECT  1 AS 'Answer'
            FROM    sys.[server_permissions] PER
                    INNER JOIN sys.[server_principals] PRIN ON PER.[grantee_principal_id] = PRIN.[principal_id]
            WHERE   PER.[permission_name] = 'VIEW SERVER STATE'
                    AND ( PER.[state] = 'G'
                          OR PER.[state] = 'W'
            AND PRIN.[name] = 'testDB_user'
            ORDER BY PRIN.[name];"
            (Invoke-Sqlcmd -ServerInstance $ServerName -query $testDBPublicQuery).Answer |
            Should be "1"

   } # Context SQL Server Configuration

   Context 'testDB Configuration' {
        It "testDB should be owned by SA" {
            $testDBsaQuery = "SELECT 1 AS 'Answer' FROM sys.databases WHERE owner_sid = 0x01 AND name = 'testDB'"
            (Invoke-Sqlcmd -ServerInstance $ServerName -query $testDBsaQuery).Answer |
            Should be "1"
        It "testDB trustworthy should be on" {
            $testDBtrustworthyQuery = "SELECT 1 AS 'Answer' FROM sys.databases WHERE name = 'testDB' AND is_trustworthy_on = 1"
            (Invoke-Sqlcmd -ServerInstance $ServerName -query $testDBtrustworthyQuery).Answer |
            Should be "1"
   } # Context testDB Configuraiton

} # Describe

Remove-PSSession -Session $Session

To call the test you would save this file (in my case as “SQL Configuration.Tests.ps1” and the run the below pointing to the save file.

Import-Module Pester

$testsFolder = 'C:\SourceTree\DBA\Scripts\PowerShell\Pester'
Set-Location  $testsFolder 

# Run tests for each file
Invoke-Pester "$testsFolder\SQL Configuration.Tests.ps1"