As part of my ongoing effort to use DSC to solve all of my deployment woes, I have turned to Microsoft's experimental xSqlPs resource to automate the install and configuration of SQL. I could have used the standard install with a custom ini file, but I felt this was a good opportunity to add something to the growing DSC community. The major issue with the existing xSqlServerInstall resource is that it did not take many important arguments. I have remedied that and below the break you can see how.
The xSqlPs resource can be found here. By default the resource takes the following arguments:
- InstanceName: The name of SQL instance
- SourcePath: The share path of SQL server software
- SourcePathCredential: The credential that vm could use to access net share of sql server software.
- Features: List of names of Sql Server features to install
- SqlAdministratorCredential: The SQL Server Administrator credential
That's a pretty good start, but immediately I thought of the other things I would want in a SQL install:
- Path for the install
- Path for database and logs
- List of SQL administrators
- Service account for SQL Service
- Service account for SQL Agent
The first thing I did was look at the code to see how the existing parameters were being used. Here's the important part:
$saPwd = $SqlAdministratorCredential.GetNetworkCredential().Password
$cmd = Join-Path $SourcePath -ChildPath "Setup.exe"
$cmd += " /Q /ACTION=Install /IACCEPTSQLSERVERLICENSETERMS /UpdateEnabled=false /IndicateProgress "
$cmd += " /FEATURES=$Features /INSTANCENAME=$InstanceName "
$cmd += " /SQLSVCACCOUNT='NT AUTHORITY\Network Service' /SQLSYSADMINACCOUNTS='NT AUTHORITY\System' /AGTSVCACCOUNT='NT AUTHORITY\Network Service' "
$cmd += " /SECURITYMODE=SQL /SAPWD=$saPwd "
$cmd += " > $logFile 2>&1 "
As you can see, the script is building a command for an install from the command line. A few lines later, the script calls Invoke-Command and passes the $cmd variable. So adding more parameters is as simple as taking them as arguments and injecting them into the $cmd variable. I looked up the command line syntax for a SQL Server install and found it here. It's a pretty extensive list of switches. So I created a custom version of the SqlServerInstall resource and added the parameters. Below is a Gist of the file. It's also on my GitHub and I have submitted a pull request to include it in the official community repo.
One thing to note... If you are planning to add your own parameters, the SQL command line install prefers double-quotes on single value items and requires them if there are multiple values or if a value has a space in it.Labels: DSC, powershell, PowerShell Desired State Configuration, Scripts, SQL Server, Windows Server 2012 R2