Every morning as you sit down at your desk, perhaps before you’ve even got your first cup of coffee, there are certain tasks that you need to check in on.  A lot can happen in your estate overnight and it pays to have a process in place to proactively ensure all is good before someone calls you to tell you it isn’t.

There are many options for completing this process, including full automation, but to get started I’m going to walk you through using the dbatools PowerShell module and the new PowerShell Notebook feature available in Azure Data Studio.

dbatools

dbatools is a PowerShell module that helps bring all the functionality you expect from a tool like SQL Server Management Studio (SSMS) into the PowerShell command line. This is extremely powerful for many reasons: one being automation, and another being that you can target multiple servers at one time. To illustrate, you can open SSMS, connect to your SQL instance, and look at the Job Activity Monitor to see if there were any failed jobs for that instance.  With dbatools you can run Get-DbaAgentJob and get the same information for one or multiple SQL Instances.

dbatools has an amazing presence in the SQL community, and I highly recommend checking out their website (https://dbatools.io) for more details. If you don’t yet have the module installed they have created a guide here to get you started here: https://dbatools.io/install.

PowerShell Notebooks

The second part of this puzzle is notebooks. This technology has been around for a while in the data science community but is now making an appearance in the SQL community thanks to it’s addition to Azure Data Studio (ADS).  Notebooks can contain code written in several languages including Python, Spark and SQL. Just recently PowerShell was also added to the list.

If you don’t have ADS installed you can visit the Microsoft docs for information on how to download the program and some tips to get started: What is Azure Data Studio?.

Notebooks give you the ability to create a document which contains both runnable code and documentation in markdown.  The user can open a notebook, read through the instructions and then execute the code snippet against their server(s).

Notebooks are also rendered really nicely in GitHub. I’ve posted the complete version of my morning checklist notebook so you can follow along at: https://github.com/jpomfret/demos/blob/master/Notebooks/dbatoolsMorningChecklist.ipynb.

Morning Checklist

So now that we’ve introduced the tools we need to complete this morning checklist, we need to also know what we should be looking at.  The obvious answer here is, it depends! Every SQL Server estate is going to be slightly different- there are things that matter to your business users that perhaps don’t matter to mine.  I have created a five step morning checklist, but step five is up to you.  I recommend you fork (a git term to create your own copy of my code) my notebook and tweak it to fit your needs.

The other four steps I think will be relevant to most environments and can really show the power of combining dbatools with PowerShell notebooks.

To run all my checks I’m using the $servers variable which holds a server object for each of my SQL instances.  In my notebook I used Get-DbaRegisteredServer to retrieve a list from my local server group. You could also read in server names from a text file, a csv file or even select them from a database table.  Customize this to however you currently keep track of your servers.

Step One - Backups

This is always going to be number one on my checklists. I like to start the day knowing my data is protected and safe.  Using Get-DbaLastBackup I can view all the backup information for my servers. If I have certain requirements on how often backups should be running, I could filter the results using Where-Object to only see databases without backups that meet those standards. If results are returned I’ll quickly know we have a problem. Remember, you’ll want to change the code to match how often you run backups. Below we are ensuring the databases have had a full backup in the last seven days and a differential in the last day.

Get-DbaLastBackup -SqlInstance $servers |
Where-Object {($_.LastFullBackup.Date -lt ((Get-Date).AddDays(-7))) -or ($_.LastDiffBackup.Date -lt ((Get-Date).AddDays(-1)))} | 
Format-Table SqlInstance, Database, LastFullBackup, LastDiffBackup, RecoveryModel -AutoSize

Here are the results I see in ADS.

Step Two – Good DBCC Checks

Once we know all our data has been backed up and is safe, we’ll move on to corruption checks.  As part of your database maintenance you should be regularly running DBCC CHECKDB against your databases to ensure no corruption exists. The full discussion on when, why and how is outside the scope of this blog post, but for most cases running it once a week is sound advice.

The dbatools command, Get-DbaLastGoodCheckDb, can take our list of servers and see whether checkdb has successfully executed in the last seven days. I can again filter the output using Where-Object so only the databases that don’t meet this requirement are returned.

Get-DbaLastGoodCheckDb -SqlInstance $servers |
Select-Object SqlInstance, Database, DaysSinceLastGoodCheckDb, Status, DataPurityEnabled | 
Where-Object {$_.Status -ne 'OK' -and $_.Database -ne 'tempdb'} |
Format-Table

For my servers, I see that I need to get a few DBCC checks.

Step Three – Failed Jobs

SQL Server agent jobs can be used for a lot of purposes, from running your maintenance jobs to running critical business processes. I highly recommend setting up failure alerts from jobs so you are notified as soon as a critical job fails. However, if you miss an email or an alert, this check will confirm whether any jobs failed overnight.

dbatools has a Get-DbaAgentJob function that can be used to return all jobs and then again we will filter for only the ones that require our attention.

Get-DbaAgentJob -SqlInstance $servers -ExcludeDisabledJobs | 
Where-Object LastRunOutcome -ne 'Succeeded' | 
Format-Table SqlInstance, Name, LastRunDate, LastRunOutcome

I see two different jobs here that need to be checked.

Step Four – SQL Server Error Logs

The SQL Server error logs can contain a wealth of information about what’s happening in our environment, but to manually go through each server and open the latest log each morning would take a lot of time.

The dbatools Get-DbaErrorLog function can be used to return all log events from all servers.  There are two parameters I will mention on this function. First, the -After parameter will filter the log entries to return based on date.  In the example below, I’m returning the last day of events.  Secondly, the -Text parameter: if you run the function without this parameter you’ll get all log messages, which could be a lot if your backups, jobs, etc. all write to the error log. Instead you can specify certain text that the message should contain. In this example we’re just looking for “Login Failed” but this could be expanded to include other common messages.

Get-DbaErrorLog -SqlInstance $servers -After (get-date).AddDays(-1) -Text "Login Failed" |
Format-Table SqlInstance, SqlInstance, Source, Text

Here are a few cases of log errors for one of my instances.

Step Five – Choose your own adventure

As I previously mentioned, step five is your homework.  Depending on your environment, you can add your own checks to ensure the things that are important to you are as expected. I did provide some ideas in the notebook for functions that could be useful.

• Get-DbaAvailabilityGroup- Check your availability groups and determine which node is primary
• Get-DbaDiskSpace- Check the available disk space on your servers and ensure you're not close to running out
• Test-DbaDbOwner- Make sure all your databases are owned by sa, or specify an owner with the -TargetLogin Parameter
• Test-DbaDbRecoveryModel- Check your database recovery models are as expected, including not in pseudo simple mode
• Test-DbaRepLatency- Test replication latency with tracer tokens

Summary

To tie this all back together, we’re using two tools in this post to create an easy to use morning checklist for your environment. Once you have ADS installed and your notebook open you can easily walk through the checks and make sure your environment is as expected.

As an added bonus, the notebook can be saved with the results as well as the code.  You could run all the cells of this notebook, save it to a folder with the date appended and keep a history to show how things looked on that particular morning.

One final thing- as you create or edit your own morning notebook remember that the more details you add in as documentation the easier it is for someone else to follow. When you go on vacation/holiday, someone else can easily pick it up and complete the