Category: SQL

Home / Category: SQL

Install SQL Server…FAST!!

September 7, 2020 | SQL, T-SQL Tuesday | 2 Comments

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.

By Josh

Back in 2012, I attended my first SQL Saturday. I had been working with SQL since 7.0 but didn’t really know about the community. I had just moved back to my hometown Philly from Cincinnati and was determined to get more involved. I volunteered and I ended up just directing people. And then a I had a hallway conversation with someone, and had a break through on a different way to think about SSIS (looking back, not sure how I hadn’t thought about SSIS as RBAR by design). I went to a few SJSSUG meetings that first year. Met Joey D’Antoni(t) before he was the superstar he is now :-).

Then life happened and I couldn’t be as involved as much as I wanted to due to obligations. Then I was able to attend my first Summit in 2014. I had been fighting for it because of how everyone hyped it up. My boss at the time came with me as we were scoping out BI solutions. We were also looking at attempting to implement Service Broker, so we divided and conquered. He went to all the Power BI sessions and I went to the dev track sessions and I attended Allen White’s Service Broker Session (I blame Allen(t) for my love of this underrated feature and Denny(t) and Jonathan(t) for helping me fill where I was confused). That first Summit, I didn’t do the after events, and I to this day regret it. My boss however became a proponent to send other staff after his experience.

I was fortunate to go back to Summit in 2015 with 2 other co-workers, and I vowed to change my experience in regards to the after events. Thanks to someone we met the first night we were there, I attended a lot more events and networked more than I could ever have imagined (Thanks again Justin(t)).

I was fortunate at the time to be working with a vendor who their DBA staff was highly involved in the community, and that just helped change my determination even more. They helped me realize how much I could give back to the community. They are the reason I have presented a session created with someone who has become a close friend at not 1, but 2 SQL Saturdays. That DBA team also helped me realize something that I feel is now more important than ever.

If it was not for PASS, I would not be where I am in not only my career, but my life as well. I have gained a like minded group of friends that when we aren’t talking about SQL, we are talking about life in general. These are people that are always willing to share and help teach others to help make everyone better. These are people that I would do anything I can to help them. Not because of what they did for me or the community, but because of the community, I was able to see them more than just someone who knows SQL.

I don’t know all the in’s and out’s of the behind the scenes. What I do know is that the SQL community will continue because of the people in the community. I also know that as great as the community will be, having an organization for people to find and help get them involved will be even better. And I will do whatever I can to help, whatever the level of volunteering I need to do.

By Josh