Installing SQL Server using PowerShell Desired State Configuration (DSC)

Note: DSC can only be run if you have at least Windows Management Framework 4.0 and if your operating system is at least Windows 8.1 or Windows Server 2012 R2.

The PowerShell team has released the xSQLPs module as part of the Desired State Configuration (DSC) Resource Kit. xSQLPs contains xSqlServerInstall, which you can use as a starting script when installing SQL Server via PowerShell and DSC. Download the module here.

This is what I needed to do to install SQL Server using DSC and the xSQLPs module. Many of these steps are from the xSQLPs documentation page.

1.In order to secure the credentials we’ll need for the installation, the server needs to have a certificate installed. Create a self signed certificate, if you don’t already have it. You can use makecert.exe from the Windows SDK for 8.1. Learn more about securing credentials in PowerShell Desired State Configuration from the PowerShell Team Blog.

2.Download xSQLPs module. Make sure you unzip and unblock after you download.

xqlps unblock

3.Save xSQLPs to your PowerShell modules folder

2015-02-08 10-27-34 PM

4.Export your certificate in your script

$certSubject = "CN=DSCDemo" 
$keysFolder = Join-Path $env:SystemDrive -ChildPath "Keys" 
$cert = dir Cert:LocalMachineMy | ? { $_.Subject -eq $certSubject } 
if (! (Test-Path $keysFolder )) 
    md $keysFolder | Out-Null 
$certPath = Export-Certificate -Cert $cert -FilePath (Join-Path $keysFolder -ChildPath "Dscdemo.cer") 

5.Add the DSC configuration node block in your script :

    AllNodes = @(
        NodeName = "localhost"
        CertificateFile = $certPath
        Thumbprint = $cert.Thumbprint
Configuration SqlInstance
        [PsCredential] $credential
    #Download xSQLPs from
    #and save it to your PowerShell Modules directory
    Import-DscResource -Module xSqlPs</code>

    Node $AllNodes.NodeName

    xSqlServerInstall installSqlServer
        InstanceName = "SQL2014"

        #where is the install binary?
        SourcePath = "D:"

        #add additional features here as needed
        Features= "SQLEngine"

        #credential will be prompted
        SqlAdministratorCredential = $credential

        CertificateId = $node.Thumbprint
        RebootNodeIfNeeded = $true

6.Run the configuration script to create a Management Object Framework (MOF)

SqlInstance -ConfigurationData $ConfigData -OutputPath .\Mof -Credential (Get-Credential)

This should generate a couple of MOF files:

mof generated

This is what the contents of the MOF would look like:

mof contents

7.If you are installing SQL Server on a different machine than where you generated the MOF, copy the MOF files to that node.(At some point, you will want your server to pull the updated MOF automatically.)

8.Set the DSC LocalConfigurationManager

Set-DscLocalConfigurationManager .\Mof

9.Start the configuration. This will install your new SQL Server instance in your specified “desired state”

Start-DscConfiguration -Force -Path .\Mof -ComputerName localhost -Wait -Verbose

Possible Error

At this point you may encounter an error saying:

Importing module MSFT_xSqlServerInstall failed with error – File C:\Program Files\WindowsPowerShell\Modules\xSqlPs\DscResources\MSFT_xSqlServerInstall\MSFT_xSqlServerInstall.psm1 cannot be loaded because you opted not tun run this software.

dsc error unblock

There are a couple things you should check or try:
1. Make sure that before you unzip the xSQLPs file you downloaded, you unblocked it.
2. If unblocking doesn’t do the trick, you may need to set your execution policy to bypass to get you past this error and get xSQLPs imported. Remember to set it back to Restricted or RemoteSigned when you are done.


One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s