Powershell – Baten ICT

When your pc is using a proxy server to connect to the internet, the following needs to be added to your Powershell profile. Otherwise, you won’t be able to register any Powershell repository. This means you cannot use the commandlet Install-Module to install modules.

Open an elevated Powershell prompt and type:

if (-not (Test-Path -Path $PROFILE)) { New-Item -Path $PROFILE -ItemType File -Force }
notepad $PROFILE

1 2	if (-not (Test-Path -Path $PROFILE)) { New-Item -Path $PROFILE -ItemType File -Force } notepad $PROFILE

This will create a new profile when not available, and opens a Notepad for you. Paste the following into your profile and save the file:

[System.Net.WebRequest]::DefaultWebProxy = New-Object System.Net.WebProxy('yourproxy:port')
[System.Net.WebRequest]::DefaultWebProxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
[System.Net.WebRequest]::DefaultWebProxy.BypassProxyOnLocal = $true

[System.Net.WebRequest]::DefaultWebProxy = New-Object System.Net.WebProxy('yourproxy:port')

[System.Net.WebRequest]::DefaultWebProxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials

[System.Net.WebRequest]::DefaultWebProxy.BypassProxyOnLocal = $true

Now close your Powershell console, and start a new elevated session. Now it should be possible to register the default repository and you can install any module using Install-Module:

Register-PSRepository -Default –Verbose

1	Register-PSRepository -Default –Verbose

Sometimes you want to check something in your SQL environment that’s not yet included in the dbachecks module. Luckily, it is quite easy to extend it’s functionality.

Let’s start by creating a file that will contain your own checks:

New-Item -Path c:\MyChecks\ContosoChecks.Tests.ps1 -ItemType File -Force

1	New-Item -Path c:\MyChecks\ContosoChecks.Tests.ps1 -ItemType File -Force

Now add the following to the newly created file:

$filename = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", "")

@(Get-Instance).ForEach{

    Describe "Contoso DBA Database Configuration" -Tags Contoso, $filename {
        Context -Name "DBA database on $psitem" {
            It "DBA database should be present" {
                (Get-DbaDatabase -SqlInstance $psitem -Database DBA | Measure-Object).Count |
                    Should -Be 1 -Because "It should be there"
            }

            It "DBA should contain sp_WhoIsActive" {
                ((Get-DbaDatabase -SqlInstance $psitem -Database DBA).StoredProcedures | Where-Object -Property Name -eq 'sp_WhoIsActive' | Measure-Object).Count |
                    Should -Be 1 -Because "It should be there"
            }
        }
    }
}

$filename = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", "")

@(Get-Instance).ForEach{

Describe "Contoso DBA Database Configuration" -Tags Contoso, $filename {

Context -Name "DBA database on $psitem" {

It "DBA database should be present" {

(Get-DbaDatabase -SqlInstance $psitem -Database DBA | Measure-Object).Count |

Should -Be 1 -Because "It should be there"

}

It "DBA should contain sp_WhoIsActive" {

((Get-DbaDatabase -SqlInstance $psitem -Database DBA).StoredProcedures | Where-Object -Property Name -eq 'sp_WhoIsActive' | Measure-Object).Count |

Should -Be 1 -Because "It should be there"

}

In the Describe block I added ‘Contoso’ to the Tags parameter. You can use this tag to include (or exclude) specific tests.

All that’s left to do is to register your custom repository:

Set-DbcConfig -Name app.checkrepos -Value 'c:\MyChecks\' -Append

1	Set-DbcConfig -Name app.checkrepos -Value 'c:\MyChecks\' -Append

Now you can invoke the checks by running:

Invoke-DbcCheck -SqlInstance localhost\SQL2017 -ComputerName localhost -Tags Contoso

1	Invoke-DbcCheck -SqlInstance localhost\SQL2017 -ComputerName localhost -Tags Contoso

Category: Powershell

Powershell Gallery and proxy servers

Dbachecks: How to add a custom repository