Category: T-SQL Tuesday

Home / Category: T-SQL Tuesday

So for this T-SQL Tuesday, Lisa (@LisaGB_sql) asked us to talk about

something else we learned while presenting. When you think of presenting, you would think the entire process is all about things you already know. I mean, if you didn’t know it, why would you be presenting in the first place. I say that because it is why it took me so long to do my first presentation. There are a lot of things I learned for that first presentation, but what follows is the something I would have never expected.

My first presentation was put together with my good friend Dan White (@crashdan). I was just in my first job as a DBA after being a developer most of my career. We did a presentation on real world use cases for DBA Tools. For those that don’t know what DBA Tools is, it is a community created toolset for SQL written in PowerShell. I have said before, and will continue to say, that it is the way I became a successfully transition to being a DBA. The whole community supports it, and it helps soften the learning curve for most things SQL. While working on the presentation, there were things I learned about standards across the toolset, such as common parameter names, common command names and being able to leverage the output from the commands.

But what I will always remember about the first time we gave the presentation at SQL Saturday is not only a piece of knowledge, but also how I learned it. We were fortunate to have a few friendly faces in the audience, most in the front row (still not sure how I feel about that). What I found out after was that there were multiple published MS MVP’s in the audience. After the presentation, one of those MVP’s, Sarah Dutkiewicz (@sadukie), came up to provide some pointers on the presentation, but also to help teach me a couple things about PowerShell that I did not know. As part of the presentation, one of the things I call out to help make things easier is leveraging profiles within PowerShell. This gives you the ability to write functions that you use often. Things like getting an array of all of the SQL servers in your environment, or your own custom logic functionality. What I did not know is that was only one of a multitude of profiles you can work with. While notepad $Profile will open your current user profile (Current User; Current Host), there are 3 other profile files. You also have Current User; All Hosts, All Users; Current Host and finally, All Users; All Hosts. These different profiles allow for easy distribution of functions across your entire team. As we all know, being able to leverage this kind of standardization is huge. You can read about profiles here, https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_profiles.

There are a couple things that this learning experience helped me with. It reinforced how powerful the #sqlfamily is and dedicated the members of the “family” are to helping everyone continue to grow and increase our skills. It also helped get over the stage freight and “imposter” syndrome that I had. Having someone who is an MVP on the core of the topic you are presenting on tell you that you did a good job, and here are some things to help is HUGE. Sarah treated me like an equal, even though her knowledge was way more than I had on the topic. What I learned while preparing and giving the presentation is not only do you have the opportunity to share your unique viewpoint on your topic, but you also have the chance to learn even more, and not just about your topic.

By Josh

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