Product and service reviews are conducted independently by our editorial team, but we sometimes make money when you click on links. Learn more.

How to Automate SQL Server Deployments with DSC

By - Source: Toms IT Pro

Find out how to get a SQL Server up and running by the end of the day without the pain of a manual set up.

Credit: ShutterstockCredit: ShutterstockIf your boss came up to you today and said, "We must get a database server running Microsoft SQL Server up and running by the end the day!", how would you react? Would you immediately freak out, drop everything you're doing, begin frantically clicking around to create a new virtual machine and try to figure out where that SQL Server ISO is? If so, this article is for you. Here you'll find out how to be prepared if this happens using PowerShell Desired State Configuration (DSC).

No longer do IT admins have to write long scripts to perform common tasks (like installing a SQL Server). With DSC, we can write a configuration and apply across one or many servers at one time. Lucky for us, Microsoft has already built many DSC resources around SQL Server that we can take advantage of.

MORE: How to Manage SQL Server Users with PowerShell

I'm going to assume you have PowerShell version 5. This version introduced a lot of improvements upon DSC. Now we had to create a DSC configuration. To do this, I used optional configuration data, because the xSqlServerSetup DSC resource require a credential. Without specifying an option to allow plaintext credentials via configuration data, I'd have to set certificates (a topic for another time).

I created a ConfigurationData.psd1 file and added all the necessary information to allow plaintext passwords in the DSC MOF file (which we created later) and the node I applied this configuration on. In this case, it's simply localhost.
    AllNodes = @(
            NodeName = '*'
            PSDscAllowPlainTextPassword = $true
            NodeName = 'localhost'

With the configuration data prepared, I created the DSC configuration.

This contains all of the code that represents what the SQL Server that will ultimately be deployed will look like. Open up a text file and copy and paste the following code in there.
#requires -Version 5
Configuration SQLStandalone
        [pscredential]$SetupCredential ## Need to pass a credential for setup
    ## Download the xSQLServer module from the PowerShell Gallery
    Import-DscResource -Module xSQLServer

    ## Run this DSC configuration on the localhost (gathered from configuration data)
    Node $AllNodes.NodeName
        ## Install a prerequisite Windows feature
        WindowsFeature "NET-Framework-Core"
            Ensure = "Present"
            Name = "NET-Framework-Core"
            Source = "\MEMBERSRV1InstallersWindowsServer2012R2sourcessxs"

        ## Have DSC grab install bits from the SourcePath, install under the instance name with the features
        ## using the specified SQL Sys admin accounts. Be sure to install the Windows feature first.
        xSqlServerSetup 'SqlServerSetup'
            DependsOn = "[WindowsFeature]NET-Framework-Core"
            SourcePath = '\MEMBERSRV1InstallersSqlServer2016'
            SetupCredential = $SetupCredential
            InstanceName = 'MSSQLSERVER'
            Features = 'SQLENGINE,FULLTEXT,RS,AS,IS'
            SQLSysAdminAccounts = 'mylab.localAdministrator'

        ## Add firewall exceptions for SQL Server but run SQL server setup first.
        xSqlServerFirewall 'SqlFirewall'
            DependsOn = '[xSqlServerSetup]SqlServerSetup'
            SourcePath = '\MEMBERSRV1InstallersSqlServer2016'
            InstanceName = 'MSSQLSERVER'
            Features = 'SQLENGINE,FULLTEXT,RS,AS,IS'

You can see that I've commented much of the code to make things easier to understand. If this code doesn't immediately make sense, I encourage you to check out a couple Microsoft Virtual Academy video courses on DSC; Getting Started with PowerShell Desired State Configuration (DSC) and Advanced PowerShell Desired State Configuration (DSC) and Custom Resources.

Notice on line 9 there the Import-DscResource -Module xSqlServer. This is crucial. The xSqlServer DSC module contains all of the SQL Server DSC resources we need for this configuration to run. These resources are dependencies for this configuration. Luckily, these dependencies are easy to get using the PowerShell Gallery. I can open up a PowerShell console and quickly determine if the module I'm looking for is available.
Find-Module –Name xSQLServer

You can see that v2.0 is available. I'll now install this by piping Find-Module to Install-Module.
Find-Module –Name xSQLServer | Install-Module

I can now verify all of the SQL Server DSC resources are available to me.
Get-DscResource –Module xSQLServer

I've got quite a lot of resources available to do just about anything I need with SQL Server. However, in this article, let's just use one to get the SQL Server install automated. That DSC resource is xSQLServerSetup.

Once the configuration is built, we'll then need to create a MOF file for the node that SQL Server will be installed on. To do that, we can "dot source" the configuration into our current session which makes the SQLStandalone DSC configuration available.

Now, I'll execute the configuration which will create a MOF file for the server we intend to install SQL Server on with DSC.

I now have a MOF file called localhost.mof that contains all the instructions DSC needs to install SQL server on my local machine. Let's run it and see what happens.
Start-DscConfiguration –Wait –Force –Path C:SQLStandalone –Verbose

This process will take some time as it's chugging through an entire SQL Server install. If all goes well, the server should either notify that a reboot is needed or will restart automatically depending on how the LCM's reboot setting is configured.
That's it! If you've installed SQL Server manually before, you can tell this is much easier and repeatable. With the proper DSC configuration, this process can be repeated as many times as necessary allowing you deploy one or more SQL servers in no time.