Desired State Configuration # 2 – Getting Started with SQL Server Installation

After my first contribution – Preparing to use Desired State Configuration (DSC) – you can now take the first steps to install a SQL Server. So what do we need for prerequisites for installing a SQL Server on a new Windows Server?

To my test environment … I’m using a development environment in Azure consisting of at least two Windows servers for the development of my scripts in this case. I use one server as a domain controller and the second machine as SQL Server to test my scripts and test if they’re functional. I can and will roll out one or more servers as needed to show the scripts in my demos or to develop and test my scripts in a “larger” environment.

Installation Requirements

To perform a simple SQL Server installation – in the sense of a customer project – on a new server, we need three things:

• .NET Framework 4.5 (minimum)
• Installation media for SQL Server 2017 Standard Edition
• and the last update in the sense of the last cumulative update

To deploy the installation media to the new server, you must have at least one folder on any drive. Now there are several possibilities to provide this folder…

• Manual installation of the folder
• Creation of the folder with Desired State Configuration
• Creation of the folder using PowerShell

Since we are in this project, these contribution series endeavour to automate everything, of course, the manual creation of the folder is eliminated. Depending on how you want to proceed, what conditions you must/would like to meet or in which order you want to proceed, you can now choose between Desired State Configuration or Powershell. In this project, I would like to illustrate how to create a directory on the target server first and then get the installation media there with very simple steps.

Create the folder with PowerShell

In order to realize my project, theoretically, first I would have to create a Desired State Configuration for the folder structure, implement and start the copy action, then start the actual installation. I’ll try that in a second step, now I’m starting with the “simpler” approach. For this I use the “Invoke Command” and check if the folder exists on the target server or not, if not the folder will be created newly.

Invoke-Command -ComputerName $NodeName -ScriptBlock { 
param ($LocalInstallFolder)
if (!(Test-Path -Path $LocalInstallFolder )) {
    New-Item -ItemType directory -Path $LocalInstallFolder | Out-Null
} else {
    Remove-Item -Recurse -Force $LocalInstallFolder
    New-Item -ItemType directory -Path $LocalInstallFolder | Out-Null
} -ArgumentList "D:$LocalInstallFolder\"

Why do I first delete the destination directory? Of course, if I run the script multiple times, then the current installation media and current update must be already there, therefore, once the directory will be deleted and then re-created. If the target directories exist, then you can copy the required files / directories.

Copy the installation media

Initially, I had copied all my files from a network drive to the destination server with “Copy-Item”, but since I was developing quite a lot with the Powershell ISE, I did not have a “progress bar” … later on, I switched back to “Start-BitsTransfer”.

Write-host "Copy SQL-Image to"$NodeName.ToUpper()
$DestinationPath = "\$NodeName\d$$LocalInstallFolder\"
Start-BitsTransfer -Source ..\SQL\* -Destination $DestinationPath -Description "..\SQL\* will be moved to $DestinationPath" -DisplayName "Copy SQL-Image" 
Start-BitsTransfer -Source ..\SQL\Updates\* -Destination "$DestinationPath\Updates\" -Description "..\SQL\Updates\* will be moved to $DestinationPath" -DisplayName "Copy SQL-Updates"

But that was somehow too much effort and complicated for me, so somehow like a “workaround” … but at least it worked 😉 That’s exactly how I started when I copied the necessary Powershell modules to the target server but with every day and every further attempt to deal with Desired State Configuration, I learned new things how to approach the goal and how to do so. By this learning, I changed the way I copied the necessary files via the Desired State Configuration.

Configuration CopyInstallationMedia
{
    Node $AllNodes.where{ $_.Role.Contains("SqlServer") }.NodeName
    {
        File InstallationFolder 
        {
            Ensure = 'Present'
            Type = 'Directory'
            SourcePath = "\\dc1\NetworkShare\SQL\"
            DestinationPath = "D:\SQL2017\"
            Recurse = $true
        }
    
        File PowershellModules 
        {
            Ensure = 'Present'
            Type = 'Directory'
            SourcePath = "\\dc1\NetworkShare\Modules\"
            DestinationPath = "C:\Windows\system32\WindowsPowerShell\v1.0\Modules\"
            Recurse = $true
        }
    }
}

Clear-Host
$OutputPath = "\\dc1\DSC-ConfigShare"
CopyInstallationMedia -OutputPath "$OutputPath\CopyInstallationMedia\"    

Start-DscConfiguration -ComputerName sqlvm02 -Path \\DC1\DSC-ConfigShare\CopyInstallationMedia -Wait -Verbose -Force

Building my DSC configurations

For the sake of a better overview and a more simplified, step-by-step testing and traceability, I have created several sections in my Desired State Configuration. I can call the particular configuration specifically and individually and the “refine” or correct the function.

Configuration CopyInstallationMedia
{
    Node $AllNodes.where{ $_.Role.Contains("SqlServer") }.NodeName
    {
        File InstallationFolder 
        {
            ...
        }

        File PowershellModules 
        {
            ...
        }

        Configuration ConfigureSQL
        {
            ...
        }
}

Of course, it also requires some parameters which can not be ignored, these are defined at the beginning of the script. In my case, for installation and later configuration, I need at least the path where the installation media should be stored centrally and the destination server where the DSC configuration should be rolled out to.

param
(
    # Path where all Install media will be located
    [Parameter(Mandatory=$true)]
    [String]
    $InstallPath,

    # Computer name to install SQL Server On
    [Parameter(Mandatory=$true)]
    [String]
    $ComputerName
)

Now that I have subdivided and defined the configuration, I can add necessary scripts or modules and initiate the creation of the MOF files. Based on these MOF files, the actual configuration is then compared with the target configuration and corrected accordingly. Since something could have changed in the SET configuration between the last time the MOF files were created and “Now”, I will always have the files re-created for safety reasons, in order to be able to roll them out directly to the target server. For a more detailed explanation of my script section … I call the respective configuration, assign it a configuration file and define a path for the filing of the MOF files. Finally, the respective SET configuration is rolled out from the central storage location to the destination server.
As you can now understand, I am also able to execute the individual steps separately for debugging purposes, either manually one by one or only individual configurations such as the mere configuration of a SQL Server.

Write-host "Starting DSC process on"$NodeName.ToUpper()
Import-Module $PSScriptRoot\ConfigureSQLServer.psm1 -Force

## Create MOF-Files
$OutputPath = "\\dc1\DSC-ConfigShare"
CopyInstallationMedia -ConfigurationData \\dc1\NetworkShare\scripts\configData.psd1 -OutputPath "$OutputPath\CopyInstallationMedia\"
SQLInstall -ConfigurationData \\dc1\NetworkShare\scripts\configData.psd1 -OutputPath "$OutputPath\SQLInstall\"
ConfigureSQL -ConfigurationData \\dc1\NetworkShare\scripts\configData.psd1 -OutputPath "$OutputPath\SQLConfig\"

## Use MOF-Files to establish desired state configuration
Start-DscConfiguration -ComputerName $Computername -Path \\DC1\DSC-ConfigShare\CopyInstallationMedia -Wait -Verbose -Force     
Start-DscConfiguration -ComputerName $Computername -Path \\DC1\DSC-ConfigShare\SQLInstall -Wait -Verbose -Force     
Start-DscConfiguration -ComputerName $Computername -Path \\DC1\DSC-ConfigShare\SQLConfig -Wait -Verbose -Force

This is what my folder structure or file structure looks like on the central server, there is a folder for each SET configuration and a single file for each target server.

More about Desired State Configuration of course in the Microsoft documentation

Björn Peters

Björn continues to work from Mexico as a Senior Consultant – Microsoft Data Platform and Cloud for Kramer&Crew in Cologne. He also remains loyal to the community from his new home, he is involved in Data Saturdays or in various forums. Besides the topics around SQL Server, Powershell and Azure SQL, he is interested in science fiction, baking 😉 and cycling.