Install SQL with DSC

Ever installed SQL and asked yourself, can’t this be simpler? Yes it can! meet DSC.

This post is part of a series, at the bottom you find links to the other parts. In this part we will take care of installing SQL server.

First step is to configure our environment as described in the previous post.
Next we can add our SQL specific stuff.

Create partial configurations

Now let’s create the SQL blueprint: InstallSQL

	# A configuration to install a default SQL instance
	Configuration InstallSQL
	{
		param (
			[Parameter(Mandatory=$true)]
			[pscredential]
			$NetworkCredentials,
			[Parameter(Mandatory=$true)]
			[ValidateNotNullOrEmpty()]
			[String]
			$ISOPath,
			[Parameter(Mandatory=$true)]
			[ValidateNotNullOrEmpty()]
			[String]
			$CDKey
		)
	 
		Import-DscResource -ModuleName 'PSDesiredStateConfiguration'
	 
		Node $AllNodes.NodeName
		{
			#
			# Ensure that .NET framework features are installed (pre-reqs for SQL)
			#
			WindowsFeature NetFramework35Core
			{
				Name = "NET-Framework-Core"
				Ensure = "Present"
			}
	 
			WindowsFeature NetFramework45Core
			{
				Name = "NET-Framework-45-Core"
				Ensure = "Present"
			}
	 
			# copy the sqlserver iso
			File SQLServerISO
			{
			    Credential=$NetworkCredentials
			    SourcePath = "$ISOPath"
			    DestinationPath = "C:\InstallSQL\SQLServer.iso"
			    Type = "File"
			    Ensure = "Present"
			}
	 
			# create sqlserver configuration ini file
			File SQLConfigurationFile
			{
				DestinationPath = 'C:\InstallSQL\ConfigurationFile.ini'
				Contents = '
					[OPTIONS]
					PID=' + $CDKey + '
					IACCEPTSQLSERVERLICENSETERMS="True"
					ACTION="Install"
					ENU="True"
					QUIET="True"
					UpdateEnabled="False"
					FEATURES=SQLENGINE,CONN,SSMS
					UpdateSource="MU"
					HELP="False"
					INDICATEPROGRESS="False"
					X86="False"
					INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
					INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
					INSTANCENAME="MSSQLSERVER"
					INSTANCEID="MSSQLSERVER"
					SQMREPORTING="False"
					ERRORREPORTING="False"
					INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
					AGTSVCACCOUNT="NT AUTHORITY\SYSTEM"
					AGTSVCSTARTUPTYPE="Manual"
					COMMFABRICPORT="0"
					COMMFABRICNETWORKLEVEL="0"
					COMMFABRICENCRYPTION="0"
					MATRIXCMBRICKCOMMPORT="0"
					SQLSVCSTARTUPTYPE="Automatic"
					FILESTREAMLEVEL="0"
					ENABLERANU="False"
					SQLCOLLATION="Latin1_General_CI_AS"
					SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
					SQLSYSADMINACCOUNTS="DEVELOPMENT\Administrator"
					ADDCURRENTUSERASSQLADMIN="False"
					TCPENABLED="1"
					NPENABLED="0"
					BROWSERSVCSTARTUPTYPE="Disabled"'
			}

			#
			# Install SqlServer using ini file
			#
			Script InstallSQLServer
			{
				GetScript =
				{
					$sqlInstances = gwmi win32_service -computerName localhost | ? { $_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe" } | % { $_.Caption }
					$res = $sqlInstances -ne $null -and $sqlInstances -gt 0
					$vals = @{
						Installed = $res;
						InstanceCount = $sqlInstances.count
					}
					$vals
				}
				SetScript =
				{
					# mount the iso
					$setupDriveLetter = (Mount-DiskImage -ImagePath C:\InstallSQL\SQLServer.iso -PassThru | Get-Volume).DriveLetter + ":"
					if ($setupDriveLetter -eq $null) {
						throw "Could not mount SQL install iso"
					}
					Write-Verbose "Drive letter for iso is: $setupDriveLetter"
					 
					# run the installer using the ini file
					$cmd = "$setupDriveLetter\Setup.exe /ConfigurationFile=C:\InstallSQL\ConfigurationFile.ini"
					Write-Verbose "Running SQL Install - check %programfiles%\Microsoft SQL Server\120\Setup Bootstrap\Log\ for logs..."
					Invoke-Expression $cmd | Write-Verbose

					# dismount the iso
					Dismount-DiskImage -ImagePath C:\InstallSQL\SQLServer.iso
				}
				TestScript =
				{
					$sqlInstances = gwmi win32_service -computerName localhost | ? { $_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe" } | % { $_.Caption }
					$res = $sqlInstances -ne $null -and $sqlInstances -gt 0
					if ($res) {
						Write-Verbose "SQL Server is already installed"
					} else {
						Write-Verbose "SQL Server is not installed"
					}
					$res
				}
			}
		}
	}

The new LEGO manual

Now let’s ensemble our environment with the blueprint we have created.

[DscLocalConfigurationManager()]
Configuration ConfigureTestMachine
{
	PartialConfiguration AssertDC
	{
		Description = 'Create a Domain Controller'
		RefreshMode = 'Push'
	}

	PartialConfiguration AllowSecureRDP
	{
		Description = 'Enable secure RDP connections'
		RefreshMode = 'Push'
		DependsOn = '[PartialConfiguration]AssertDC'
	}

	PartialConfiguration InstallIIS
	{
		Description = 'Install IIS'
		RefreshMode = 'Push'
		DependsOn = '[PartialConfiguration]AssertDC'
	}

	PartialConfiguration ConfigureIIS
	{
		Description = 'Configure IIS'
		RefreshMode = 'Push'
		DependsOn = '[PartialConfiguration]InstallIIS'
	}

	PartialConfiguration InstallSQL
	{
		Description = 'Install SQL'
		RefreshMode = 'Push'
		DependsOn = '[PartialConfiguration]AssertDC'
	}
}

Compile the stuff into MOF files

We now have created all the script parts we need, it time to compile it all into MOF files.

	$configurationData = @{
		AllNodes = @(
			@{
				NodeName="localhost"
				PSDscAllowPlainTextPassword=$true
			 }
		)
	}

	$username = 'Wouter'
	$password = 'S@fePassw0rd'
	$iso = '\\SERVERNAME\ISO\SQL2012.ISO'
	$key = 'XXXXX-XXXXX-XXXXX-XXXXX-XXXXX'

	# Compile the partial configuration "InstallSQL"
	InstallSQL `
		-ConfigurationData $configurationData `
		-NetworkCredentials (New-Object System.Management.Automation.PSCredential ($username, (ConvertTo-SecureString $password -AsPlainText -Force))) `
		-ISOPath $iso `
		-CDKey $key `
		-outputpath c:\InstallSQL
	  
	# Compile the "ensamble" configuration "ConfigureTestMachine "
	ConfigureTestMachine -outputpath c:\ConfigureTestMachine

Update our DSC Configuration

Set-DscLocalConfigurationManager -Path c:\ConfigureTestMachine -Verbose
Publish-DscConfiguration -path c:\InstallSQL -verbose
Start-DscConfiguration -UseExisting -Wait

This post is seperated into multiple parts:

1. What is DSC?
2. Setting up the environment for DSC
3. Configure Active Directory with DSC
4. What LEGO has to do with PowerShell DSC Partial Configurations!
5. Configure IIS with DSC
6. Install SQL with DSC(this post)
7. Install SharePoint
8. Configure SharePoint