How to build and test SQL server within Azure DevOps CI/CD pipeline

$path = "$($env:system_DefaultWorkingDirectory)\dm9\tests"

$p = Invoke-Pester "$($path)\tests-after-build.tests.ps1" -Passthru
$p | Export-NUnitReport -Path "$($path)\tests-after-build-results.xml"

$p = Invoke-Pester "$($path)\test-connectivity-from-internet.tests.ps1" -Passthru
$p | Export-NUnitReport -Path "$($path)\tests-connectivity-from-internet.xml"
              </pre></code>
          </div>

          <p>In our case, we will run Pester tests with new syntax and use of PesterConfiguration parameter.</p>

          <div class="container w-90">
            <pre><code class="powershell"> 
Import-Module Pester -MinimumVersion 5.0.0

$path = "$($env:system_DefaultWorkingDirectory)\dm9\tests"

$config = [PesterConfiguration]::Default

$config.Output.Verbosity = 'Detailed'
$config.Run.Path = "$($path)\azure-tests-after-destroy.tests.ps1"
$config.TestResult.Enabled = $true
$config.TestResult.OutputPath = "$($path)\testResults.xml"

Invoke-Pester -Configuration $config

BeforeAll {
    $rg_name = "a-d1-dm9-rg"
    $nsg_name = "a-d1-dm9-nsg"
    $allowed_public_IP = "x.x.x.x"
    $server_count = 3
}

$servers = @(
    @{ name = 'a-d1-dm9-dc1'; size = 'Standard_B1ms' }
    @{ name = 'a-d1-dm9-ado1'; size = 'Standard_B1ms' }
    @{ name = 'a-d1-dm9-sql1'; size = 'Standard_E2as_v4' }
)

describe 'Azure_congifuration' {

    context "Check if recource group exist" {

        it "Resource group <rg_name> exist" {

            $rg_exist = (Get-AzResourceGroup | Where-Object { $_.ResourceGroupName -eq $rg_name }).count

            $rg_exist | should -Be 1
        }
    }

    context "Check NSG rules - if allow access from home IP" {

        It "<nsg_name> allows access from <allowed_public_IP>" {

            $NSG = Get-AzNetworkSecurityGroup -name $nsg_name -ResourceGroupName $rg_name
        
            $access = ($NSG.SecurityRules | where-object { ($_.Direction -eq "Inbound") -and ($_.SourceAddressPrefix -contains $allowed_public_IP) -and ($_.DestinationPortRange -contains "3389") }).access

            $access | Should -Be 'Allow'
        }
    }

    context "Server(s) check" -ForEach $servers {

        it "Server <name> does exist" {

            $server_exist = (Get-AzVM | Where-Object { ($_.ResourceGroupName -eq $rg_name) -and ($_.Name -eq $name) }).count

            $server_exist | should -Be 1
        }

        it "Server <name> size is <server_size>" {

            $server_size_test = (Get-AzVM -ResourceGroupName $rg_name -Name $name).HardwareProfile.VmSize

            $server_size_test | should -Be $size
        }
    }

    context "Server(s) count check" {
        
        it "Server(s) count is <server_count>" {
        
            $server_counts_test = (Get-AzVM -ResourceGroupName $rg_name).count

            $server_counts_test | should -Be $server_count
        }
    }
}

$rg_name = "a-d1-dm9-rg"
$public_IPs = (Get-AzPublicIpAddress -ResourceGroupName $rg_name).ipaddress

context "RDP port (tcp\3389) is not accesible from the Internet" -ForEach $public_IPs {

    it "Unable to connect to public IP <_> on port 3389" {

        $connection_test = Test-NetConnection -ComputerName $_ -port 3389 -InformationLevel Quiet

        $connection_test | should -Be "False"
    }
}

$sql_configuration = @(
    @{ name = 'remote admin connections'; value = 1 }
    @{ name = 'backup compression default'; value = 1 }
    @{ name = 'max degree of parallelism'; value = 1 }
    @{ name = 'cost threshold for parallelism'; value = 50 }
    @{ name = 'max server memory (MB)'; value = 13312 }
)

$sql_service = @(
    @{ name = 'MSSQLSERVER'; status = 'Running' }
    @{ name = 'SQLBrowser'; status = 'Stopped' }
    @{ name = 'SQLSERVERAGENT'; status = 'Running' }
    @{ name = 'SQLTELEMETRY'; status = 'Stopped' }
    @{ name = 'SSASTELEMETRY'; status = 'Stopped' }
    @{ name = 'SQLWriter'; status = 'Running' }
    @{ name = 'MSSQLFDLauncher'; status = 'Running' }
    @{ name = 'MSSQLLaunchpad'; status = 'Stopped' }
    @{ name = 'MSSQLServerOLAPService'; status = 'Stopped' }
)

BeforeAll {
    $sql_instance = "a-d1-dm9-sql1,6007"
    $server_name = $sql_instance.Split(',')[0]
}

context "sql_configuration" -ForEach $sql_configuration {

    it "setting name: <name> is <value>" {

        $sql = @"
select value_in_use from sys.configurations
where name = '$($name)'
"@

        $current_value = (Invoke-Sqlcmd -ServerInstance $sql_instance -Query $sql).value_in_use # -As DataSet

        $current_value | should -Be $value
    }
}

context "sql_service" -ForEach $sql_service {

    it "service name: <name> is <status>" {

        $current_ststus = (Get-Service -ComputerName $server_name -Name $name -ErrorAction SilentlyContinue).status

        $current_ststus | should -Be $status

    }
}

context "sql_connectivity" {

    it "is working" {
        
        $server_name = (Invoke-Sqlcmd -ServerInstance $sql_instance -Query "select @@servername as server_name").server_name # -As DataSet

        $server_name | should -Be $server_name

    }
}

context "sql_authentication" {

    it "is mixed" {
        
        $sql_authentication = (Invoke-Sqlcmd -ServerInstance $sql_instance -Query "SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') as MixedAuthentication").MixedAuthentication

        $sql_authentication | should -Be 0

    }
}