Introduction
In this blog post, we will audit the dbatools command Install-DbaMaintenanceSolution. I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely Install-DbaMaintenanceSolution is the key to simple, reliable, and comprehensive SQL Server maintenance tasks through the easy deployment of Ola Hallengrin’s maintenance solution.
dbatools is a community project full of bright minds. This audit series is more an informational series on useful capabilities and considerations rather than rigorous testing. This is to inspire even greater use of PowerShell (and by extension dbatools) as I believe it will assist in automating many tasks as a DBA.
Script Overview
Script Name: Install-DbaMaintenanceSolution
Author: Viorel Ciucu
Description: This script will download and install the latest version of SQL Server Maintenance Solution created by Ola Hallengren.
Testing Environment Setup
I created a multi-subnet lab with three developer edition SQL Server instances, one SQL Express and a network share based on Jonathan Kehayias’s blog post Configuring a Multi-Subnet SQL Server Test Environment.
Step-by-Step Execution
1) Download and Install
You are probably familiar with dbatools at this point, but if not now is the perfect time to familiarize yourself with the install and use of the module. On your test environment you can open windows PowerShell or the PowerShell ISE, or visual studio code, and run the following scripts. These will install Dbatools and add it to the path for future projects as well as import it into the current session for the purpose of testing the scripts of this blog post.
Install-Module Dbatools Import-Module Dbatools
2) Initial Setup
To save both me and you time, I will skip the test environment steps since they are identical here to my last post. Instead, I will share Ola Hallengren’s website if you are not familiar. This has a detailed breakdown of all of the switches and capabilities of the solution, of which there are many that I will not be covering in this post.
Now that we have the environment set up and you have an idea of the capabilities, we can show off Install-DbaMaintenanceSolution in a few simple scenarios.
Scenario 1: A Fresh Install
This is my favorite example to discuss because it is the most common for many DBAs and why they would pick this script up. The time savings will be evident after your first use (as well as in this post). I do not know how long your maintenance rollout takes currently, but if it is not automated in some way, then your time savings through using this command will be huge.
First we define our targets and which options we want. I will build this as close to the Straight Path recommended standard as we can get. There are more we typically add, but this will be a great start for most environments. Also, I am making use of Splatting in Powershell. If you are new to PowerShell, here is a quick resource about Splatting, otherwise it is just a simple way to define the parameters of the command and then use them. Great for readability, especially in blog posts!
$SPLAT = @{
Sqlinstance = 'Labsql1'
Database = 'DB_admin'
BackupLocation = '\\LabShare\SQLBackups'
Cleanuptime = 336
Logtotable = $True
Installjobs = $True
AutoScheduleJobs = $True
}
This $splat variable holds all of the switches that would get you close to the default maintenance deployment from us, at least in terms of Ola Maintenance. Every environment is different, so I recommend trying it out in test before moving to production, but the parameters are the following:
- SQL instance: LabSQL1 is my first test environment, this could also be an array of targets if desired. This is where maintenance will be installed.
- Database: This is the database that will house the maintenance solution stored procedures and tables. It will not create the database if it doesn’t exist already.
- Backuplocation: This is the backup location for all four backup jobs. If it is a network share, I recommend ensuring that SQL is being run with a low privilege domain account and that the account has write permissions on the network share backup directory.
- Cleanuptime: The time in hours that will be used to clean up old backups. Anything older than 2 weeks will be cleared in this case.
- Logtotable: This enables the use of the CommandLog table where all ola commands with the switch enabled will log their run details. Picture below to get a peek into what that looks like:
- InstallJobs: This is just a switch to tell the script whether or not to deploy the jobs or not. Without it you would just get the stored procedures.
- Autoschedulejobs: Another parameter that helps us to align with industry standards here. There are many options, ‘Weeklyfull’ means there will be 1 weekly full, daily diffs, and 15-minute log backups. Since we didn’t specify the start time switch it will check if anything is happening at 1:15, adjusting by one hour until the timeslot is clear if the initial spot isn’t open. System backups happen every day and other tasks.
To summarize, we should expect a brand new maintenance rollout on my LabSQL1 instance, with stored procedures loaded into my target table, backups jobs pointed to my share with a two week cleanup time, the logtotable switch enabled on all maintenance jobs, and an automatic schedule created for all jobs. What does the entire script look like? I added the sqlcredential parameter in there just in case you need it, all you would need to do is remove the ‘#’ and reference the previous post to get the script I shared to store credentials!
$SPLAT = @{
Sqlinstance = 'Labsql1'
Database = 'DB_admin'
BackupLocation = '\\LabShare\SQLBackups'
Cleanuptime = 336
Logtotable = $True
Installjobs = $True
AutoScheduleJobs = ‘WeeklyFull’
# SqlCredential = $credential
}
Install-DbaMaintenanceSolution @splat
As you can see, it is very simple. I will add a stopwatch and see how long it takes. We will review in the results section, but here is the final code:
$sw = [system.diagnostics.stopwatch]::startNew()
$SPLAT = @{
Sqlinstance = 'Labsql1'
Database = 'DB_admin'
BackupLocation = '\\LabShare\SQLBackups'
Cleanuptime = 336
Logtotable = $True
Installjobs = $True
AutoScheduleJobs = ‘WeeklyFull’
# SqlCredential = $credential
}
Install-DbaMaintenanceSolution @splat
$sw.stop()
"Ola Rollout completed - $($sw.Elapsed.Minutes) minutes, $($sw.Elapsed.Seconds) seconds."
Scenario 2: Fixing a Broken Deployment
A lot of the groundwork is already here, so imagine someone decided to bork up the commands and schedules of all your maintenance jobs. A replacement for a destroyed deployment is as simple as taking the original script I shared above adding just one new parameter, < ReplaceExisting = $true>. That script looks like this:
$sw = [system.diagnostics.stopwatch]::startNew()
$SPLAT = @{
Sqlinstance = 'Labsql2'
Database = 'DB_admin'
BackupLocation = '\\LabShare\SQLBackups'
Cleanuptime = 336
Logtotable = $True
Installjobs = $True
AutoScheduleJobs = ‘WeeklyFull’
# SqlCredential = $credential
Replaceexisting = $true
}
Install-DbaMaintenanceSolution @splat
$sw.stop()
"Ola Replacement completed - $($sw.Elapsed.Minutes) minutes, $($sw.Elapsed.Seconds) seconds."
To emphasize the pain fixed, I went ahead and ran a simple script to set the command text to all my jobs to ”. Here is the second instance with the broken rollout:
Results
Ola Hallengren’s maintenance is an industry standard for a good reason. It is simple, effective, and feature rich. The first scenario is adding maintenance to a fresh server. Here are the run results:
That was quick! Did anything even happen? Looks like the backup jobs have the correct location and cleanup time:
What about schedules?
Sure enough, one second to deploy the stored procedures, create the jobs and schedules. Impressive! I’m sure you can imagine the time savings when adding this to a complete maintenance rollout where alerts are enabled, other jobs are created, job commands are modified, etc.
What about the replacement?
Same story, one second. That is blazing fast, especially compared with trying to sift through the changes in the GUI. And to check, sure enough, the commands are back:
Review and Analysis
This command should be one of the first in a DBA’s toolbox as they transition to PowerShell or start using dbatools. This is one of those specific commands that opens up the world of automation by giving the DBA a specialized tool for a necessary task. Every instance needs maintenance and this enables maintenance rollouts measured in seconds rather than minutes or hours.
Warnings and Caveats
The warning here will spread across all of the commands we audit as well as anytime you use PowerShell. This script rolls out maintenance and some of those tasks can be resource intensive, which would be especially bad during times of extreme business load on the instance. Take care that you don’t accidentally lose modifications of ola jobs through a replacexisting run, and make sure you have thought through all the necessary tasks for your environment before the rollout! As always, anything you build should be built in a test environment with unimportant data and the results should be reviewed before deploying to production.
Conclusion
Install-DbaMaintenancesolution is a very useful tool, especially since many of the clients we serve come to us with less-than-ideal maintenance configured. This tool makes it relatively easy to install a standard set of tasks that should be used on pretty much every instance. If you’re like me, spending more than 1 minute on deploying maintenance per new SQL instance and deploying maintenance more than once a month, Install-DbaMaintenanceSolution will be your time-saving hero.
