There are no items in your cart
Add More
Add More
Item Details | Price |
---|
Sat Jun 3, 2023
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.
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.
For the demo part, we use two servers named CENTRALSERVER and VM1.
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.
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.
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.
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
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.
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.
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.
Powershellacademy