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

Posted by Nikos Tsirmirakis on

In my previous post, I have described how to create a cloud lab environment with SQL Server VM (IaaS) based on Microsoft image from Azure Marketplace. In this post, we will run it as part of CI/CD pipeline and run Pester tests to validate the configuration. All scripts have been updated to Terraform 0.14 and Pester 5, and are available in the DBAinTheCloud GitHub repository.

Pipelines

First, we will create a pipeline with two stages, to build and destroy the entire environment with Domain Controler, Azure build agent and SQL server.

Build stage will include three jobs. First to execute Terraform scripts to build environment.

The second job will run Pester tests on Azure agent to validate Azure configuration and check if connectivity from a public IP address is blocked.

The third job will run Pester tests on a local agent. We have installed this agent to make sure that we will have access to the database server and will be able to validate the SQL Server configuration.

Tests

For the running test, we will use the new version of Pester 5. There are many changes including tests definitions and way how pester is executed. For backward compatibility, we can still execute it using command switches.

 
$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"
              

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

 
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
              

We will run three types of tests.

  1. Azure configuration test
  2. Connectivity test
  3. SQL configuration test

Azure configuration test we will run on Azure hosted agent within Azure PowerShell script to execute it against out Azure subscription. We will check the following settings:

  • Does the resource group exist?
  • Is NSG (network security group) allowing connections from trusted IP address?
  • Are all server created?
  • Are all server created with correct size?
  • Is the server count correct?
          
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  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 " allows access from " {

            $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  does exist" {

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

            $server_exist | should -Be 1
        }

        it "Server  size is " {

            $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_counts_test = (Get-AzVM -ResourceGroupName $rg_name).count

            $server_counts_test | should -Be $server_count
        }
    }
}
        

Connectivity test we will run on Azure hosted agent within Azure PowerShell script to obtain a list of public IP addresses allocated to our VMs and then we will test if the connection to RDP port (tcp\3389) is open. We are initiating connections from Azure build agent with randomly assign a public IP address.

          
$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"
    }
}
        

We are not allowing public access to SQL server and we will have to run tests on the local agent. It will allow us to connect to the SQL server and check the following settings:

  • SQL connectivity
  • SQL configuration
  • SQL authentication mode
  • Services configuration
          
$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:  is " {

        $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:  is " {

        $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

    }
}
        

Each test job we will finish with the task to publish test results to Azure DevOps.

Congratulations!

After successfully running the pipeline, you have created your lab environment, run tests and publish it back to Azure DevOps. Check tests tab for details.

Running costs

Please find below the estimated monthly cost for running our lab in the West Europe region.

1 h 9-17 Mon-Fri 24/7
Lab (Demo 9) £ 0.23 £ 38.09 £ 160.70

Coming next …

We will create a much more lean environment with SQL server. The current lab has many moving parts and it takes more than 25 minutes to build it. If we are testing SQL server only and do not require Active Directory we can streamline the process.