I took this idea from one of my previous posts Simple WSUS Maintenance/Cleanup for your SCCM environment where a PowerShell script can be loaded into an SCCM Configuration Item and run automagically across all the SCCM servers using a dynamic collection. Hands off and completely automated.

This time round it’s the renowned Ola Hallengren SQL maintenance scripts. Ola’s scripts have always been recommended by ConfigMgr/SCCM MVPs and partners etc. The re-indexing provides performance gains in the CM database. SQL databases need maintenance as over time, the data/tables/indexes can become fragmented and this leads to poor performance.

You can check for SQL fragmentation using this T-SQL:

sp_helpdb

This will give you a list of the databases and mostly importantly, the dbid.  The ConfigMgr database in this example has ID = 7

sp_helper

select * from sys.dm_db_index_physical_stats (7,DEFAULT,DEFAULT,DEFAULT,DEFAULT)
where page_count > 1000
order by avg_fragmentation_in_percent desc

This will give you the avg_fragmentation_in_percent column. You can use this to compare before/after running the Ola Hallengren maintenance scripts.

In an SCCM hierarchy, each CAS and Primary Site will need its own database. These are generally run on a single SQL server per SCCM site. Software Update Points also use SQL databases and can sometimes share the SQL Server with the SCCM Primary Site Server. SUPs (WSUS) can also share databases.

I have developed a simple PowerShell script that uses an SCCM Configuration Item (CI) /Baseline to deploy the Ola Hallengren SQL maintenance onto the SCCM/SUP SQL Servers and maintain the SQL Agent scheduling for the various maintenance tasks. It is effectively a DSC/Compliance for the Agent Jobs/Schedules.

The CI is targetted at a dynamic collection containing all SCCM SQL servers and SUPs.

Assumptions:

  • The SYSTEM account on each of the SQL / WSUS servers has sysadmin access to the SQL server
  • SQL is installed locally on the WSUS/SUP server (if this is not the case, just change the collection query appropriately)

Script Overview

The script does the following:

  • Retrieves the Ola Hallengren MaintenanceSolution.sql from the disk or network
  • Loads the SQL Server PoSh module from the disk or network
  • Writes to a local CMTrace formatted log file
  • Installs MaintenanceSolution.sql into the Master database
  • Creates SQL Agents Jobs
  • Changes the SQL Agent service startup to automatic
  • Sets SQL Agent Job schedules
  • Upgrades the MaintenanceSolution.sql scripts if it detects a new version available on the disk or network
  • Supports adding custom TSQL to the job steps
  • Tested with SCCM 1802+ and SQL 2012+

Installation

Pre-reqs

  • Disable all existing SQL maintenance jobs but not backup jobs.
    Disable Jobs that are related to: DBCC/Integrity Checks/Indexing
  • Download a copy of my SCCM SQL Ola Hallengren Maintenance Scripts.ps1.
  • Download the latest copy of MaintenanceSolution.sql
  • Copy the MaintenanceSolution.sql file to a network share that the computer accounts of the SCCM SQL Server and SUP Servers can access - this is the recommended approach so that all SQL servers can access the same version.
  • Grab a copy of the SQLServer module from the PowerShell gallery and copy to a network share. Same permissions as mentioned above.

Script Preparation

In the downloaded copy of SCCM SQL Ola Hallengren Maintenance Scripts.ps1, amend line 32 variable:

$OlaHallengrenScriptLocation =

so that it reflects
the UNC path of the MaintenanceSolution.sql
file.

Also confirm that line 37 variable:

$SQLServerPoShModule =

points to a location that contains the SQLServer module file SqlServer.psm1 and that the location is also accessible by the computer accounts of the SCCM SQL Server and SUP Servers.

Adjust the schedules to your liking:

$SQLJobsConfig

This variable contains all the SQL Agent jobs you want to configure. In my script they are mostly the Ola Hallengren ones. You can add your own custom jobs if they need configuring.

Note:  The script does assume that the job has already been created by another means - in this case, by the MaintenanceSolution.sql script, so you’re wanting to configure your own SQL Agent jobs using this script, make sure they already exist.

Script Deployment

Add the contents of SCCM SQL Ola Hallengren Maintenance Scripts.ps1 to the Discovery script on a new Configuration Item in SCCM:

Add a dummy compliance rule:

Add the CI to a new Baseline and deploy the baseline to your SCCM SQL/WSUS servers collection. I used the below two queries for my collection:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemRoles = "SMS SQL Server"

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemRoles = "SMS Software Update Point"

Results

Not much to look at, but from the SSMS we can see the agent jobs and the schedules have been configured in accordance with the settings in our CI script:

Your SQL databases both SYSTEM and USER databases will now be optimised using Ola’s maintenance scripts.

Monitor the jobs in your test environment prior to production deployment to evaluate performance impact and competition times. The IndexOptimise job will take significantly longer during the first run if no maintenance has been performed on the databases before.