Desired State Configuration (DSC) is a powerful management platform in PowerShell that allows you to manage your IT and development infrastructure with configuration as code. With DSC, you can define the desired state of a server or application and automatically configure and manage that state over time.

One of the key benefits of using DSC is the ability to install SQL Server remotely. This not only saves time but also ensures consistency and scalability across your environment. In this article, we will explore how to install SQL Server remotely using DSC, passing minimal information to the script.

Benefits of Using DSC for SQL Server Installation

There are several benefits of using DSC for SQL Server installation:

• Consistency: DSC ensures that all installations of SQL Server on your network are configured to the same settings, preventing configuration drift and ensuring consistency.
• Automation: DSC allows you to automate the process of installing SQL Server on one or more servers, saving time and reducing the potential for human error.
• Scalability: DSC is designed to be scalable, making it easy to 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, maintaining version control and ensuring compliance with your organization’s policies and standards.

Environment Setup

Before we begin, let’s set up the environment:

• CENTRALSERVER: The server from which the DSC script will be initiated.
• VM1: The server where we will install SQL Server using DSC.

SQL Server DSC Module Installation

To start using DSC for SQL Server installation, we need to install the SQL Server DSC module. Here’s how:

1. Open a PowerShell command line or PowerShell ISE tool as Run as Administrator.
2. Run the following code to install the SqlServerDsc module:

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

A folder named “SqlServerDsc” will be created in the location C:\Program Files\WindowsPowerShell\Modules.

Download the SQL Server Media File

Next, we need to download the SQL Server media file and extract it to a shared folder accessible to both CENTRALSERVER and VM1. Here’s how:

1. Download the SQL Server ISO file from the Microsoft site and save it to a local folder (e.g., C:\Downloads).
2. Create a shared folder (e.g., C:\SQL2022) on CENTRALSERVER and grant access to the necessary users.
3. Run the following script to extract the ISO files to the shared folder:

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'

Create a DSC Configuration File

Now, let’s 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. Here’s an example:

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"
        }
    }
}

In this example, we are installing SQL Server with the instance name “MSSQLSERVER” and the “SQLENGINE” feature. The SQL Server media files are located in the shared folder “\\Centralserver\sql2022”. We also specify the SQL Server sysadmin accounts and dependencies on the .NET Framework 4.5 feature.

Compile the DSC Configuration File

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

1. Create a folder named C:\DSC to save the MOF file.
2. Run the following script to compile the DSC configuration file:

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

This script creates the MOF file in the C:\DSC folder with the name VM1.mof.

Apply the DSC Configuration

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

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

This script applies the DSC configuration located in the C:\DSC folder. The installation process will be executed, and DSC will provide verbose output.

Remember to reboot the server if required.

Verify the Installation

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

Summary

In this article, we have explored how to use PowerShell Desired State Configuration (DSC) to install SQL Server remotely from another server. DSC provides a consistent, scalable, and automated approach to SQL Server installation, ensuring that all installations are configured to the same settings. While this article covers the basic resources for SQL Server installation, there are many more resources available that can be customized to meet your organization’s specific needs.