sql-server

Pre-Check: SQL Deployments

SQL Padre SQL , ,

Following the introductory post of my series Optimize SQL Server Deployment with PowerShell and DBA Checks, after much trial and error I have a working set of deployment PowerShell scripts that I would say “do their job”. My deployment process is laid out into 3 scripts: Pre-Check, Install and Post-Check. These are modular based inteneded to be executed one script at a time. In the future, I may wrap these in a callable function if the need arises.

Pre-Check in my mind is preparing the OS layer for SQL Installation. This include OS level settings, things needed at the OS level and ensuring volumes are correctly configured for SQL Server.

These steps are what my company does, not necessarily what is best for everyone; but it is what is best for us. Because my copmany uses “privledged accounts” and the requirement for Local admin rights of PowerShell, my be method of implementing these scripts is to be logged into a “jump” server with my Privledged Account which has Local Admin rights to execute these scripts against a new server.

  1. Remotely install PowerShell Management Scripts
    • SQL Server
    • DBA Tools
    • Install Windows Updates
  2. Check OS for any pending updates
  3. Validate attached volumes intended for SQL Data, Log and Temp database files are formated 64K
$ServerName = 'MyTestServer'
$PSSession = New-PSSession -ComputerName $ServerName
Invoke-Command -Session $PSSession -ScriptBlock{
    Install-PackagePRovider -Name NuGet -MinimumVersion 2.8.5.201 -Force
    Set-Repository -Name PSGallery -InstallationPolicy -Trusted
    Install-Module -Name PSWindowsUpdate -Scope Allusers -Force
    Install-Module -Name SqlServer -Scope Allusers -Force
    Install-Module -Name Dbatools -Scope AllUsers -Force
}
Invoke-COmmand -Session $PSSession -ScriptBlock {
     Install-WindowsUpdate -MicrosoftUpdate -AcceptAll -Confirm -AutoReboot
}

Get-CimInstance -ComputerName $ServerName -ClassName Win32_Volume|Select-Object Name,Filesystem,Label,BlockSize

Optimize SQL Server Deployment with PowerShell and DBA Checks

SQL Padre Powershell, SQL, SQL Toolbox , , , ,

In my current role I inherited a very long and complex PowerShell script that actually installed SQL Server. This script was almost 1500 lines of code and it scared me. But to my surprise there was a way to install SQL without clicking check boxes! You would set a few parameters in a config script like name of server, service accounts and volume information to name a few. Then of course execute the 1500 hundred lines of code, one script block at a time. Nice thing about this script is you could setup multiple block sets of parameters and actually perform multiple installations at the same time. I could actually build a 3 node cluster in time it took to do just one server! Believe it or not, in my 20 years of managing SQL Server I have only used the instalation wizard to install SQL Server. So, unlike most of my readers this was brand new to me.

In trying to learn more about this “wizardary” known as Desired State Configuration, I like many before me decided to read. And I read to only get more confused. DSC is a very complicated and SQL DSC is even more complicated at least to me. The more I read about DSC and the more I used the script the more I realized there has to be a better way. As time passed, requirements changed, specs of servers changed, the DSC script had to be changed. It got to the point that it would fail move often than succeeding. I understood the concept; but apparently I was not a DSC PowerShell developer.

So I took it upon myself to develop a new method of remotely installing SQL Server using PowerShell, DBA Tools, DBA Checks and a few little home grown functions.

First I laid out the steps that would need to be done in any SQL Installation. These really are straight forward.

  1. Pre-Check — way to verify server OS, volumes, and patching
  2. Install SQL SQL Server — perform this task per our companies policy
  3. Post-Check — configuration, installation of tools needed by DBA Team and adding monitoring
  4. Validation — determine a method to validate SQL is running per CIS standards.

Each Step will be elaborated on in future blog posts with.