Automation of SQL Server Install
I was chatting with another Redditer recently regarding automatic Installation of SQL Server using DSC and at the time I couldn't provide him some examples of working Scripts. The conversation went along the lines of SQL Server is too Awkward to automate...
I have tided up any company sensitive material in the below so hopefully this may help some people.
At the moment we use SQL Server DSC to do the install which once you get your head round it works largely the same as most other forms of DSC, that been said I appreciate to any DBA's out there it may not be something you have spent much time looking at.
To start off we need a few things;
- A working Windows Server machine (Windows 2016 + works better for certificate generation)
- You need to be able to Remote Powershell onto the Box
- Have local admin on the Box
- Which must allow you to create certificates and Install Applications
- A pre-extracted SQL Server ISO/Install Directory, you can mount the ISO and pre-extract prior to the running of the DSC step but be aware some security elements within your organisation may prevent the automatic mounting of ISO's. I ran into this problem so instead we maintain a directory of all SQL Server Install Files we need to be copied across to designated box.
- An Orchestration tool that can run Powershell and has access to the box like; Octopus Deploy ,Jenkins ,Team City
- Access to the SQL Server DSC Powershell Module ( I recommend looking at the GIT -Kudos to johlju for been an excellent help)
To start you will need a DSC certificate in 2016 windows server the below code will generate it for you, i will not look at older versions of Windows Server or it would need an entire other post just to explain.
$Server = 'TBPWINDV001'
$command = {
CD c:\
New-Item -Path "c:\" -Name "Temp" -ItemType "directory"
DIR
CD c:\TEMP
$cert = New-SelfSignedCertificate -Type DocumentEncryptionCertLegacyCsp -DnsName "$($env:computername)_PublicKey.cer" -HashAlgorithm SHA256
$cert | Export-Certificate -FilePath "$($env:computername)_PublicKey.cer" -Force
}
Invoke-Command -ComputerName $server -ScriptBlock $Command
The above will run remotely and create the location ( could do with some error handling but this is meant to be minimal) and then create/export the required certificate. This certificate can then be securely moved to a location the below two scripts can access it or remain in place and the below scripts run on the server with the Certificate. Naturally ensure all credentials/certificates are kept secure.
Next you will need two files, the first is the configuration file, this is the file that will receive the variables from the second file and then help create the MOF File. I have removed lots of features from the script to reduce the size but I do things like MaxDop,MaxMemory,Network. These are all available on the GIT link above and are pretty easy to add in, I kept one as an example on top of the standard SQLSetup feature, once you get one right its mostly copy paste.
Configuration SQLInstall
{
param(
[PsCredential] $SetupCredential,
[PsCredential] $SQLSvcAccount,
[PsCredential] $AgtSvcAccount
)
Import-Module -Name SqlServerDsc -RequiredVersion '11.2.0.0'
Import-DscResource -ModuleName SqlServerdsc
node $AllNodes.nodename
{
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
SQLSetup 'InstallDefaultInstance'
{ Action = "Install"
SuppressReboot = $false
ForceReboot = $true
PsDscRunAsCredential = $SetupCredential
InstanceName = $ConfigData.allnodes.InstanceName
Features = $ConfigData.allnodes.Features
SourcePath = $ConfigData.allnodes.SourcePath
SQLSysAdminAccounts = $ConfigData.allnodes.SQLSysAdminAccounts
SQLCollation = 'Latin1_General_Bin'
InstallSharedDir = "C:\Program Files\Microsoft SQL Server";
InstallSharedWOWDir = "C:\Program Files (x86)\Microsoft SQL Server"
InstanceDir = "C:\Anchor\$($ConfigData.allnodes.InstanceName)\SQL";
SQLUserDBDir = "C:\Anchor\$($ConfigData.allnodes.InstanceName)\SQL_DataFiles\DataFiles";
SQLUserDBLogDir = "C:\Anchor\$($ConfigData.allnodes.InstanceName)\SQL_LogFiles\LogFiles";
SQLTempDBDir = "C:\ANCHOR\$($ConfigData.allnodes.InstanceName)\SQL_tempdb";
SQLTempDBLogDir = "C:\Anchor\$($ConfigData.allnodes.InstanceName)\SQL_LogFiles\LogFiles";
BrowserSvcStartupType = 'disabled'
SQLSvcAccount = $SQLSvcAccount
AgtSvcAccount = $AgtSvcAccount
}
SQLServerNetwork 'SQL_Network'
{
InstanceName = $ConfigData.allnodes.InstanceName
ProtocolName = "tcp"
IsEnabled = $true
TCPPort = if($ConfigData.allnodes.alternativeConfigureTCPPort -ne $null)
{$ConfigData.allnodes.alternativeConfigureTCPPort}
else {'1433'}
RestartService = $true
DependsOn = "[SQLSetup]InstallDefaultInstance"
}
LocalConfigurationManager
{
CertificateId = $ConfigData.allnodes.Thumbprint
}
}
}
The second file contains all the variables you want and then creates the MOF File. The MOF file is the instructions to the SQL Install file on what it needs to do.
This requires the user to input the credentials to be used for the relevant accounts, this should be automated so will need to be populated by the tool you use from a secure password safe rather than by manually, I wont be publishing that part.
In short the below takes the first file and passes in the variables specified below combining the two in the final statement to spit out a MOF File.
You will need to make sure you update and identify things like the location of the Certificate, the location of the SourcePath.
Alot of these variables can be pulled in from your appropriate tool or calculated prior to going in to the two files.
Import-Module -Name SqlServerDsc -RequiredVersion '11.2.0.0'
$SetupCredential = Get-Credential -Message 'Please enter the Installer Service Account details '
$SQLSvcAccount = Get-Credential -Message 'Please enter the SQL Service Account details - it must start with CIG\'
$AgtSvcAccount = Get-Credential -Message 'Please enter the SQL Agent Service Account details - it must start with CIG\'
$psloc = $PSScriptRoot
$QAServer = "TBPWINDV001"
$MOFPath = "$psloc\SQLInstall"
$certFileQA = "$psloc\Certificates\$($QASERVER)_PublicKey.cer"
$thumbprintQA = (Get-PfxCertificate -Filepath $certFileQA).Thumbprint
$DSCConfigFile = Join-Path $psloc "SQLInstallConfigurations.ps1"
. $DSCConfigFile
##config data
$ConfigData= @{
AllNodes = @(
@{
NodeName = 'localhost';
CertificateFile = $certFileQA;
Thumbprint = $thumbprintQA;
}
@{
NodeName = "*";
InstanceName = "InstanceName01";
Features = "SQLENGINE";
SourcePath = "C:\Temp\InstallMedia\en_sql_server_2016_enterprise_with_service_pack_1_x64_dvd_9542382";
SQLSysAdminAccounts = @("CIG\SystemBuild_DBA");
SQLMaxDop = 1;
SQLMaxMemory = 4096;
SQLCostThreshold = 50;
SQLRemoteQueryTimeOut = 0;
SQLRemoteLoginTimeout = 30;
SQLRemoteAdminConnections = 1;
SQLSetAgentXP = 1;
SQLSetCLR = 0;
PSDscAllowDomainUser = $true;
alternativeConfigureTCPPort = "not1433";
}
)
}
SQLInstall -OutputPath $MOFPath -ConfigurationData $ConfigData -SetupCredential $SetupCredential -SQLSvcAccount $SQLSvcAccount -AgtSvcAccount $AgtSvcAccount
Once you have a MOF file and all the required parts (install media etc) you need to Run
install-Module -Name "SqlServerDsc" -RequiredVersion '11.2.0.0' -Repository 'PSPrivateGallery' | Install-Module -force
import-module -Name "SqlServerDsc" -RequiredVersion '11.2.0.0'
$mofPath = "C:\temp\SQLINSTALL"
Set-DscLocalConfigurationManager -Path $MOFPath -Verbose -Force
Start-DscConfiguration -Path $MOFPath -Wait -Verbose -Force
This will open your MOF File and run it. If everything is correct and it gets to begin installing you will be able to use this the standard SQLSetup bootstrap log if you do get any errors otherwise you will need to use some DSC error handling and logging to find the problem.
As a run through you should be able to run this manually to begin with to get used to it ( there will need to be some tweaks for you/your company) then you can begin automating it with your relevant automation tool.
The main benefit of all this is the variables which are specific to each server are scripted so our fully automated scripts will determine the Memory/max Dop etc etc and will give the same result every time we rebuild an environment. This means our Dev-Prod servers are build the same. It also means we dont need a DBA/engineer to do an install we can build it into a step in a pipeline as part of the environment spinning up.
There are also loads of other ways to do this and im not saying any are better than any others. Just wanted to share how we use SQL Server DSC to script and install SQL Server.
Hope this helps anyone considering automating SQL Server Installations using DSC