ConfigMgr Database Maintenance – New 2020 edition | Quisitive
ConfigMgr Database Maintenance – New 2020 edition
March 23, 2020
We provide the best solution for ConfigMgr Database Maintenance

Steve Thompson, ConfigMgr MVP and SQL MVP, has a nice write up on using Ola’s Maintenance Solution with ConfigMgr.  It’s good stuff.  Matthew Teegarden has a follow-up article with more info and some alterations about database maintenance.

There’s only one problem… both are seemingly violating the licensing of the Usage Rights for SQL technology included with ConfigMgr.  Let’ me explain.

Microsoft Docs lists the approved use rights which basically just includes the site database, database replicas for Management Point, WSUS, and SQL Reporting.  An issue was logged to GitHub requesting clarification on usage rights for other databases / apps / solutions.  In summary, Aaron Czechowski clarified…

  • Ola Hallengren’s Maintenance Solution with a CommandLog database (i.e. CMMonitor), ConfigMgr Client Health DB by Anders Rodland, the MDT database, MBAM, and PowerBI all fall into the category of “additional Microsoft or third-party product” and must have a separate license
  • Adding custom stored procedures, tables, views, database schemas, etc. to a licensed database on the SQL instance is not a licensing question, but a support question.

Thus using Ola Hallengren’s Maintenance Solution without the CommandLog database (by disabling table logging or using the master, CM_xyz, SUSDB, or ReportServer databases) is within the licensing terms.

While Matthew Teegarden states that using the master database isn’t a great idea, it is the best option given the licensing limitations.

So, What To Do?

Option 1: Follow Steve Thompson’s post, but replace [CMMonitor] with [master]

Option 2: Follow Matthew Teegarden’s post, but replace [SQLMaint] with [master]

Option 3: In Ola Hallengren’s MaintenanceSolution.sql, use it as-is and the objects will be stored in the [master] database.  Optionally, change ‘Y’ to ‘N’ on line 30 to disable logging to a table.

Option 4: Create a standard SQL Maintenance Plan.  The biggest issue I have with this is that is isn’t as easily automated, imported, or exported as Ola’s solution because SSIS is required.  However, it really only has to be done one time per ConfigMgr SQL instance, so the impact is pretty small… probably 1/2 an hour.

The only issue I have with Matthew’s solution is that it mixes Ola’s and standard Maintenance Plans.  That makes it difficult to support, especially for ConfigMgr admins who are not DBAs.

If you would like to see Microsoft expand SQL usage rights vote it up at UserVoice and create/reply to a GitHub issue on the topic.

In the next post, I’ll detail doing it all in a standard Maintenance Plan. 

It’s like long hand division, but it’s cleaner and easier to support.