In the following post, I would like to step a little more into depth to show you how I customized my scripts in order to copy and prepare everything for my Desired State Configuration SQL Server rollout. After explaining and showing the preparations and fundamentals of my deployment in the first two blog posts, today I would like to explain the actual installation as I had implemented it (there is also another way that I would like to try later – as you know there is no time in such projects ;-))

My scripts are in a state that you can call them “working” as I wrote this post. My scripts are currently able to create all the necessary folders on one (or more) server from a central server, they will copy all the required files (ISO image, updates, PowerShell modules, etc) to the target server, then according to the specifications they will install a SQL Server 2017 including the latest patch level and configure this SQL Server according to best practice requirements.

As explained in the second part of the DSC series, I have copied all the necessary files with my DSC-Configuration – including the current ISO image of the SQL Server 2017 and the last cumulative update – to the destination server. (In the picture only the TARGET state was checked, no actions were necessary!)

You can start with that actual state of the script right away and finally start with the real setup… for that we need the following

• the ISO-Image (en_sql_server_2017_developer_x64_dvd_11296168.iso)
• the latest patch (SQLServer2017-KB4466404-x64.exe)
• the ConfigurationFile.ini
• and the necessary Powershell-modules (e.g. dbatools, SecurityPolicyDsc, SqlServerDsc …)

In the file “ConfigData.psd” I have written down all necessary configuration variables, values and roles which were needed during the setup. In my current example, all variables and values are identical for all servers but they may be different in other environments.

@{
    AllNodes = @(
        @{
            NodeName        = "*"
            PSDscAllowPlainTextPassword = $true
            PSDscAllowDomainUser =$true
            SQLInstallPath     = "D:\SQL2017"
            InstanceName    = "MSSQLServer"
        },
        @{
            NodeName        = "sqlvm01"
            Role            = "SqlServer"
        },
        @{
            NodeName        = "sqlvm02"
            Role            = "SqlServer"
        }
        @{
            NodeName        = "sqlvm03"
            Role            = "SqlServer"
        }
        @{
            NodeName        = "sqlvm04"
            Role            = "SqlServer"
        }
    )
}

Murphy has also struck

But from the beginning … On my test machines sqlvm01 and sqlvm02 I “played” a little bit without really logging what I did and why … so what did I do – for whatever reason – on those two machines locally or in which sequence and what caused my scripts to work ??? On sqlvm03 and later on sqlvm04, my scripts did not work as desired … Actually, I was very satisfied how my scripts ran on the first two machines, then SQL Server 2017 was installed and finally configured by Desired State Configuration. All four SQL Servers were configured identically within the deployment in the same resource group and I ran the same preparation scripts on all of them.

• Network Discovery enabled
• all discs were integrated and formatted
• joined the target domain

And then from the domain controller using DSC, I pushed the configurations – as described before – to the target server … but unfortunately, the script resource did not like me … on 01/02 I was able to mount the ISO image without any problem. No matter what I did, no matter how I turned it, I could never get the ISO image mounted on 03/04 and therefore I wasn’t able to access the drive in order to run “setup.exe”.

The new beginning – everything back to 0.25

Sometimes things are going from bad to worse… of course, I didn’t have any backup of any script (whether working or not) … I created at least some kind of versioned folder structure if I was successful with a “milestones”, but I did not have a proper backup and certainly not included my work in a versioning software. So it came, how it had to come! I deleted the wrong folder with Powershell and everything was gone, only what was still open in Visual Studio code (3 files) could be saved.
Actually, I have – no idea where the error came from – deleted almost the entire C-disk… no more Internet browser, no PowerShell more, it was almost nothing there… it was just the easiest to rebuild the server (if you as I have no backup) => it was only the domain controller 😮

This unpleasant events, however, I have taken as an opportunity to clean up my entire development environment and choose “a little different” set up … actually, everything is the same, except that I will no longer develop on the domain controller itself and my scripts will no longer be located on the server, in other projects I have already gone this way. So I just set up a new Windows Server 2016 (standard DS1 v2), roll out and configure the Active Directory and DNS role. In addition, there is now a Windows 10 machine (Standard A4m v2) on which I will develop my scripts.

Learning from mistakes makes you stronger

In order to come closer to my goal or the project’s goal in terms of “automated installation of SQL servers with Desired State Configuration”, I, unfortunately, had to start over again … but this time with a slightly different approach. 😉
My first lessons-learned … Create a backup from a “blank machine” to get back to the original server faster. My second lessons-learned … do everything else with scripts (which I will publish in another blog post)

But back to the topic Desired State Configurations – I stayed with my starting activities, copying the Powershell modules and the installation media to the respective target server, what has remained is checking whether the necessary .NET framework is installed, otherwise, it will be installed. This time I oriented myself a bit (or a bit more) to Chris Lumnah (Blog), who has built a script based on an introduction to DSC from the Microsoft Virtual Academy and own knowledge, which I would like to adapt, since it doesn’t run with the DSC Script resource works, but with the “correct” SQL Server DSC resource.

More to come in the next blog post, because I had to spend another day on setting up everything again and start over with my new DSC scripts…
Sorry and thank you for your understanding!

Björn Peters

Björn works in Hamburg as Senior Consultant – Microsoft Data Platform and Cloud at Kramer & Crew. He regularly participates in the PASS regional group meeting in Hamburg, the events of the PASS such as SQLSaturday and DataGrillen and organises the Azure Meetup in Hamburg. He is interested in topics such as SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.