The original post for this article was back in 2018 but since then I’ve been deploying SQL Server 2017 on Windows Server 2016 (without Desktop Experience) without issue.
Microsoft’s documentation, which is available at https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-with-powershell-desired-state-configuration?view=sql-server-2017 , shows copying the ISO of SQL Server to the local machine.
For our deployments we didn’t do this. We extracted the ISO to a network share. Actually, we setup a network share with as many applications we could locate in a customer’s environment to automate all installations of software, for server nodes or clients.
With Windows Server 2012 R2 or newer, this isn’t as bad as you think. The de-duplication capabilities are pretty decent and only get better with the newer versions.
To start the process we need to have the SqlServerDsc DSC Module on the authoring workstation. *** Remember you also need this on the server to *** The following PowerShell DSC Configuration also uses additional Modules for configuring other stuff on the system as well. ***
Just to let you know, we store user databases, transaction logs, the TempDB and backups all on separate drives.
The PowerShell DSC Configuration also shows configuration of SQL Server’s RAM utilization and also enables TCP connections to the Microsoft SQL Server Database Engine. You know, because you’re probably going to want to connect to the server and use it after you set it up 😉 and not have to use some named pipes path.
PowerShell DSC Configuration Installing and Configuring SQL Server 2017
**************************
THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
This posting is provided “AS IS” with no warranties, and confers no rights.
**************************
$cd = @{
AllNodes = @(
@{
Nodename = 'SQLSERVER-1'
NICInterfaceAlias = 'Ethernet'
NICIPAddress = '192.168.1.22/24'
NICGateway = '192.168.1.1'
NICDNSServerAddresses = '192.168.1.20','192.168.1.21'
PSDscAllowPlainTextPassword = $true
PSDscAllowDomainUser = $true
}
)
}
Configuration SQLDBEngine {
param (
[pscredential]$SACreds,
[pscredential]$DomainCreds
)
Import-DscResource -ModuleName 'PSDesiredStateConfiguration'
Import-DscResource -ModuleName 'xPendingReboot' -ModuleVersion '0.4.0.0'
Import-DscResource -ModuleName 'ComputerManagementDsc' -ModuleVersion '6.0.0.0'
Import-DscResource -ModuleName 'StorageDsc' -ModuleVersion '4.4.0.0'
Import-DscResource -ModuleName 'NetworkingDsc' -ModuleVersion '6.2.0.0'
Import-DscResource -ModuleName 'SqlServerDsc' -ModuleVersion '12.3.0.0'
Import-DscResource -ModuleName 'xSMBShare' -moduleversion '2.2.0.0'
Node $Allnodes.Nodename {
LocalConfigurationManager {
AllowModuleOverwrite = $true
ActionAfterReboot = 'ContinueConfiguration'
ConfigurationMode = 'ApplyAndAutoCorrect'
RebootNodeIfNeeded = $true
}
xPendingReboot RB {
Name = 'RebootWhenNeeded'
}
OpticalDiskDriveLetter CDROM {
DiskId = 1
DriveLetter = 'Z'
Ensure = 'Present'
DependsOn = '[xPendingReboot]RB'
}
Disk DATADrive {
DiskId = 1
DriveLetter = 'D'
FSFormat = 'NTFS'
FSLabel = 'MSSQLDATA'
PartitionStyle = 'GPT'
DependsOn = '[OpticalDiskDriveLetter]CDROM'
}
Disk DATADriveTwo {
DiskId = 2
DriveLetter = 'E'
FSFormat = 'NTFS'
FSLabel = 'LOGS'
PartitionStyle = 'GPT'
DependsOn = '[OpticalDiskDriveLetter]CDROM'
}
Disk DATADriveThree {
DiskId = 3
DriveLetter = 'F'
FSFormat = 'NTFS'
FSLabel = 'TEMPDB'
PartitionStyle = 'GPT'
DependsOn = '[OpticalDiskDriveLetter]CDROM'
}
Disk DATADriveFour {
DiskId = 4
DriveLetter = 'G'
FSFormat = 'NTFS'
FSLabel = 'BACKUPS'
PartitionStyle = 'GPT'
DependsOn = '[OpticalDiskDriveLetter]CDROM'
}
VirtualMemory PageFileInVM {
Drive = 'C'
Type = 'CustomSize'
DependsOn = '[OpticalDiskDriveLetter]CDROM'
InitialSize = 800
MaximumSize = 2048
}
TimeZone EasternTZ {
IsSingleInstance = 'Yes'
TimeZone = 'Eastern Standard Time'
}
###################################
# ADD NETWORKING INFORMATION HERE #
###################################
## Networking Settings
IPAddress LocalNetwork {
IPAddress = $AllNodes.NICIPAddress
AddressFamily = 'IPv4'
InterfaceAlias = $AllNodes.NICInterfaceAlias
}
DefaultGatewayAddress LocalGateway {
Address = $AllNodes.NICGateway
AddressFamily = 'IPv4'
InterfaceAlias = $AllNodes.NICInterfaceAlias
DependsOn = '[IPAddress]LocalNetwork'
}
DNSServerAddress LocalDomainControllers {
Address = $AllNodes.NICDNSServerAddresses
AddressFamily = 'IPv4'
InterfaceAlias = $AllNodes.NICInterfaceAlias
DependsOn = '[DefaultGatewayAddress]LocalGateway'
}
WindowsFeature NetFramework45 {
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
DependsOn = '[VirtualMemory]PageFileInVM'
}
# SQL SERVER FOLDERS
WaitForVolume ddrive {
DriveLetter = 'D'
DependsOn = '[Disk]DATADrive'
RetryIntervalSec = 20
RetryCount = 20
}
WaitForVolume edrive {
DriveLetter = 'E'
DependsOn = '[Disk]DATADriveTwo'
RetryIntervalSec = 20
RetryCount = 20
}
WaitForVolume fdrive {
DriveLetter = 'E'
DependsOn = '[Disk]DATADriveThree'
RetryIntervalSec = 20
RetryCount = 20
}
WaitForVolume gdrive {
DriveLetter = 'G'
DependsOn = '[Disk]DATADriveFour','[WaitForVolume]fdrive','[WaitForVolume]edrive','[WaitForVolume]ddrive'
RetryIntervalSec = 20
RetryCount = 20
}
File MSSQLDBFolder1 {
DestinationPath = "D:\MSSQL"
Ensure = "Present"
Type = "Directory"
DependsOn = '[WaitForVolume]gdrive'
}
File MSSQLDBFolder2 {
DestinationPath = "D:\MSSQL\Data"
Ensure = "Present"
Type = "Directory"
DependsOn = "[File]MSSQLDBFolder1"
}
File MSSQLDBFolder3 {
DestinationPath = "D:\MSSQL\Backup"
Ensure = "Present"
Type = "Directory"
DependsOn = "[File]MSSQLDBFolder1"
}
File MSSQLLogsFolder1 {
DestinationPath = "E:\MSSQL"
Ensure = "Present"
Type = "Directory"
DependsOn = '[WaitForVolume]gdrive'
}
File MSSQLLogsFolder2 {
DestinationPath = "E:\MSSQL\Logs"
Ensure = "Present"
Type = "Directory"
DependsOn = "[File]MSSQLLogsFolder1"
}
File MSSQLTempDBFolder1 {
DestinationPath = "F:\MSSQL"
Ensure = "Present"
Type = "Directory"
DependsOn = "[File]MSSQLLogsFolder1",'[WaitForVolume]gdrive'
}
File MSSQLTempDBFolder2 {
DestinationPath = "F:\MSSQL\TempDB"
Ensure = "Present"
Type = "Directory"
DependsOn = "[File]MSSQLTempDBFolder1"
}
File Transfer {
DestinationPath = "G:\Backups"
Ensure = "Present"
Type = "Directory"
DependsOn = "[WaitForVolume]gdrive"
}
File SQLBackupFolder {
DestinationPath = "G:\Transfer\Backups"
Ensure = "Present"
Type = "Directory"
DependsOn = "[File]Transfer"
}
xSmbShare TransferShare {
Name = "Transfer"
Path = "G:\Transfer"
FullAccess = 'Everyone'
Ensure = 'Present'
DependsOn = '[File]SQLBackupFolder'
}
# SQL SERVER SETUP
SqlSetup BaseInstall {
InstanceName = 'MSSQLSERVER'
Action = 'Install'
SqlSvcStartupType = "Automatic"
BrowserSvcStartupType = 'Automatic'
Features = 'SQLEngine,FullText,Conn'
ForceReboot = $true
SAPwd = $saCreds
SecurityMode = "SQL"
SourcePath = "\\dbat-fs-01a\SoftwareRepository\Microsoft\SQLServer\2017"
InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
InstanceDir = 'C:\Program Files\Microsoft SQL Server'
InstallSQLDataDir = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data'
SQLUserDBDir = "D:\MSSQL\Data"
SQLUserDBLogDir = "E:\MSSQL\Logs"
SQLBackupDir = "G:\Transfer\Backups"
SQLTempDBDir = "F:\MSSQL\TempDB"
SQLTempDBLogDir = "F:\MSSQL\TempDB"
UpdateEnabled = 'False'
PsDscRunAsCredential = $DomainCreds
DependsOn = '[File]MSSQLTempDBFolder2'
}
SqlServerMemory Set_SQLServerMaxMemory_To12GB
{
Ensure = 'Present'
DynamicAlloc = $false
MinMemory = 1024
MaxMemory = 12288
ServerName = $AllNodes.Nodename
InstanceName = 'MSSQLSERVER'
PsDscRunAsCredential = $DomainCreds
DependsOn = '[SqlSetup]BaseInstall'
}
SqlServerNetwork ChangeTcpIpOnDefaultInstance {
InstanceName = 'MSSQLSERVER'
ProtocolName = 'Tcp'
IsEnabled = $true
TCPDynamicPort = $false
TcpPort = 1433
RestartService = $true
PsDscRunAsCredential = $DomainCreds
DependsOn = '[SqlServerMemory]Set_SQLServerMaxMemory_To12GB'
}
}
}
Compiling the PowerShell DSC Config
So Mof Files aren’t everywhere on the developer’s workstations, the following next few lines create a folder under C:\MofFiles with the name of the server your going to provision.
$MofLocation = "C:\MofFiles\$($cd.AllNodes.Nodename)"
if ((Test-Path -Path $MofLocation) -eq $False) {
New-Item -Path 'C:\MofFiles' -Name $cd.AllNodes.Nodename -ItemType Directory -Verbose
}
Executing the next few lines will prompt you for 2 credentials. The first one is to be used for the ‘sa’ account and the other is used to retrieve the files from the network share and is the security context in which the Sql Server Installer runs as. So make sure that domain account is a local admin on the server your running this configuration against.
$saCredential = Get-Credential -Message "Enter what you want the sa password to be"
$InstallerCreds = Get-Credential -Message "Enter Application Installer Creds"
Compile it
SQLDBEngine -SaCreds $saCredential -DomainCreds $InstallerCreds -ConfigurationData $cd -OutputPath $MofLocation -Verbose
Push the PowerShell DSC Configuration to the Server
Since we made changes to the Local Configuration Manager, we have to send those over to the server first. The config will need the server to reboot several times.
Set-DscLocalConfigurationManager -Path $MOFlocation -ComputerName $cd.AllNodes.Nodename -Credential $InstallerCreds -Verbose
After that finishes you can push the config and go to lunch.
Start-DscConfiguration -Path $MOFlocation -ComputerName $cd.AllNodes.Nodename -Credential $InstallerCreds -Force -Verbose