Run Glenn Alan Berry’s Diagnostic Notebooks Everywhere

As part of starting a new job, you need a way to get a good inventory of basic information about SQL Server instances.  Once you have done what I outlined in this blog post.  I find it helpful to run Glenn Alan Berry’s Diagnostic Notebooks against all the instances to get a static point-in-time snapshot of all the properties and some performance information.  While dbatools has commands under the Community Tools section for running the data into spreadsheets and creating notebooks for the newest queries I like to go get Glenn’s because he has all the comments in there of what they mean and links to resources about things.  So, you can explore that route if you like but I’ll be manually downloading them from Glenn’s site for that reason.  To able to open the notebooks successfully in ADS look for the tip on my blog post on Tools I Use on My Jumpbox for opening large notebooks.

So first download his notebooks and name them according to their version, I use the naming scheme:

• SQL Server 2014 – Diag2014
• SQL Server 2016 – Diag2016
• SQL Server 2017 – Diag2017
• SQL Server 2019 – Diag2019
• Azure SQL DB – DiagAzure

The code to this is very simple but requires PowerShell 5.1 and will not work with PowerShell 7 because the Invoke-SqlNotebook function does not work in PowerShell 7.  I’ll write a follow up blog post on how to do this with dbatools with Glenn’s nice comments in there.

Import-Module -Name SqlServer -Force
Import-Module dbatools

$CMSServer = "CMSServerName"
$SQLInstances = Get-DbaRegServer -SQLInstance $CMSServer
$servers = @($CMSServer)

ForEach ($s in $SQLInstances) {
    $connection = Test-DbaConnection -SQLInstance $s.name

    if ($connection.ConnectSuccess -and $s.name -notin $servers) {
        $servers += $s.name
    }
}

Foreach ($s in $servers) {
    $connection = Test-DbaConnection -SQLInstance $s

    if ($connection.ConnectSuccess) {
        $version = $connection.SqlVersion.ToString()

        if ($version.substring(0, 2) -eq 12) { # SQL 2014
            $InputFile = "c:\temp\Diag2014.ipynb"
        } elseif ($version.substring(0, 2) -eq 14) { # SQL 2017
            $InputFile = "c:\temp\Diag2017.ipynb"
        } elseif ($version.substring(0, 2) -eq 15) { # SQL 2019
            $InputFile = "c:\temp\Diag2019.ipynb"
        } else {
            Write-Error "Unknown version of SQL"
            $InputFile = ""
        }

        if ($InputFile -ne "") {
            try {
                Write-Host "Create notebook for server: $($s)"
                Invoke-SqlNotebook -ServerInstance $s -InputFile $InputFile -OutputFile "C:\temp\Diag$($s).ipynb" -Force
            } catch {
                Write-Error "Error creating notebook on Server: $($s)"
            }
        }
    }
}