August 18, 2011 by Mike Hillwig
I have a confession to make. I’m not a fan of maintenance plans on SQL Server. Sure, they’re easy to set up and they work well in small environments. But what happens when you migrate from one server to the next? What happens when your company is rapidly growing and you find yourself adding new database servers every week? Perhaps you’re a nut for consistency like me and want to change transaction log backup retention across all your servers without having to go change it on all of your servers.
Here is the dirty little secret: Maintenance plans don’t scale very well.
I’m currently working on a presentation that I’m hoping to give at SQL Saturday 92 in Oregon. My belief is that anything you can do with a maintenance plan, I can do with T-SQL. By leveraging a MSX-TSX relationship with SQL Server Agents, this can scale far and fast.
This is going to be fun to put together. Stay tuned.