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:
- What is DSC?
- Setting up the environment for DSC
- Configure Active Directory with DSC
- What LEGO has to do with PowerShell DSC Partial Configurations!
- Configure IIS with DSC
- Install SQL with DSC(this post)
- Install SharePoint
- Configure SharePoint