Introduction

As per Microsoft, Desired State Configuration  (DSC) is a management platform in PowerShell that enables you to manage your IT and development infrastructure with configuration as code.  DSC allows administrators to define the desired state of a server or application, and automatically configure and manage that state over time. With DSC, you can define a configuration that specifies the exact software, settings, and policies that should be present on a given machine, and then apply that configuration to one or more servers using PowerShell.

This article will explore how to install SQL Server remotely using DSC (Desired State Configuration), passing minimal information to the script.

Benefits of Using DSC

There are a number of benefits of using DSC. A few are listed here.

Consistency: DSC ensures that all installations of SQL Server on your network are configured to the same settings, which helps to prevent configuration drift and ensures consistency across your environment.

Automation: With DSC, you can automate the process of installing SQL Server on one or more servers, which can save time and reduce the potential for human error.

Scalability: DSC is designed to be scalable, which means you can easily manage hundreds or thousands of servers.

Flexibility: DSC allows you to define the desired state of SQL Server installations using PowerShell scripts, giving you the flexibility to customize the installation process to suit your organization's specific needs.

Version Control: With DSC, you can track and manage changes to your SQL Server configuration over time, which helps you maintain version control and ensure compliance with your organization's policies and standards.

Environment

For the demo part, we use two servers named CENTRALSERVER and VM1.

• CENTRALSERVER - the server from which the DSC script is initiated.
• VM1 - the server where we install the SQL Server using the DSC.

SQL Server DSC Module Installation

Let us prepare the environment for DSC, including installing PowerShell DSC and SQL Server prerequisites in CENTRALSERVER. Open a PowerShell command line or PowerShell ISE tool as Run as Administrator. Then run this code:

# Install PowerShell Desired State Configuration (DSC)
Install-Module -Name SqlServerDsc

You may get this pop-up while installing the SqlServerDsc module. Click Yes to continue.

A folder with the module, named SqlServerDsc, will be created in the location C:\Program Files\WindowsPowerShell\Modules as shown below.

Download the SQL Server Media File

For the demo purpose, we downloaded the SQL server 2022 ISO from Microsoft site  to C:\Downloads. Extract it to a shared folder that is accessible to both CENTRALSERVER and VM1.

In our case, we created a folder named C:\SQL2022 folder in CENTRALSERVER and made it a shared folder granting access to everyone. However, it is advisable to grant access only to the user performing the SQL Server installation. The shared folder in our case is \\Centralserver\sql2022

The below script can be used to extract the iso files to the shared folder \\Centralserver\sql2022.

New-Item -Path \\Centralserver\sql2022 -ItemType Directory
$mountResult = Mount-DiskImage -ImagePath 'C:\Downloads\SQLServer2022-x64-ENU.iso' -PassThru
$volumeInfo = $mountResult | Get-Volume
$driveInfo = Get-PSDrive -Name $volumeInfo.DriveLetter
Copy-Item -Path ( Join-Path -Path $driveInfo.Root -ChildPath '*' ) -Destination \\Centralserver\sql2022\ -Recurse
Dismount-DiskImage -ImagePath 'C:\Downloads\SQLServer2022-x64-ENU.iso'

The output of the script looks like the below one.

Create a DSC Configuration File

Next, you need to create a DSC configuration file that defines the desired state of the SQL Server installation. This file should include the installation options for SQL Server, such as the version, edition, and features to be installed. Resource named Node in the script below should be provided with the server name where the SQL Server must be installed.

In our case, it should be VM1.

Configuration InstallSQLServer
{
Import-DscResource -ModuleName SqlServerDsc
   Node VM1
    {
       WindowsFeature 'NetFramework45'
          {
                Name   = 'NET-Framework-45-Core'
                Ensure = 'Present'
          }
  
      SqlSetup SQLInstall
         {
                InstanceName = "MSSQLSERVER"
                Features = "SQLENGINE"
                SourcePath = "\\Centralserver\sql2022"
                SQLSysAdminAccounts = @("LAB\Administrator","LAB\DBAs")
                DependsOn = "[WindowsFeature]NetFramework45"
}
}
}

This will create the configuration function named InstallSQLServer.

Compile the DSC Configuration File

After creating the DSC configuration file, you need to compile it into a MOF (Managed Object Format) file. This file is used to configure the server and install SQL Server.

Here is the PowerShell script to compile the DSC configuration file:

Create a folder named C:\DSC for saving the MOF file. Then run the compilation.

# Compile the DSC configuration file
InstallSQLServer -OutputPath "C:\DSC"

This script creates the mof file in the C:\DSC folder named VM1.mof

Apply the DSC Configuration

Once you have the MOF file, you can apply the DSC configuration to the server using PowerShell. This will initiate the installation of the SQL Server, and DSC will ensure that the installation is completed according to the defined configuration. Here is an example PowerShell script to apply the DSC configuration:

# Apply the DSC configuration
Start-DscConfiguration -Path "C:\DSC" -Wait -Verbose -Force

The output will look like the below image

Reboot the server if required.

Verify the Installation

After the installation is complete, you should verify that SQL Server is installed and configured correctly. You can do this by connecting to SQL Server using SQL Server Management Studio and verifying that the databases and features you specified in the DSC configuration are present.

Summary

The article provides a step-by-step guide on how to use PowerShell Desired State Configuration (DSC) to install SQL Server in a remote server from another server. This article covers only the basic resources like InstanceName, Features, SourcePath, SQLSysAdminAccounts, etc, however, we can add many more resources like SQL network level configurations, SQL Server additional configurations, etc which we can discuss in another article.