How to create and customise SQL Server VM in Azure with use of Terraform

Posted by Nikos Tsirmirakis on

In my previous post, I have described how to create a cloud lab environment with Azure DevOps Agent and Domain Controller in Azure with use of Terraform. In this post, we will create SQL Server VM (IaaS) based on Microsoft image from Azure Marketplace with use of Terraform. And customise it with PowerShell and SQL scripts. SQL Server on virtual machines is closest to the on-premises installation of SQL server. If we can not use Azure native services like Azure SQL Databases or Azure SQL Manage Instance it is our only option to migrate the workload to the cloud. There might be multiple reasons why we have to configure SQL Server on a VM like compatibility when cloud-native services are not on vendor supported matrix and this is our only option. Consistency - we would like to replicate our on-premises configuration in a cloud for testing, development or migration purposes.

All scripts are available in the DBAinTheCloud GitHub repository.

Creating SQL Server VM

Microsoft is providing multiple images with a pre-installed SQL Server with latest patches. Azure VM image is identified with 4 parameters in terraform script (doc). We can run the following script in Azure PowerShell console to check image details (more details how to check it you can find in Microsoft documentation).


$location  = "westeurope"
$publisher = "MicrosoftSQLServer"
$offer     = "sql2019-ws2019"
Get-AzVMImageSku -Location $location -PublisherName $publisher -Offer $offer | Select Skus
  • publisher - Specifies the publisher of the image, all Microsoft SQL servers are published by "MicrosoftSQLServer" and this is the value we will use in our configuration
  • offer - Specifies the offer of the image, in our config we will use "sql2019-ws2019"
  • sku - Specifies the SKU of the image, in our config we will use "SQLDEV"
  • version - Specifies the version of the image, in our config we will use "latest" 

storage_image_reference {
  publisher = "MicrosoftSQLServer"
  offer     = "sql2019-ws2019"
  sku       = "SQLDEV"
  version   = "latest"
}

We can also install SQL Server from scratch on a “regular” VM however, in that case, we won’t be taking advantage of pre-cooked images from Microsoft and we will have to take care of patch management and initial configuration.

Server customization - OS level

For server OS level customisation we will use PowerShell and PowerShellDSC which we will execute with VM custom script extension. To streamline the configuration process we will use PowerShell module NetworkingDsc and SqlServerDsc. We will configure the following settings, including SQL settings which are configured on OS level.

  • Enable remote admin connections
  • Enable backup compression default
  • Update cost threshold for parallelism
  • Update SQL Server MaxDop
  • Update SQL Server Max Memory
  • Disable not required SQL services
  • Change SQL Server authentication mode to mixed mode
  • Change SQL Server port
  • Open local firewall port for SQL Server

Below you can find a fragment of a script we are using for SQL Server configuration, the entire script can be found in my repository (sql-config-dsc.ps1)


SqlConfiguration remote_admin_connections
{
    Servername   = $NodeName
    InstanceName = $InstanceName
    OptionName   = 'remote admin connections'
    OptionValue  = 1
}
SqlConfiguration backup_compression_default
{
    Servername   = $NodeName
    InstanceName = $InstanceName
    OptionName   = 'backup compression default'
    OptionValue  = 1
}
SqlConfiguration cost_threshold_for_parallelism
{
    Servername   = $NodeName
    InstanceName = $InstanceName
    OptionName   = 'cost threshold for parallelism'
    OptionValue  = 50
}

SqlMaxDop SetSQLServerMaxDop
{
    Ensure       = 'Present'
    DynamicAlloc = $false
    MaxDop       = $MaxDop
    ServerName   = $NodeName
    InstanceName = $InstanceName
}

SqlMemory SetSQLServerMaxMemory
{
    Ensure       = 'Present'
    DynamicAlloc = $false
    MaxMemory    = $MaxMemor
    ServerName   = $NodeName
    InstanceName = $InstanceName
}

ServiceSet SQLServiceSetToManualAndStop {
    Name        = @("MsDtsServer150", "SSISTELEMETRY150", "SQLTELEMETRY", "SSASTELEMETRY", "MSSQLServerOLAPService")
    StartupType = "Manual"
    State       = "Stopped"
}

ServiceSet SQLServiceSetToAutomaticAndStart {
    Name        = @("SQLSERVERAGENT")
    StartupType = "Automatic"
    State       = "Running"
}

Registry ConfigureSQLMixedAuthentication {
    Ensure    = "Present"
    Key       = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer"
    ValueName = "LoginMode"
    ValueData = "2"
    ValueType = "Dword"
}

SqlProtocolTcpIP 'ChangeIPAll'
{
    InstanceName           = $InstanceName
    IpAddressGroup         = 'IPAll'
    TcpPort                = $SQLport
}

Firewall Firewall4SQL
{
    Name                  = 'SQL-tcp-' + $SQLport
    DisplayName           = 'SQL-tcp-' + $SQLport
    Ensure    = 'Present'
    Enabled   = 'True'
    Direction = 'InBound'
    LocalPort = $SQLport
    Protocol  = 'TCP'
    Description           = 'SQL access on tcp\' + $SQLport
}

# service start timeout extension to 60s
Registry ServicesPipeTimeout {
    Ensure    = "Present"
    Key       = "HKLM:\SYSTEM\CurrentControlSet\Control"
    ValueName = "ServicesPipeTimeout"
    ValueData = "60000"
    ValueType = "Dword"
}

Server customization - SQL level

Further customisation of SQL Server is done with T-SQL queries executed with the separate script (sql-config-sql.ps1), we will configure following settings.

  • Create a login for the domain user with sysadmin privilages
  • Create additional SQL account with sysadmin privileges 

IF SUSER_ID('winopsdba-demo8\devadmin') IS NULL
  CREATE LOGIN [winopsdba-demo8\devadmin]
  FROM WINDOWS WITH DEFAULT_DATABASE = [master]
    ,DEFAULT_LANGUAGE = [us_english]

ALTER SERVER ROLE [sysadmin] ADD MEMBER [winopsdba-demo8\devadmin]
GO

IF SUSER_ID('$($saname)') IS NULL
  CREATE LOGIN [$($saname)]
    WITH PASSWORD = N'$($strtongsapassword)'
      ,DEFAULT_DATABASE = [master]
      ,DEFAULT_LANGUAGE = [us_english]
      ,CHECK_EXPIRATION = OFF
      ,CHECK_POLICY = OFF

ALTER SERVER ROLE [sysadmin] ADD MEMBER [$($saname)]
GO

Congratulations!

After successfully running the scripts, you have created your own lab in the cloud with SQL server added to the domain and server with Azure DevOps agent. You will be able to see a new agent with an online status under the agent pool in Azure DevOps.

Running costs

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

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

Coming next …

We will create a Pester test for our SQL configuration and run everything through CI/CD pipeline.