Deploy SQL Server 2019 with PowerShell DSC | Dynamic Business Automation Technology

This is still applicable for deploying SQL Server 2019 with PowerShell DSC onto Windows Server 2019, with and without Desktop Experience.

This post isn”t that different than the Deploy SQL Server 2017 with PowerShell DSC post found on this site. This configuration is for PowerShell DSC v1.1.

Install dependencies on the remote server and your local machine:

Install-Module -Name "ComputerManagementDsc" -RequiredVersion "8.0.0" -Scope AllUsers
Install-Module -Name "SqlServerDsc" -RequiredVersion "14.0.0" -Scope AllUsers
Install-Module -Name "StorageDsc" -RequiredVersion "4.4.0.0" -Scope AllUsers

The full configuration:

Configuration DBServer {

    param (
        [pscredential]$SACreds,
        [pscredential]$DeploymentCreds
    )

    Import-DscResource -ModuleName "PSDesiredStateConfiguration"
    Import-DscResource -ModuleName "SqlServerDsc" -ModuleVersion "14.0.0"
    Import-DscResource -ModuleName "ComputerManagementDsc" -ModuleVersion "8.0.0"
    Import-DscResource -ModuleName "StorageDsc" -moduleversion "4.4.0.0"


    Node $AllNodes.NodeName {

        LocalConfigurationManager {
            AllowModuleOverwrite = $true
            ActionAfterReboot    = "ContinueConfiguration"
            ConfigurationMode    = "ApplyAndMonitor"
            RebootNodeIfNeeded   = $true
        }

        TimeZone EastCoast {
            IsSingleInstance = "Yes"
            TimeZone         = "Eastern Standard Time"
        }

        PowerPlan HighPerformance {
            IsSingleInstance = "Yes"
            Name             = "High Performance"
        }

        RemoteDesktopAdmin RDC {
            IsSingleInstance   = "Yes"
            UserAuthentication = "Secure"
            Ensure             = "Present"
            DependsOn          = "[PowerPlan]HighPerformance"
        }

        VirtualMemory PageFileInVM {
            Drive       = "C"
            Type        = "CustomSize"
            DependsOn   = "[RemoteDesktopAdmin]RDC"
            InitialSize = 2048
            MaximumSize = 4096
        }

        PendingReboot RB {
            Name      = "RebootWhenNeeded"
            DependsOn = "[VirtualMemory]PageFileInVM"
        }

        OpticalDiskDriveLetter CDROM {
            DiskId      = "1"
            DriveLetter = "Z"
            Ensure      = "Present"
            DependsOn   = "[PendingReboot]RB"
        }

        WindowsFeature NetFramework45 {
            Name      = "NET-Framework-45-Core"
            Ensure    = "Present"
            DependsOn = "[OpticalDiskDriveLetter]CDROM"
        }

        # Sql Server Best Practices 64K Allocation Unit Size - https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966412(v=technet.10)?redirectedfrom=MSDN
        Disk DATADrive {
            DiskId             = 1
            DriveLetter        = "E"
            FSFormat           = "NTFS"
            AllocationUnitSize = 65536
            FSLabel            = "DATA"
            PartitionStyle     = "GPT"
            DependsOn          = "[OpticalDiskDriveLetter]CDROM"
        }

        Disk DATADriveTwo {
            DiskId             = 2
            DriveLetter        = "F"
            FSFormat           = "NTFS"
            AllocationUnitSize = 65536
            FSLabel            = "LOGS"
            PartitionStyle     = "GPT"
            DependsOn          = "[OpticalDiskDriveLetter]CDROM"
        }
        Disk DATADriveThree {
            DiskId             = 3
            DriveLetter        = "G"
            FSFormat           = "NTFS"
            AllocationUnitSize = 65536
            FSLabel            = "TEMPDB"
            PartitionStyle     = "GPT"
            DependsOn          = "[OpticalDiskDriveLetter]CDROM"
        }
        Disk DATADriveFour {
            DiskId         = 4
            DriveLetter    = "H"
            FSFormat       = "NTFS"
            FSLabel        = "BACKUPS"
            PartitionStyle = "GPT"
            DependsOn      = "[OpticalDiskDriveLetter]CDROM"
        }

        WaitForVolume edrive {
            DriveLetter      = "E"
            DependsOn        = "[Disk]DATADrive"
            RetryIntervalSec = 20
            RetryCount       = 20
        }
        WaitForVolume fdrive {
            DriveLetter      = "F"
            DependsOn        = "[Disk]DATADriveTwo"
            RetryIntervalSec = 20
            RetryCount       = 20
        }
        WaitForVolume gdrive {
            DriveLetter      = "G"
            DependsOn        = "[Disk]DATADriveThree"
            RetryIntervalSec = 20
            RetryCount       = 20
        }
        WaitForVolume hdrive {
            DriveLetter      = "H"
            DependsOn        = "[Disk]DATADriveFour", "[WaitForVolume]fdrive", "[WaitForVolume]Edrive", "[WaitForVolume]gdrive"
            RetryIntervalSec = 20
            RetryCount       = 20
        }

        File MSSQLDBFolder1 {
            DestinationPath = "E:\Data"
            Ensure          = "Present"
            Type            = "Directory"
            DependsOn       = "[WaitForVolume]hdrive"
        }
        File MSSQLdatabuilder {
            DestinationPath = "E:\Data\databuilder"
            Ensure          = "Present"
            Type            = "Directory"
            DependsOn       = "[File]MSSQLDBFolder1"
        }
        File MSSQLproduction {
            DestinationPath = "E:\Data\production"
            Ensure          = "Present"
            Type            = "Directory"
            DependsOn       = "[File]MSSQLDBFolder1"
        }
        
        File MSSQLogsFolder1 {
            DestinationPath = "F:\MSSQL"
            Ensure          = "Present"
            Type            = "Directory"
            DependsOn       = "[File]MSSQLDBFolder1"
        }
        File MSSQLLogsFolder2 {
            DestinationPath = "F:\MSSQL\Logs"
            Ensure          = "Present"
            Type            = "Directory"
            DependsOn       = "[File]MSSQLogsFolder1"
        }
        File MSSQLTempDBFolder1 {
            DestinationPath = "G:\MSSQL"
            Ensure          = "Present"
            Type            = "Directory"
            DependsOn       = "[File]MSSQLLogsFolder2"
        }
        File MSSQLTempDBFolder2 {
            DestinationPath = "G:\MSSQL\TempDB"
            Ensure          = "Present"
            Type            = "Directory"
            DependsOn       = "[File]MSSQLTempDBFolder1"
        }
        File MSSQLBackupFolder {
            DestinationPath = "H:\Backups"
            Ensure          = "Present"
            Type            = "Directory"
            DependsOn       = "[File]MSSQLTempDBFolder2"
        }
        
        SmbShare BackupShare {
            Name       = "Backups"
            Path       = "H:\Backups"
            FullAccess = "Everyone"
            Ensure     = "Present"
            DependsOn  = "[File]MSSQLBackupFolder"
        }

        SqlSetup BaseInstall {
            InstanceName          = "MSSQLSERVER"
            Action                = "Install"
            SqlSvcStartupType     = "Automatic"
            BrowserSvcStartupType = "Automatic"
            Features              = "SQLEngine,FullText,Conn"
            ForceReboot           = $true
            SecurityMode          = "SQL"
            SAPwd                 = $saCreds
            SQLSysAdminAccounts   = @("Administrators")
            SourcePath            = "\\zm-fs-01\SoftwareRepo\Apps\SQLServer2019_Install"
            InstallSharedDir      = "C:\Program Files\Microsoft SQL Server"
            InstallSharedWOWDir   = "C:\Program Files (x86)\Microsoft SQL Server"
            InstanceDir           = "C:\Program Files\Microsoft SQL Server"
            InstallSQLDataDir     = "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data"
            SQLUserDBDir          = "E:\Data"
            SQLUserDBLogDir       = "F:\MSSQL\Logs"
            SQLBackupDir          = "H:\Backups"
            SQLTempDBDir          = "G:\MSSQL\TempDB"
            SQLTempDBLogDir       = "G:\MSSQL\TempDB"
            UpdateEnabled         = "False"
            PsDscRunAsCredential  = $DeploymentCreds
            DependsOn             = "[SmbShare]BackupShare"
        }

        SqlMemory Set_SQLServerMaxMemory {
            Ensure               = "Present"
            DynamicAlloc         = $false
            MinMemory            = 1024
            MaxMemory            = 32768
            ServerName           = $Node.NodeName
            InstanceName         = "MSSQLSERVER"
            PsDscRunAsCredential = $DeploymentCreds
            DependsOn            = "[SqlSetup]BaseInstall"
        }

        SqlProtocol EnableTcpIp {
            InstanceName           = "MSSQLSERVER"
            ServerName             = $Node.NodeName
            ProtocolName           = "TcpIp"
            Enabled                = $true
            ListenOnAllIpAddresses = $true
            RestartTimeout         = 300
            PsDscRunAsCredential   = $DeploymentCreds
            DependsOn              = "[SqlMemory]Set_SQLServerMaxMemory"
        }
        
        SqlProtocol SharedMemory {
            InstanceName         = "MSSQLSERVER"
            ProtocolName         = "SharedMemory"
            Enabled              = $true
            PsDscRunAsCredential = $DeploymentCreds
            DependsOn            = "[SqlProtocol]EnableTcpIp"
        }

        SqlProtocol EnableNamedPipes {
            InstanceName   = "MSSQLSERVER"
            ServerName     = $Node.NodeName
            ProtocolName   = "NamedPipes"
            Enabled        = $true
            RestartTimeout = 300
            DependsOn      = "[SqlProtocol]SharedMemory"
        }


        Script ConfigureSqlServerAgentWindowsService {
            TestScript = {
                (Get-Service -Name SQLSERVERAGENT).StartType -eq "Automatic"
            }
            GetScript  = { @{ Result = ((Get-Service -Name SQLSERVERAGENT).StartType -eq "Automatic") } }
            SetScript  = {
                Set-Service -Name SQLSERVERAGENT -StartupType Automatic
                Start-Service -Name SQLSERVERAGENT
            }
            DependsOn  = "[SqlProtocol]EnableNamedPipes"
        }

    }
}

Let”s get the configuration data section together.

$cd = @{
    AllNodes = @(
        @{
            NodeName                    = "SQL1"
            PsDscAllowPlainTextPassword = $true
            PsDscAllowDomainUser        = $true
        }
    )
}

This deployment assumes you”ll be using a domain account for the run as credentials to install Microsoft SQL Server and change some of it”s configuration after install. Additionally, the installation is configured for Mixed mode authentication, so we”ll need credentials for the sa account.

$saCredObj = Get-Credential -username "sa"

$DeploymentCredObj = Get-Credential

Now let”s create a a place to save the mof files.

$MofLocation = "C:\dsc_files"

if ((Test-Path -Path $MofLocation) -eq $False) {
    New-Item -Path "C:\" -Name dsc_files -ItemType Directory
}

Now we compile the config.

DBServer -SaCreds $saCredObj -DeploymentCreds $DeploymentCredObj -ConfigurationData $cd -OutputPath $MofLocation -Verbose

After that we can test the configuration against the remote node.

$ServerName = $cd.AllNodes.Nodename

#region TestTheConfiguration
$output = Test-DscConfiguration -Path $MofLocation -ComputerName $ServerName -Verbose

# Then check out the output
$output

The Local Configuration Manager has been changed to reboot the node if needed, which is nice since the virtual memory is being configured and SQL Server is being installed.

Set-DscLocalConfigurationManager -Path $MOFlocation -ComputerName $ServerName -Verbose

Then we can push the configuration.

Start-DscConfiguration -Path $MOFlocation -ComputerName $ServerName -Verbose