Installing SQL Server Using Desired State Configuration

(Update: I’ve since discovered that SqlServerDsc has replaced xSQLServer.)

One of my growing passions is using PowerShell Desired State Configuration (DSC) to automate all the things. I started out with simple configurations for testing but wanted to dive into more complex\useful situations for my day-to-day DBA life. To be honest, I was intimidated by the idea of doing a SQL installation. Configuring simple parameters or creating a directory are easy enough to wrap my head around, but something as complex as a DBMS installation gave me pause. I’m here to tell you that my worries were unfounded, and that you should have none as well.

The blessing and curse of DSC is that it’s so new. It is without doubt a very powerful tool, but as of yet there isn’t a lot of documentation around the individual resources. Or worse yet, the pace of improvement moves so quickly that information from two years ago is now out of date. I plan on doing a separate post for how to approach DSC given these realities. With this post, however, I wanted to fill one of those documentation gaps. Specifically, how to install and configure an instance of SQL server. I based my file off of an example one provided by Microsoft in the “Examples” folder of the xSQLServer module named “SQLPush_SingleServer.ps1”. Pro tip: always look for example folders in the modules you want to work with. It should be noted that you can address much more complicated scenarios, such as setting up clusters or Availability Groups, but for simplicity this configuration will be creating a single instance on one node.

If you have experience with DSC or simply don’t want to listen to me drone on about the details, the full configuration is at the bottom. For those interested in the play by play, or just bored and looking for something to do, I’ll address each piece individually.

The script starts out with compulsory documentation on the script and what it does. Kidding aside, get into the habit of doing small sections like this. Your coworkers (and you years from now when you’ve forgotten what you did) will thank you.

############################################################################################################################
# SQL Server Single Instance Push
# Author: Landon Fowler
# Date 01/2018
#
# Based off of Microsoft example file
#
# Prerequisites: Unzip the SQL ISO to your file share
#                If required, copy the sxs folder to your file share for .NET Framework
############################################################################################################################

Next, we hard-code a couple of items specific to your individual run of the script. List the computer(s) that you want to deploy to as well as a local path for the configuration file that DSC will create.

#Computers to target
$computers = 'YourComputer'
#Local path on your computer to which the .mof file will be exported
$OutputPath = 'C:\Install\DSCLocal'

$cim = New-CimSession -ComputerName $computers

Following that, we will set how the Local Configuration Manager on the target nodes is to behave. We’re specifying that the configuration is being pushed to it, that it should automatically check every so often for compliance to this configuration and auto-correct anything that’s not aligned, that modules on the node can be overwritten, and that it can reboot if needed.

#Set LCM configuration for target nodes
[DSCLocalConfigurationManager()]
Configuration LCM_Push
{
    Param(
        [string[]]$ComputerName
    )
    Node $ComputerName
    {
    Settings
        {
            AllowModuleOverwrite = $True
            ConfigurationMode = 'ApplyAndAutoCorrect'
            RefreshMode = 'Push'
            RebootNodeIfNeeded = $True
        }
    }
}

#Apply the above LCM settings to each target node
foreach ($computer in $computers)
{
    $GUID = (New-Guid).Guid
    LCM_Push -ComputerName $Computer -OutputPath $OutputPath
    Set-DSCLocalConfigurationManager -Path $OutputPath  -CimSession $computer
}

Following that is the actual configuration details, where all the fun is defined. Mine is named “SQLSA”, but it really doesn’t matter what you name it. This is like defining a function; so as long as you call it by that same name later, little else is relevant. You’ll see at the top of this section there are three “Import-DscResource” lines. This tells the configuration which DSC modules will be needed to perform the actions we’re requesting.

#Detail the configuration that will be pushed
Configuration SQLSA
{
    Import-DscResource -Module xPSDesiredStateConfiguration
    Import-DscResource -Module xSQLServer
    Import-DscResource -Module xNetworking

    Node $AllNodes.NodeName
    {
        # Set LCM to reboot if needed
        LocalConfigurationManager
        {
            AllowModuleOverwrite = $true
            RebootNodeIfNeeded = $true
        }

The WindowsFeature item is one of the most handy in DSC. This allows us, as you might guess, to install Windows Features (in this case the .NET Framework).

        # Not necessary when using SQL 2016 on Windows Server 2016.
        WindowsFeature "NET"
        {
            Ensure = "Present"
            Name = "NET-Framework-Core"
            Source = $Node.NETPath
        }

Next I’ve created a firewall rule to make sure our instance’s port will be open (this is defined later under xSQLServerNetwork). It’s worth noting that there is a resource built into xSQLServer that allows you to configure firewall rules for SQL. However, I did not like the behavior of it and found that xFirewall from the module xNetworking provided a lot more flexibility.

        #Create firewall rule for our instance
        xFirewall Firewall
        {
            Name                  = 'SQL_Instance'
            DisplayName           = 'SQL Instance Firewall Rule'
            Ensure                = 'Present'
            Enabled               = 'True'
            Profile               = ('Domain', 'Private', 'Public')
            Direction             = 'Inbound'
            RemotePort            = 'Any'
            LocalPort             = $Node.SqlPort
            Protocol              = 'TCP'
        }

Up next is the actual meat of installing SQL Server. The if($Node.Features) block is something I picked up from the example file. I’d say it’s redundant to check for whether you’re installing SQL when you came here to install SQL, but hey, it works well so I left it.

One way I’ve altered this section from the original is to parameterize everything. If you look further down there is a $ConfigurationData section. Having all of our customizable fields there allows us to easily change them for each deployment (dev, test, prod) without having to search through the code. You and your team will know exactly where to go and what to change for each situation.

I’ve also included some examples of basic SQL Server tasks like creating a database, disabling the sa account, disabling a feature like xp_cmdshell, and configuring the network port (referenced earlier). The naming on these items looks odd but makes sense. By adding in the node name we can ensure that they are unique should we deploy to more than one target node. And adding a friendly name to the configuration item, like “sa”, makes it easy to tell DSC which item depends on which. Speaking of which, note that each of the configurations depends on the base installation. That way DSC will not run those if there is nothing to actually configure.

        #Begin actual SQL setup
        if($Node.Features)
        {
           xSqlServerSetup ($Node.NodeName)
           {
               DependsOn = '[WindowsFeature]NET'
               SourcePath = $Node.SourcePath
               SourceCredential = $Node.InstallerServiceAccount
               InstanceName = $Node.InstanceName
               Features = $Node.Features
               SQLSysAdminAccounts = $Node.AdminAccount
               InstallSharedDir = $Node.BinaryPath
               InstanceDir = $Node.InstancePath
               InstallSQLDataDir = $Node.SysDBPath
               SQLUserDBDir = $Node.UserDBPath
               SQLUserDBLogDir = $Node.UserDBLogPath
               SQLTempDBDir = $Node.TempDBPath
               SQLTempDBLogDir = $Node.TempDBLogPath
               SQLBackupDir = $Node.BackupPath
           }

           #Create a database
           xSQLServerDatabase ($Node.NodeName+"Diagnostics")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                Name = "Diagnostics"
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                Ensure = "Present"
           }

           #Disable the builtin sa account
           xSQLServerLogin($Node.Nodename+"sa")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                Name = 'sa'
                Disabled = $true
           }

           
           #Disable an option such as xp_cmdshell
           xSQLServerConfiguration($Node.Nodename+"xp_cmdshell")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                OptionName = 'xp_cmdshell'
                OptionValue = '0'
           }

           #Make sure TCP support is enabled and configured
           xSQLServerNetwork($Node.Nodename+"Network")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                InstanceName = $Node.InstanceName
                ProtocolName = 'tcp'
	        TCPPort = $Node.SqlPort
	        IsEnabled = $true
	        RestartService = $true
           }
        }
    }

After the configuration definition we have the $ConfigurationData mentioned earlier. It’s a great idea to get in the habit of using sections like this. It will make your transition between various environments much easier.

#### Section to customize each DSC deployment ####
$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName = "*"
            PSDscAllowPlainTextPassword = $true
            PSDscAllowDomainUser =$true
            NETPath = "\\FileShare\Install\sxs"
            SourcePath = "\\FileShare\Install\SQL"
            InstallerServiceAccount = Get-Credential  -Message "Credentials to Install SQL Server"
            AdminAccount = "YourAccount"
            BinaryPath = "C:\Program Files\Microsoft SQL Server"
            InstancePath = "C:\Program Files\Microsoft SQL Server"
            SysDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            UserDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            UserDBLogPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            TempDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            TempDBLogPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            BackupPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            SQLService = 'NT Service\MSSQLSERVER'
            SQLAgentService = 'NT Service\SQLSERVERAGENT'
            SqlPort = '1433'

        }
    )
}

The next section details what we’d like the instance name to be as well as what features should be installed. It’s very picky about the feature names, and they don’t line up exactly with a standard command line install. So be careful with what you place here. It won’t install anything incorrectly, just simply cause the configuration not to run and you to lose your mind.

Also in this section, we’re copying over the modules that each node will need in order to perform this configuration. This isn’t necessary when using DSC in pull mode, but that’s a story for a different post.

#Loop through each computer target, setting instance name, features to be installed, and also copying over necessary modules.
ForEach ($computer in $computers) {
   $ConfigurationData.AllNodes += @{
            NodeName        = $computer
            InstanceName    = "MSSQLSERVER"
            Features        = "SQLENGINE"

    }
   $Destination = "\\"+$computer+"\\c$\Program Files\WindowsPowerShell\Modules"
   Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xSQLServer' -Destination $Destination -Recurse -Force
   Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xNetworking' -Destination $Destination -Recurse -Force
}

I know you thought it’d never come, but at last it’s time to actually do something with all of this. We call our “SQLSA” configuration, passing in the $ConfigurationData and specifying to place the resulting .mof file in $OutputPath. After that, configuration is started on each node using Start-DscConfiguration and calling the .mof that was just created. Lastly, the node is tested to make sure it’s not out of compliance.

#Prepares the configuration's .mof file to be pushed.
SQLSA -ConfigurationData $ConfigurationData -OutputPath $OutputPath

#Push the configuration to each computer and start the configuration.
foreach($Computer in $Computers)
{

    Start-DscConfiguration -ComputerName $Computer -Path $OutputPath -Verbose -Wait -Force
}

#Make sure each node is now incompliance
foreach($Computer in $Computers)
{
    Test-DscConfiguration -ComputerName $Computer
}

If all goes well, your output will lack red and eventually will end in a message stating that the configuration tests as “True”.

And that’s all there is to it! Not so scary after all. I deployed my first DSC SQL Server while making tea and wondered why I’d been doing it any other way…

############################################################################################################################
# SQL Server Single Instance Push
# Author: Landon Fowler
# Date 01/2018
#
# Based off of Microsoft example file
#
# Prerequisites: Unzip the SQL ISO to your file share
#                If required, copy the sxs folder to your file share for .NET Framework
############################################################################################################################

#Computers to target
$computers = 'YourComputer'
#Local path on your computer to which the .mof file will be exported
$OutputPath = 'C:\Install\DSCLocal'

$cim = New-CimSession -ComputerName $computers

#Set LCM configuration for target nodes
[DSCLocalConfigurationManager()]
Configuration LCM_Push
{
    Param(
        [string[]]$ComputerName
    )
    Node $ComputerName
    {
    Settings
        {
            AllowModuleOverwrite = $True
            ConfigurationMode = 'ApplyAndAutoCorrect'
            RefreshMode = 'Push'
            RebootNodeIfNeeded = $True
        }
    }
}

#Apply the above LCM settings to each target node
foreach ($computer in $computers)
{
    $GUID = (New-Guid).Guid
    LCM_Push -ComputerName $Computer -OutputPath $OutputPath
    Set-DSCLocalConfigurationManager -Path $OutputPath  -CimSession $computer
}

#Detail the configuration that will be pushed
Configuration SQLSA
{
    Import-DscResource -Module xPSDesiredStateConfiguration
    Import-DscResource -Module xSQLServer
    Import-DscResource -Module xNetworking

    Node $AllNodes.NodeName
    {
        # Set LCM to reboot if needed
        LocalConfigurationManager
        {
            AllowModuleOverwrite = $true
            RebootNodeIfNeeded = $true
        }
        
        # Not necessary when using SQL 2016 on Windows Server 2016.
        WindowsFeature "NET"
        {
            Ensure = "Present"
            Name = "NET-Framework-Core"
            Source = $Node.NETPath
        }

        #Create firewall rule for our instance
        xFirewall Firewall
        {
            Name                  = 'SQL_Instance'
            DisplayName           = 'SQL Instance Firewall Rule'
            Ensure                = 'Present'
            Enabled               = 'True'
            Profile               = ('Domain', 'Private', 'Public')
            Direction             = 'Inbound'
            RemotePort            = 'Any'
            LocalPort             = $Node.SqlPort
            Protocol              = 'TCP'
        }
        
        #Begin actual SQL setup
        if($Node.Features)
        {
           xSqlServerSetup ($Node.NodeName)
           {
               DependsOn = '[WindowsFeature]NET'
               SourcePath = $Node.SourcePath
               SourceCredential = $Node.InstallerServiceAccount
               InstanceName = $Node.InstanceName
               Features = $Node.Features
               SQLSysAdminAccounts = $Node.AdminAccount
               InstallSharedDir = $Node.BinaryPath
               InstanceDir = $Node.InstancePath
               InstallSQLDataDir = $Node.SysDBPath
               SQLUserDBDir = $Node.UserDBPath
               SQLUserDBLogDir = $Node.UserDBLogPath
               SQLTempDBDir = $Node.TempDBPath
               SQLTempDBLogDir = $Node.TempDBLogPath
               SQLBackupDir = $Node.BackupPath
           }

           #Create a database
           xSQLServerDatabase ($Node.NodeName+"Diagnostics")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                Name = "Diagnostics"
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                Ensure = "Present"
           }

           #Disable the builtin sa account
           xSQLServerLogin($Node.Nodename+"sa")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                Name = 'sa'
                Disabled = $true
           }

           
           #Disable an option such as xp_cmdshell
           xSQLServerConfiguration($Node.Nodename+"xp_cmdshell")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                OptionName = 'xp_cmdshell'
                OptionValue = '0'
           }

           #Make sure TCP support is enabled and configured
           xSQLServerNetwork($Node.Nodename+"Network")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                InstanceName = $Node.InstanceName
                ProtocolName = 'tcp'
	        TCPPort = $Node.SqlPort
	        IsEnabled = $true
	        RestartService = $true
           }
        }
    }
}

#### Section to customize each DSC deployment ####
$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName = "*"
            PSDscAllowPlainTextPassword = $true
            PSDscAllowDomainUser =$true
            NETPath = "\\FileShare\Install\sxs"
            SourcePath = "\\FileShare\Install\SQL"
            InstallerServiceAccount = Get-Credential  -Message "Credentials to Install SQL Server"
            AdminAccount = "YourAccount"
            BinaryPath = "C:\Program Files\Microsoft SQL Server"
            InstancePath = "C:\Program Files\Microsoft SQL Server"
            SysDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            UserDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            UserDBLogPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            TempDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            TempDBLogPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            BackupPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            SQLService = 'NT Service\MSSQLSERVER'
            SQLAgentService = 'NT Service\SQLSERVERAGENT'
            SqlPort = '1433'

        }
    )
}

#Loop through each computer target, setting instance name, features to be installed, and also copying over necessary modules.
ForEach ($computer in $computers) {
   $ConfigurationData.AllNodes += @{
            NodeName        = $computer
            InstanceName    = "MSSQLSERVER"
            Features        = "SQLENGINE"

    }
   $Destination = "\\"+$computer+"\\c$\Program Files\WindowsPowerShell\Modules"
   Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xSQLServer' -Destination $Destination -Recurse -Force
   Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xNetworking' -Destination $Destination -Recurse -Force
}

#Prepares the configuration's .mof file to be pushed.
SQLSA -ConfigurationData $ConfigurationData -OutputPath $OutputPath

#Push the configuration to each computer and start the configuration.
foreach($Computer in $Computers)
{

    Start-DscConfiguration -ComputerName $Computer -Path $OutputPath -Verbose -Wait -Force
}

#Make sure each node is now incompliance
foreach($Computer in $Computers)
{
    Test-DscConfiguration -ComputerName $Computer
}

10 thoughts on “Installing SQL Server Using Desired State Configuration”

Add yours

  1. Pingback: Getting Started with PowerShell Desired State Configuration (DSC) – landonfowler.com

  2. Hi Landon fowler,

    Hope you are doing great..

    Could you please provide the DSC script for the SQL integration service (SSIS) installation? I need to install 10 SQL integration services in different servers.
    Thanks
    Babu

    Reply

    1. Hi, and thanks for your question. I believe that you can modify the script to include SSIS simply by changing the value under “Features”. The link below has SSIS listed as “IS”, so it would likely be something like ‘Features = “SQLENGINE,IS”‘. However, I have not tested this personally. Good luck, and let me know how it goes!

      https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-2017#Feature

      Reply

      1. You are correct, IS is feature that can be specified. It works well. Just don’t forget to specify directories; otherwise, it might default to something you don’t want. Service credentials too, if you omit them, local service credentials will be created.

        Reply

  3. Hi Landon,
    Your post has given me the confidence to begin working with DSC. I am working with the script you posted, and hoping you can tell me what needs to be done to get any SPs or CUs installed at the same time. Google has not been helpful so far.

    Thanks again for the post,
    John

    Reply

    1. Hi John, I’m so happy you’re getting into DSC! I really enjoy it, and I think you will too. For adding in SPs or CUs I think your best bet will be to slipstream it with the installation files. That way, when you point at the installation media directory it will all be packaged together and ready to go.

      Hope this helps! Let me know if you have any other questions.

      Reply

  4. Hi,
    Is there a way to give credentials without specifying ‘PSDscAllowPlainTextPassword = $true’. If there is not, let me know how I can secure my mof file with my password in it in plain text.

    Reply

Leave a Reply to Landon Fowler Cancel reply

Your email address will not be published. Required fields are marked *

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑