February 21, 2025 at 12:00 am
Comments posted to this topic are about the item Configuring Maintenance Plans in SQL Server
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
July 16, 2025 at 3:19 pm
Just wanted to add my 2 cents to this. I know this isn't a "new" topic (created back in Feb), but I wanted to play devil's advocate a little bit.
My preference - don't like maintenance plans. Sure they make things nice and easy with a pretty GUI to configure things like backup and maintenance, but it is not scalable. You spin up a new instance, you have to re-create the MP from the GUI. My preferred approach is scripts in an "admin" database. Each instance gets it's own, small admin database. Doesn't need to be big as it is just holding admin related objects and limited access to DBA's. Then if you put all of the SQL instances into registered servers, you can deploy your backup scripts (and any other useful scripts) against ALL servers quickly and easily as registered servers allows you to run a script against multiple instances. If you are concerned that the scripts may be out of date (bug fixes in some that may not have been deployed across the company) You can also store the backup scripts in source control if you script it out. I am not aware of any way to put a Maintenance plan into source control. Plus your admin DB can contain other useful scripts that you find (monitoring metrics that you want to capture, restore scripts, useful 3rd party scripts you find like sp_WhoIsActive, etc.).
Pros for maintenance plans are that non-DBA's or people very new to SQL Server can create and maintain their databases from there pretty easily. Cons are that it is hard to mass deploy changes. For example, if you have 100 SQL Instances and your backup location changes, you have 100+ (plus because you may have a full, differential, and log backup maintenance plan) maintenance plans are not easy to mass deploy. Another con is that you can't have it in source control.
Pros for scripting it out are that it is easy to deploy, can store the code in source control, easier to scale, and easier to modify across all instances if needed. On top of that, if your scripts are designed carefully, you can handle special cases (such as log backups skipping databases in simple recovery without needing anything special in your setup) inside the script while still having it generic. With Maintenance Plans, log backups need to have named databases or you run into problems with different recovery models. Same thing with index maintenance - scripts can handle cases where you may not be able to reorg an index (such as if row and page lock is disabled on the index) while maintenance plans are more dumb and will try and fail. Cons of scripts are that you need to either trust a script you found online or have a good DBA who knows your systems well to build the scripts to minimize failures. I personally like to build my own scripts as I can tailor it to my environment, but I hear that Ola's scripts are good too. I personally don't use Ola's scripts, but it is mostly because it has a lot in it that I don't need. For example, I don't need all of the different backup tools to be in my scripts - I just want the backup tool my company uses. Plus, I like to follow the "KISS" principle - keep it simple silly. I like to have my backup and maintenance scripts simple. I end up with a LOT more stored procedures for backup and maintenance (one for full backups, one for differential backups, one for log backups, one for index maintenance, one for statistics maintenance, and one "master" script that calls all of those), but when a bug is found, I can re-run just the step that failed (re-run index maintenance on database ABC for example) or fix a short, easy to understand stored procedure rather than trying to figure out where it failed in a 1000+ line script (not sure if Ola's are really that long). Downside to how I did mine is that they end up having a lot of stuff hard-coded in (such as backup locations), but that works fine for my specific use case. That way if the backup location changes, I update the script in source control and push it out to all my systems and I'm good to go. Now that being said, Maintenance Plans are what my company has used in the past and still uses for some systems that I haven't migrated to scripts yet, but it's on my to do list (similar to getting all my databases into source control... I have a few in there, but not all).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply