Powershell

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.

Learning something new!

SQL Padre Powershell, SQL

old-dog-new-tricks

You all know the old saying, “Can’t teach an old dog new tricks”. Well I am here to tell you, I AM old (at least in the IT world) and I can learn new tricks.

After study, testing, studying and testing; I finally passed enough exams to earn my Microsoft Certified Solutions Expert: Data Management and Analytics Certification. (Yeah me!). This is not to brag and this was not required of my job, but a personal endeavor for me to complete it. Yes, I know some people put zero stock in MSFT exams and certificaitons and I understand that, but again this was a personal goal.

So what’s next?

I know in the IT world if you are not always learning something new you will be left behind and become a dinasaur real quick. Since my current job role is primarily “to keep the engine running” and we have over 100 instances of SQL; I felt I needed to expand my toolbelt. The first logical option is Powershell for SQL and DBAtools (http://dbatools.io)

I know there are 100s if not 1000s of blog posts about SQL Powershell, but I hope to document this learning (i.e. take notes) and hopefully I can retain some of this information, because you know I am getting old and such!