Install-DbaMaintenanceSolution now supports auto-scheduling

If you're wondering about the uptick in SQL Server-related posts, I recently changed jobs and went back to being a SQL Server DBA. For a few years there, I switched to security engineering; I figured I'd try something new and welcomed no longer being an admin. It was nice enough and I got to do a ton of PowerShell, but I didn't like where I worked and left to be a DBA again.

The previous DBA at my new place left great documentation (a first!) but used a mix of Ola's solution and Maintenance Plans for ongoing maintenance, and I wanted to make it all Ola. So I added my servers to Registered Servers, ran Export-DbaInstance, then reinstalled Ola's maintenance solution using Install-DbaMaintenanceSolution.

Well, prior to doing that, I added some features that I needed to Install-DbaMaintenanceSolution in dbatools version 2.0.3. First, I added a recent version of Ola's scripts in dbatools itself in the bin directory. This will help DBAs with offline networks by reducing the number of solutions they have to copy over.

Then, I added auto-scheduling, something that Ola does not offer. If I had to guess why, it's because there are too many factors to consider when scheduling and staggering job executions in large environments. Ola actually addresses scheduling jobs in the FAQ, as follows:

How should I schedule jobs?

The answer depends on your maintenance window, the size of your databases, the maximum data loss you can tolerate, and many other factors. Here are some guidelines that you can start with, but you will need to adjust these to your environment.

User databases:

• Full backup one day per week
• Differential backup all other days of the week
• Transaction log backup every hour
• Integrity check one day per week
• Index maintenance one day per week

System databases:

• Full backup every day
• Integrity check one day per week

I recommend that you run a full backup after the index maintenance. The following differential backups will then be small. I also recommend that you perform the full backup after the integrity check. Then you know that the integrity of the backup is okay.

Cleanup:

• sp_delete_backuphistory one day per week
• sp_purge_jobhistory one day per week
• CommandLog cleanup one day per week
• Output file cleanup one day per week

Auto-Scheduling

As I mentioned earlier, I decided to add auto-scheduling to our install command anyway. I did this mostly for myself, as I work in small environments and can benefit from a generalized yet modifiable scheduler.

Warning: Don't pipe in multiple servers at once, because then they'd all have the same schedule. Execute this command in a loop, adding minutes or hours to each run to stagger the schedules to reduce the strain on resources, especially storage.

By default, when -AutoScheduleJobs WeeklyFull is used and if no other jobs are running at 1:15 AM, this is the schedule it makes:

I decided to run the Weekly Clean and Purge at the same time bc it's not resource intensive. Index Optimization is run 2.5 hours earlier (on Saturday night) than the full backups which will start at 1:15 AM on Sunday morning. Integrity checks start 12 hours earlier than that at 10:45 AM.

Diffs run every night except the night that Fulls are made 👍🏼

You can change the start time if you wish then the subtractions will occur from that start time. Oh, and if anything is scheduled at 1:15 AM on Sunday, it'll keep trying for an open slot each hour until it finds it. So the next try will be at 2:15 AM, then 3:15 AM, etc.

How to use it

You can use the new feature like this:

1$params = @{
2    SqlInstance = "localhost"
3    InstallJobs = $true
4    CleanupTime = 720
5    AutoScheduleJobs = "WeeklyFull"
6    BackupLocation = "\\dc1\backups"
7}
8
9Install-DbaMaintenanceSolution @params

This will install the solution, create the jobs, and schedule full user backups at 1:15 AM on Sunday. Log backups will run every every 15 minutes and diffs will be scheduled each night except Sunday when full backups are performed. The other jobs will be scheduled at the times mentioned above.

And a second example is as follows:

 1$params = @{
 2    SqlInstance = "localhost"
 3    ReplaceExisting = $true
 4    InstallJobs = $true
 5    CleanupTime = 720
 6    AutoScheduleJobs = "DailyFull", "HourlyLog"
 7    StartTime = "23:15:00"
 8    BackupLocation = "\\sql\backups"
 9}
10
11Install-DbaMaintenanceSolution @params

This will install the solution, create the jobs, and schedule full user backups at 11:15 PM every night. Log backups will be scheduled every hour and all databases will be backed up to \\sql\backups. System databases will be backed up each night at 1:15AM. The other jobs will be scheduled at the times mentioned above.

If you have suggestions for environments that operate within normal business hours and are closed or less busy on weekends, I'm open to changes. Leave a GitHub issue or comment below and we can discuss.