Jeremy Davis
Sitecore, C# and web development
This is post 4 of 7 in a series titled Development environments with PowerShell DSC
- Development environments with PowerShell DSC – Introduction to DSC
- Development environments with PowerShell DSC – Windows Features
- Development environments with PowerShell DSC – Mongo DB
- Development environments with PowerShell DSC – SQL Server
- Development environments with PowerShell DSC – Sitecore
- Development environments with PowerShell DSC – Coveo CES
- Development environments with PowerShell DSC – Coveo REST API & Coveo for Sitecore
After MongoDB last week, next on my list of stuff we might want to automate in our dev environments is SQL Server. There's a good story around automating installs here, as Microsoft have supported this for some time. And there's also a good story around installing it via DSC. I've taken most of my inspiration from this blog post, and pulled it into the install framework I've been working on. Thanks to Colin Dembovsky for his work here – as there wasn't that much that I found I needed to change to get everything working for me.
Before I get going, couple of notes about the examples I'm including in this series of posts: They all have a call to `Start-DSCConfiguration` using the "-Force" flag to make DSC run this configuration immediately in "push" mode. However, the `Configuration` blocks declared should all work in pull configurations. Writing the scripts this way just makes them easier for people to try out without any other setup effort. Also the examples are all pretty much self-contained, so the overall configuration of a server using them would rely on many separate scripts. You don't necessarily need to have as many as I'm showing here. It's more to keep the examples clear and usable than to represent the "right" architecture. You're free to merge things together, or even break them apart further if that suits you.
The process for installing this service is going to be based on the ISO image available from Microsoft that includes the installer and its files. So the installation will need to know which ISO to use from our file dependency location. It also relies on an
.INI
file which will store the settings we need for configuring the service. And finally, we need to have a password for the SQL Administrator accounts. For the development environment scenario I find that these passwords are rarely actually secret, hence it's in plain text here. If you need more security, storing the passwords in an encrypted form, or prompting for them may be more appropriate for you.
@{
AllNodes = @(
@{
NodeName = "WIN-AQEKG7L9SE8"
Role = "Setup, WindowsFeatures, IE, SqlServer, MongoDB, Sitecore"
TempFolder = "c:\dsc"
SQL = @{
ISOFile = "en_sql_server_2014_web_edition_with_service_pack_1_x64_dvd_6670146.iso"
InstallConfig = "SqlConfigurationFile.ini"
SQLPassword = "p@55w0rd"
}
}
);
}
As described in Colin's blog post, to run an automated install of SQL Server you need to provide a file containing all the install settings you want. This file gets created when you click through the installer, so you can enter all the settings you want, and then grab this file from the path shown in the last page of the wizard.
It's worth noting that you will need to make some edits to this file – for example enabling the "quite" mode for installing with no UI. This file does not store whatever options you enter for passwords, however. These need to be supplied separately.
And it ends up looking something like:
;SQL Server 2014 Configuration File [OPTIONS] ; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. ACTION="Install" ; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system. ENU="True" ; Setup will not display any user interface. QUIET="True" ; Setup will display progress only, without any user interaction. QUIETSIMPLE="False" ; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found. UpdateEnabled="True" ; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature. ERRORREPORTING="False" ; If this parameter is provided, then this computer will use Microsoft Update to check for updates. USEMICROSOFTUPDATE="False" ; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components. FEATURES=SQLENGINE,SSMS,ADV_SSMS ; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services. UpdateSource="MU" ; Displays the command line parameters usage HELP="False" ; Specifies that the detailed Setup log should be piped to the console. INDICATEPROGRESS="False" ; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. X86="False" ; Specify the root installation directory for shared components. This directory remains unchanged after shared components are already installed. INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server" ; Specify the root installation directory for the WOW64 shared components. This directory remains unchanged after WOW64 shared components are already installed. INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server" ; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS). INSTANCENAME="MSSQLSERVER" ; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature. SQMREPORTING="False" ; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance. INSTANCEID="MSSQLSERVER" ; Specify the installation directory. INSTANCEDIR="C:\Program Files\Microsoft SQL Server" ; Agent account name AGTSVCACCOUNT="NT Service\SQLSERVERAGENT" ; Auto-start service after installation. AGTSVCSTARTUPTYPE="Manual" ; CM brick TCP communication port COMMFABRICPORT="0" ; How matrix will use private networks COMMFABRICNETWORKLEVEL="0" ; How inter brick communication will be protected COMMFABRICENCRYPTION="0" ; TCP port used by the CM brick MATRIXCMBRICKCOMMPORT="0" ; Startup type for the SQL Server service. SQLSVCSTARTUPTYPE="Automatic" ; Level to enable FILESTREAM feature at (0, 1, 2 or 3). FILESTREAMLEVEL="0" ; Set to "1" to enable RANU for SQL Server Express. ENABLERANU="False" ; Specifies a Windows collation or an SQL collation to use for the Database Engine. SQLCOLLATION="Latin1_General_CI_AS" ; Account for SQL Server service: Domain\User or system account. SQLSVCACCOUNT="NT Service\MSSQLSERVER" ; Windows account(s) to provision as SQL Server system administrators. SQLSYSADMINACCOUNTS="WIN-AQEKG7L9SE8\Administrator" ; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication. SECURITYMODE="SQL" ; Provision current user as a Database Engine system administrator for %SQL_PRODUCT_SHORT_NAME% Express. ADDCURRENTUSERASSQLADMIN="False" ; Specify 0 to disable or 1 to enable the TCP/IP protocol. TCPENABLED="1" ; Specify 0 to disable or 1 to enable the Named Pipes protocol. NPENABLED="0" ; Startup type for Browser Service. BROWSERSVCSTARTUPTYPE="Disabled"
As with last week, the code follows the standard pattern for the
Configuration
and the
Node. The first thing we need is to ensure that the appropriate .Net runtimes are available. As we know from
the previous entry on O/S features, this is easy:
Configuration SQLInstall
{
param (
[Parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[String]
$PackagePath
)
Node $AllNodes.where{ $_.Role.Contains("SqlServer") }.NodeName
{
WindowsFeature NetFramework35Core
{
Name = "NET-Framework-Core"
Ensure = "Present"
}
WindowsFeature NetFramework45Core
{
Name = "NET-Framework-45-Core"
Ensure = "Present"
}
#
# Rest of the resources go here...
#
}
}
SQLInstall -ConfigurationData "configData.psd1" -PackagePath "\\VBOXSVR\DSC\SQL"
Start-DscConfiguration -Path .\SQLInstall -Wait -Force -Verbose
You may find that you end up with duplication here, between the O/S features and the SQL script - but as previously mentioned that's not a major issue, as DSC won't try to re-install things your server already has. You can adjust the scripts to avoid the duplication if you wish, but to my mind being able to install SQL Server separately to the rest of the process is useful – consider the scenario where you want your database server on a different server to your Sitecore install.
The next task required is to copy over the files that the install depends on:
File SQLServerIso
{
SourcePath = "$PackagePath\$($Node.SQL.ISOFile)"
DestinationPath = "$($Node.TempFolder)\$($Node.SQL.ISOFile)"
Type = "File"
Ensure = "Present"
}
File SQLServerIniFile
{
SourcePath = "$PackagePath\$($Node.SQL.InstallConfig)"
DestinationPath = "$($Node.TempFolder)\$($Node.SQL.InstallConfig)"
Type = "File"
Ensure = "Present"
}
The files both come from the
-PackagePath
folder, and they get copied into the temporary folder in the config data.
Next the installation can run using a
Script
resource. The
GetScript
and
TestScript
use WMI queries to find out if there is a SQL Server service installed:
Script InstallSQLServer
{
DependsOn = "[file]SQLServerIso", "[file]SQLServerIniFile"
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
}
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
}
SetScript =
{
$tmp = $using:Node.TempFolder
$iso = $using:Node.SQL.ISOFile
$pwd = $using:Node.SQL.SQLPassword
$isoFile = "$tmp\$iso"
Write-Verbose "$isoFile"
$setupDriveLetter = (Mount-DiskImage -ImagePath "$($isoFile)" -PassThru | Get-Volume).DriveLetter + ":"
if ($setupDriveLetter -eq $null) {
throw "Could not mount SQL install iso"
}
Write-Verbose "Drive letter for iso is: $setupDriveLetter"
$cmd = "$($setupDriveLetter)\Setup.exe /ConfigurationFile=$tmp\SqlConfigurationFile.ini /SAPWD=$pwd /IAcceptSQLServerLicenseTerms /SQLSVCPASSWORD=$pwd /AGTSVCPASSWORD=$pwd"
Write-Verbose "Running SQL Install - check %programfiles%\Microsoft SQL Server\120\Setup Bootstrap\Log\ for logs..."
Invoke-Expression $cmd | Write-Verbose
Dismount-DiskImage -ImagePath "$isoFile"
}
}
The
SetScript
extracts the config variables it requires and mounts the
.ISO
file as a volume. It then works out the command line needed to run the installer, before executing it. The output of the command is piped to the screen to ensure the script runs synchronously. The command line includes the path to the configuration file, the passwords for the various services and a special switch.
/IAcceptSQLServerLicenseTerms
is required for silent installs, in place of clicking "I accept the license terms". Finally the script dismounts the ISO file, clearing the way for the temp folder to be tidied up later if required.
And that's all that's required to get the SQL service installed and running for you.
Next week, finally (!), we'll get on to getting Sitecore added into the mix.
↑ Back to top