The next post of this series is finally ready to fly. I apologize for the delay between posts, but between the rolling blackouts in Texas during the 2021 Snowpaclypse and a personal health issue, getting this written was a struggle. Let’s get back to it and take a look at the final steps in the client onboarding process.
Building a Maintenance Job List
Now that we’ve taken a look at getting the biographical data for your servers gathered, the next step is to take an inventory of the maintenance jobs that are already set up and running on your servers. We mainly want to see if proper backups are taking place, and whether or not common tasks such as integrity checks and index maintenance jobs exist. At this point we’re not concerned whether or not things are set up correctly, we only want to assess what is or is not happening so we can put together an effective plan for future data protection and preservation.
Gathering this information is easy, while deciding what to do with it will take some thought.
In SSMS, open up your target instance in Object Explorer, then drill down to SQL Server Agent >> Jobs >> Job Activity Monitor.
This tutorial assumes the SQL Server Agent is running on your instance.
Double-click the Job Activity Monitor to open up a new window that should look something like this:
Now you can copy/paste this information into an Excel spreadsheet if you’d like to have a local copy of this information to work with. You’ll be limited to keyboard shortcuts to select all and copy—there’s no right-click “Copy with headers” available in this window.
You can see on my machine I have Ola Hallengren’s SQL Server Maintenance Solution installed to take care of all the basic maintenance tasks, as well as some addition useful maintenance jobs. In your situation, you may find similar results, a collection of Maintenance Plan jobs, or a different set of jobs altogether. Hopefully, you find something that’s already been set up to take care of backups, integrity checks, and index maintenance.
From here, you can see what jobs are active, when those jobs last executed successfully, and get a sense of the job’s schedule. Remember, at this stage of the onboarding process we’re more concerned with what jobs are set up rather than if they are set up properly.
Gathering this information will allow you to make informed recommendations and decisions going forward from this point. You’ll begin to be able to determine if the current job maintenance schedule meets your company’s Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO). If so, great, you’re in good shape! If not, then you’ve got some work ahead of you to get your maintenance jobs in order.
Once you have this information gathered and collated, you’ll be ready to tackle the next step in the onboarding process, which is building a backup frequency list. We’ll take a look at that issue in the next post.