Managing maintenance plans on hundreds of servers

• Bryan Welch-438459

  Old Hand

  Points: 372

  More actions

  September 9, 2011 at 7:16 am

  #247773

  How does one manage the maintenance plans on several hundred SQL Server systems? Are there any commercial or free products for doing this?

  I'm adding and standardizing maintenance plans. Most use standard maintenance plans configured with the gui, though I'm using a custom script to handle index reorg/rebuild work. Doing this manually on many, many servers gets tedious. Changing all of them in even a minor way is more tedious. How do you do it?

• Yuri55

  SSCrazy Eights

  Points: 8449

  More actions

  September 9, 2011 at 10:10 am

  #1379383

  Check BOL: "Administering Multiple Servers Using Central Management Servers"

  Hope this can help

• SQLBuddy123

  One Orange Chip

  Points: 28294

  More actions

  September 11, 2011 at 4:10 pm

  #1379747

  Usually we use MPs for the basic tasks like Index Reorg\Rebuilds, Update stats and DBCC checkDB...

  And all of theses can be replaced by using custom scripts. Use the excellent scripts from http://ola.hallengren.com/

  And then use Central Management server or Multi Server queries using Registered servers -- Local server Groups.

  Thank You,

  Best Regards,

  SQLBuddy

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 12, 2011 at 6:05 am

  #1379910

  Check out SQL Sentry's Event Manager.

  And for the actual maintenance itself you absolutely should use ola.hallengren.com's scripts.

  Tell them both that TheSQLGuru sent you! 🙂

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service