I have been playing around with Ola Hallengren’s Maintenance Solution for some time on our Test and Dev SQL Servers. This week I began deploying it to production. Our previous maintenance solution was a cobbled patchwork of home grown scripts, and some stuff from the Microsoft AIT Automation scripts that badly needed an update.
One thing that is very important to me in terms of a maintenance solution is that it needs to be portable – you can run the code on a SQL Server and it installs with a minimum of configuration. No GUI allowed. I like Ola’s solution in that you only need to make 1 config change to install it: Change the drive letter and folder for the Backup root.
The other thing I like is that you can tweak each server separately by adding or removing parameters to the stored procedures. In an ideal world every server would be the same in terms of drive layout, config etc. I strive for this but in reality this is not achievable. Compromises must be made and this solution allows you to tweak where needed.
I was very happy to see support for 3rd party backup tools. We use Red Gate’s Hyperbac on about 1/3 of our servers so I added a BackupSoftware= ‘Hyperbac’ parameter and it just worked – compressing the files with *.HBC file extension. Big Win, Ola!
Other customizations we turned on:
Also, typically with databases over 50GB, I prefer to turn on
It just makes things easier to manage.
A couple of minor points that I want to make. You will need to assign job schedules to the maintenance tasks, and any overrides to the default configuration (Example, 48 hour retention instead of 24) need to be put into a custom script that you run after you do the install. Finally, one minor complaint with the solution is that there is no naming consistency for the job names so that they all appear in the same area in the Agent job scheduler. Yes, I know the Job category is “Database Maintenance” , but I suspect that few people use or look for this functionality. One other gripe is that it doesn’t generate Auto-Restore scripts, but after reading the FAQ section that feature is planned for a future release.
In summary, this is a great solution that has a lot of functionality and flexibility for your maintenance plans. Best of all, it’s free. I highly recommend it.