How to Assess Your SQL Server Environment

Give me six hours to chop down a tree and I will spend the first four sharpening the axe.

Abraham Lincon

Welcome back to the Two Weeks to A Healthy SQL Server series of posts! This is the official “Day One” post. This first step may not be the most glamorous or the biggest payoff, but it is almost certainly the most important.

People always say that the best place to start is at the beginning. If you don’t know what you need to fix, how can you know where to start? So that’s the goal today.

Day one – let’s not “fix” anything today. Let’s introduce a few concepts and get you to identify your SQL Servers, assess your SQL Server’s health and know what needs to be fixed. This is a learning, prep, and self-assessment day.

The three steps to assess your SQL Server Environment are:

1. Identify
2. Collect and Assess
3. Prepare

1. Find and Identify Your SQL Servers

You need to find your SQL Servers first! I’m still a big fan of the MAP Toolkit from Microsoft. It’s the tool they use to help make sure you are paying them enough money in licenses. But it has a wealth of information. This is an ancient blog post of mine about finding SQL Servers. You can find the latest MAP Toolkit here.

Another way to find SQL Servers is with the DBA Tools PowerShell module’s “Find-DbaInstance” cmdlet. You can read about that here. You’ll see that collection of PowerShell cmdlets in our homework. You DO NOT NEED to be a PowerShell expert to use these tools (and they’ll make you a hero).

Run one of those tools or both. (Find-DbaInstance is quick, fewer permissions needed, fairly effective. The MAP Toolkit will find more and report a LOT more details but will likely need greater permissions. And either way, make sure your network security knows you are doing this! You’ll make them nervous when it scans the network, so you should tell them about it.)

Once you have collected the instance info, store it in a Spreadsheet. Start by recording some basic information (Collect what you care about – but I would start with Server Name | Instance Name | Edition (Enterprise/Standard/Express/Web) | Version Number | Cores | Memory | Major Purpose/Users | Notes (for all the miscellaneous stuff that can later turn into columns.)

This will be useful as you start prioritizing which SQL Servers to run checks on. Possibly use the spreadsheet to track which have issues and which have been fixed as we go.

Later, when we turn this into a video series and class of sorts – we’ll show some examples of collecting this. The major idea here is – Find your SQL Servers (there are always hidden SQL Servers, rogue even 😉 ), Assess them, and get organized.

2. Collect Health Data and Start Assessing

Don’t worry about all of the stuff these tools turn up if you aren’t sure what they mean. Just save your report and use it each day coming up in this series. Don’t let me stop you, though… Hit your favorite search engine and look this stuff up.

There are a few methods of getting this info. I expanded on it greatly in a blog post a few years ago. But I would look to something immediately like the Glenn Berry Diagnostic queries (I love how you can pick and choose what you collect, and the comments tell you what to expect), or SP_Blitz (also informative and great). Save the output from these. And, certainly, open the reports up and start looking and learning. The next 9 “days” worth of posts won’t cover everything on the reports – but we’re covering the basics (recoverability, looking into corruption, quick performance review, SQL Server configurations, etc.), and you’ll learn looking at your results.

3. Prepare and Learn

In addition to looking at the links above, you should at least start familiarizing yourself with two concepts for now.

DBATools

I believe this is one of the most important tools to a DBA today. Just looking at the command index teaches you what you should care about in a SQL Server health check and what you should do to prepare for a SQL Server migration.

I blogged about this amazing PowerShell Module four years ago, and I believe everything I said then. Check out this session that Straight Path Team Member Jack Corbett delivered to the PASS Summit in 2019. Very informative and helpful.

Ola Hallengren’s Maintenance Scripts.

I won’t make you use his award-winning, well-loved, heavily used, often discussed maintenance solution during these lessons, but I’m going to suggest you do. And learning about his tool, you’ll learn about the maintenance you should be doing as a DBA. So check out his home page and do some learning and reading.

Homework

• Download and review DBA Tools PowerShell Module
• Look at a SQL Server Health Script
  • SP_Blitz
  • Glenn Berry’s Scripts
  • DBA Checks
• Look at Ola Hallengren’s Maintenance Solution to get an idea of the maintenance you should be considering.
• Create your Server inventory
• Begin running the data collectors
• Start reviewing the output.

Next time, we’ll start looking at the output and look at Database Recovery (if we can’t recover it, the rest is all not worth doing.)