Maintenance Plans vs. Scripts
-
I'm a big fan of automating DBA tasks and enjoy reading how others automate their daily tasks. I find that many DBAs have created scripts and automation for their backups. I, on the other hand, have always relied on Maintenance Plans and SQL Server Agent jobs to run my backups. So, I am interested to hear from those that prefer their own home grown process to the built-in Maintenance Plans for managing backups. Is there a compelling reason for me to change, or is it merely an issue of personal preference?
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
If the maintenance plans meet your needs, use them. They work just fine and save you a great deal of time, but there are a few limitations. Until you run into those limitations, there's no real reason to script out your database maintenance tasks.
Personally, I use the maintenance plans as well. They work great.
--J
-
I use the same rule as defaults. Use them until you have a reason to use something else. If maintenance plans work, use them. If you need to go beyond them, like with selective rebuilding, then use a script.
-
In general SQL Server Maintenance Plan covers what is basic to maintain databases.
When the requirement goes beyond the basic, automating using scripts may help.
Also when there are more SQL Server instances to manage, it is usual across the organization to have their own custom scripts / tool to manage & monitor the SQL Server instances.
-
I use maintenance plans myself, and unlike others - have found that I can do everything needed without having to create additional agent jobs to call specific scripts.
When I have run into issues where I need to perform something that is not handled by the built-in tasks, I will use an Execute SQL Task to call a custom script or code.
For example, when I need to perform an integrity check on a particular database using the physical only option - I will use the Execute SQL Task and run DBCC CHECKDB('Database') WITH physical_only.
So, I get the best of both worlds - the ease of using maintenance plans and the flexibility of using scripts where needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
So far, the consensus is just what I thought. While there may be some advantages to executing scripts for managing backups, without a specific need not addressed by Maintenance Plans there is no compelling reason to use one over the other.
Thanks for your thoughts.
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
I wouldn't consider local maintenance plans to be a scalable solution.
As your environment grows you may see a need for a centralized management solution.
Dan
-
We tend to use scripts where I work as you can micro manage things easier. For example with the index recbuilds in maintenance plans it's generally an all or nothing kind of deal. You can't just rebuild the ones that actually need to be rebuilt. This causes excess work on your server.
Another advantage of not using maintenance plans is that when you have instances without the SQL Agent you can use the same scripts on them using Windows Task Scheduler so you could have a generic solution across your entire estate.
Rich
Hope this helps,
Rich[p]
[/p] -
We use a SSIS package to poll servers and pull in information about those servers. We also pull a list of databases and maintain a list which controls backups. It also had info about failed jobs, modified jobs, audit info, report runs, free space and more.
We use this http://ola.hallengren.com/%5B/url%5D for index maintenance since the maintenance plans fail in different ways on clustered named instances and some other conditions.
It's a bit of a hodgepodge of scripts, but it's been working well.
-
I have a set of scripts I use as a base and modify as needed - the only real compelling reason is so I can modify them where necessary. But I do use them even when I don't have to modify them just because they're already done, I know they work, and its scalable
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply