Install SQL Server…FAST!!

Automation! It is the magic behind all the cool stuff we see lately. It could be a timer that turns our front porch light on at sunset. It could be a motion sensor that turns a light on when you walk in a room. Or it could be a routine that runs when you ask your Amazon Echo or Google Home to turn on the TV. All of those examples are cool (feel free to ask some of my friends who have heard me ramble about it), but this article is about a way cooler automation, SQL Server installs. This month’s TSQL Tuesday is all about automations that made your life easier.

A little over a year ago, I made the jump from primarily being a developer, which I have been most of my career, to primarily being a DBA. The first task my new boss asked me to do is create a step by step set of instructions for standing up a new SQL server. One of the reasons I have the job I currently do is there wasn’t a person dedicated to handling the data environment. This had created a hodgepodge of different configurations for SQL servers. Things like different drive letters depending on who setup the server. The directories that were setup for storing the data, transaction logs or tempdb. That was just on the outside. Maximum memory settings, MAXDOP setting or cost threshold were not following a standard either. Then some of the servers had Adam Machanic’s(t) sp_whoisactive, or Brent Ozar’s(t) First Responder Kit (FRK) and some didn’t. To be clear, this came to be over years and multiple people trying to keep everything going.

So when I started working on this set of instructions, the first thing I did was go look at what is available from dbatools. This is and continues to be my go to for automating most anything in SQL. dbatools not only helps me when running one off tasks like backing up and restoring, it also helps me collect metrics on the servers. I have 3 different jobs that collect and store server parameters, disk space usage and database usage. I will be putting out a set of blog posts with these scripts attached.

So now to the cool stuff, installing SQL Server in less than 20 minutes.

There are 3 total files that are needed for this magic. The first and one you should already be aware of is the ConfigurationFile.ini. This has been helping DBA’s for years to apply the same settings to SQL installs. I have a configuration file for all the versions of SQL in our environment (there are still 2012 servers I need to stand up for dev environments). The configuration file holds settings like directory paths, authentication mode, update settings and more depending on the version of SQL you are installing. More recent versions are including best practices settings such as automatic setting for max memory.

The second file is a PowerShell script that is run on the server you are doing the install from. This script handles a couple different steps. It opens the standard ports in Windows Firewall that are needed for SQL Server. It verifies if the install media is available (or mounted) and stops the script with a warning if not. And finally, it starts the install with some standard parameters, such as the config file, the SA password and the password for the SQL service account. Once the setup is done, I restart the server knowing that I don’t have to do anymore of the standard configs.

The third and final file can be run from any system that can connect to your SQL Server. This is another PowerShell script that will finalize the standardization. This where I install our standard tools (sp_whoisactive, FRK, Ola Hallengren’s(t) Maintenance Solution). I install all of these using DBA Tools as this gives me a current version. The rest of the scripts adds the rest of our customizations. An example of this is disabling the backup component of Ola’s maintenance solution. We do this because our standard backups are handled through Veeam, however we do have scenarios that require SQL native backups, and as you know, Ola’s solution knocks this out of the park. Other examples include setting up our operators for alerts, our mail profiles, MAXDOP, parallel cost threshold, etc. The last step of the script inserts a record into our server inventory table. This is done so that the nightly jobs know to retrieve information from this new server.

In the end, using a few different tools all pulled together leveraging the power of PowerShell (see what I did there 😊 ), there is limitless potential on the amount of automations we can do. These automations will allow us the time to work on the more advanced (and hopefully more fun) projects. Please reach out if you would like a copy of the scripts mentioned in this blog.

SQL, T-SQL Tuesday