How to deploy SQL Server through PowerShell DSC

Desired State Configuration (DSC) is a new PowerShell feature that manages Windows infrastructure (servers and applications) in both premises and on cloud services. In this article, will guide you how to deploy SQL Server via PowerShell DSC.

DSC is integrated into PowerShell 4.0 and is part of Windows Management Framework. PowerShell DSC is included in Windows 2012 R2, but is available for download and installation by Windows 2008 R2 and Windows 2012.

DSC is declared. The DSC script has no logic on how to perform a specific installation or uninstall. Instead it specifies specific server or application settings.

Imagine you’ve just bought a new apartment, with walls, doors, and windows. You don’t need to worry about the wall or the window position, because it’s the builder’s job. All you need to do is describe the apartment you want, and the builder will use it to build it for you.

DSC works the same way. The specification is contained in the configuration and the DSC runtime acts like a builder, ensuring that the resources on the server are set up at the user’s discretion.

DSC is a relatively new tool. Other configuration management tools such as Chef and Puppet have been on the market for years, and they manage both Windows and non-Windows systems. However, using these tools requires users to learn another language. Script DSC uses new extensions for PowerShell.

Advantages of DSC

– Standardized: Create scripts containing definitions of the different types of services in your structure (such as IIS, databases, file servers) and then use them for all new deployments. You can be sure one thing all servers will be set up exactly the same.

– Acceleration Deployment: Quickly and easily apply DSC configuration to server via PowerShell runtime.

– Detect configuration: DSC provides a solution to determine the active server configuration specified in the scripts and can report or automatically correct deviations.

– Simple: DSC was developed based on PowerShell, so users can fine-tune DSC through PowerShell. However, the scripts have no logic, error handling, but are easy to read.

– Idempotency: You can apply the same DSC configuration without any problems. And if the configuration is updated, only the different settings change.

Components of DSC

– WinRM (Windows Remote Management): Microsoft’s implementations of the standard WS-Management protocol for managing servers using SOAP.

– CIM (Common Information Model): standard to describe the structure and behavior of managed resources (such as storage, networks, software components). WMI is deployed from CIM on Windows.

– MOF (Managed Object Format) files: Contains configuration applied to the destination node.

– Resources: Are the building blocks for DSC configuration. DSC includes a number of built-in resources, such as File and Windows Feature, or in addition users can create their own.

Function of DSC resources

Each DSC resource consists of 3 functions

– Test-TargetResource: This is the first function to be called when the DSC configuration is applied. It returns True / False value based on whether the source is accurate or not. If so, the DSC runtime does not have to perform other operations.

– Set-TargetResource: Called when Test-TargetResource returns False. Responsible for ensuring resources are set according to the specifications stored in the configuration.

– Get-TargetResource: Returns all properties of the resource. Not used in configuration step but used for reporting.

DSC configuration

The configuration below identifies a named folder DSC_Demo exist in C: temp.

When the above PowerShell script is executed, the MOF file has a name localhost.mof created by DSC runtime in directory CreateFolderDemo. If you open the file, you will see a file like the following:

How to deploy sql server via powershell dsc 2

To apply the configuration -and and “make it so“, we use the Start-DscConfiguration command:

Start-DscConfiguration -Path. CreateFolderDemo -Wait -Verbose

How to deploy sql server via powershell dsc 3

Directory does not exist before the script is run, so the function Test-TargetResource will return the value False. Next function Set-TargetResource will be called and the directory will be created.

To display idempotency, if the script runs for the second time, Test-TargetResource will return True and no configuration will take place.

How to deploy sql server via powershell dsc 4

Deploy SQL Server through PowerShell DSC

If used xSqlPs PowerShell module, you can perform SQL Server deployments through PowerShell DSC

First download the module and extract it in the directory $ env: ProgramFiles WindowsPowerShell .

Download the xSqlPs PowerShell module to your computer and extract it here: Download xSqlPs PowerShell (insert link)

Open PowerShell and verify the modules are available by running the command Get-DSCResource:

How to deploy sql server via powershell dsc 5

Next create the configuration. In this example, we install an SQL 2014 instance called DSCInstance on the local computer:

How to deploy sql server via powershell dsc 6

When running the script will create the file localhost.mof in the directory InstallSQLDemo:

How to deploy sql server via powershell dsc 7

Deploy configuration by command:

Start-DscConfiguration -Path. InstallSqlDemo -Wait -Verbose

The above command will run SQL Server installation to create a new DSCInstance database.

How to deploy sql server via powershell dsc 8

Just like FileDemo above, if you try to run Start-DscConfiguration a second time, it will try to complete and no errors will occur:

How to deploy sql server via powershell dsc 9

Note: xSqlServerResource is designed for SQL 2012 and other versions will report installation failures even after the user has finished installing. This is because the resource finds the SQL Setup log file in folder 110, but the SQL Server versions store the log file in a different location.

The fastest way to fix errors is to edit “C: Program Files WindowsPowerShell Modules xSqlPs DSCResources MSFT_xSqlServerInstall MSFT_xSqlServerInstall.psm1 ” and change the directory above line 154 into one of the rows related to your SQL version (100 for Server 2008 and 120 for 2014).

DSC is a great tool to make it easier to manage and deploy servers in the future. However, at this time, DSC is still very new, and only supports basic operations. The SQL Server module does not show many command line utilities available in SQL Setup.

Thus, the article on has shown you how to deploy SQL Server through PowerShell DSC. You can also use DSC to deploy Service Packs or Cumulative Updates to an SQL Server instance. And then if Service Pack is newly released, DBA will update some configuration files and re-deploy, and SQL Server resources will detect only one patch needs to be installed, not the entire deployment.
Before making any important changes on SQL Server, to avoid risks, you should refer to the tutorial article backup and restore SQL Server thereby grasping how to recover SQL Server data.


Add a Comment

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