Powershell

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!