SQL

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.

To-do lists

SQL Padre SQL

Something on your “to-do list” that never gets done.

Well, this WordPress prompt is really quite easy to answer: writing blog posts!! Ha ha

I always have great intentions of writing, but I always fall short. I truly thought being a new “empty nester” I would have more time on my hands. Well, that didn’t go as planned.

And the embarrassing thing is I absolutely love training, learning new things and imparting that knowledge. For the past 15+ years, I have been an adult volunteer for the Boy Scouts of America. In that time, I have participated in local, regional and national training courses. I was instrumental in beginning out council’s University of Scouting. I have staffed multiple local, regional, and national training courses for both youth and adults. I have presented multiple times at various SQL Saturdays. I saw all that not to brag, but to show I truly do love training and imparting knowledge. I guess I am more of an in person trainer, not a writer.

One of the hardest part of writing is determining the subject matter. I believe if given a topic, I could figure it all out. Another issue with flogging is the fear that I am writing about things that have been done to death. No one wants to read duplicate topics. It is hard to be original.

I hope to do better. I will set new goals and task myself with doing one blog post a month to start. If I can accomplish that, maybe increase frequency of blog posts. Maybe, haha!

B2B: SQL Checklist

SQL Padre SQL

Working with a SQL Server Checklist

This is the 2nd post in my “Back 2 Basics” series following the introduction of topics.

Part of a DBAs role is to ensure SQL Server is installed and configured properly for optimal performance. Just about every DBA that has any experience validates their SQL installations with a checklist.  For years, my “go-to” checklist was Brent Ozar’s SQL Server Installation Checklist.  It has been a few years since it has been updated; but it still is the standard.  Based off his and many other I wrote my own check list of things to do on installation of a SQL Server.

These are the important things you need to remember when it comes to an installation checklist or a checklist for optimal performance.

For me, if I am given a new SQL instance to manage or installing a new SQL instance these are my personal checklist items:

  1. Server Level Configurations
    1. Enable DAC
    1. Configure TempDB
    1. Max Degree of Parallelism
    1. Cost Threshold for Parallelism
    1. SQL Server MAX Memory
      1. Memory Management
    1. Ad Hoc Workloads
  2. Database Configurations
    1. Individual Volumes for Data (mdf) and Transaction Log (ldf) files
    1. Auto Growth Settings
    1. Auto-Shrink
    1. Auto-Close
    1. Auto-Update Stats
    1. Compatibility Level
  3. Setup Maintenance – I recommend Ola Hallengren Scripts for this.  Ola’s scripts are world famous script used to manage all your databases.  On smaller setups, you can install and configure with a single script and never have to worry again.  However, I strongly recommend you learn the ins & outs of his scripts to customize them for your needs.  You can even extend its functionality with Configuration Tables.
    1. Backups
    1. Integrity Check
    1. Index
  4. SQL Agent Alerting
    1. Database Mail
    1. Configure Operator
    1. SQL Agent Failsafe
    1. SQL Agent Alerts

Revisiting and Restarting

SQL Padre SQL

Today is a new day. I now have a domain (http://sqlpadre.com) and I now have an email address Robert@sqlpadre.com and I hope this is a new beginning. This morning I revisited my old blog posts and discovered that I am not very consistent at blogging. In fact I was pretty terrible. I hope to change that.

Many years ago, I tried to begin two different blogging series called “B2B: Back to Basics” and “8 Weeks of Indexes”. These series were stared over 6 years ago and completely lost steam…translation I dropped the ball. Couple that with the fact two new versions of SQL have been released plus SQL Database in Azure, my blog is way behind the time.

I hope this is truly the beginning of a new chapter for me. I hope to restart both these series, producing one post a week alternating the topics. I have even set reminders.

The first post will be an introduction to both topics and provide an outline of the up coming posts (hey, I’m trying to stay organized).