As a follow on to a frequently referenced, and still accurate blog post: Optimizing SCCM Databases Revisited – Let’s automate the creation of this optimization solution using PowerShell and DBATools. Let’s get building!
If you have not used dbatools, read Dave’s article: Cool SQL Tricks with DbaTools and MEM ConfigMgr start there, then come back. You’ll need to install the dbatools PowerShell module as he has described, additionally can be found in the references section.
This script will perform the following:
- Create a new database named ‘DBA’
- Install the latest version of Ola Hallengren database maintenance solution into ‘DBA”
- Install a custom SQL Agent job and task specifically with optimum MEMCM database tuning settings
- Schedule the Agent Job to run weekly on Sunday @ 1:00AM
Note: Couple of updates to this script. Change DB recovery model to simple, change owner to SA. Change variables as appropriate.
# Create new database for Database Maintenance plan, install Ola's solution, create and schedule IndexOptimize task # 8/27/2021 # Author: Steve Thompson # Change variables as appropriate $SQLInstance = "localhost" $DBName = "DBA" # Create a new database on the localhost named DBA New-DbaDatabase -SqlInstance $SQLInstance -Name $DBName -Owner sa -RecoveryModel Simple # Install Ola Hallengrens Database Maintenance solution using the DBA database Install-DbaMaintenanceSolution -SqlInstance $SQLInstance -Database $DBName -ReplaceExisting -InstallJobs # Create a new SQL Server Agent Job to schedule the custom Agent Task New-DbaAgentJob -SqlInstance $SQLInstance -Job OptimizeIndexes -Owner sa -Description 'Ola Hallengren Optimize Indexes' # Create a new SQL Agent Task step with the optimal parameters for MEMCM New-DbaAgentJobStep -SqlInstance $SQLInstance -Job OptimizeIndexes -StepName Step1 -Database $DBName -Command "EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 10, @FragmentationLevel2 = 40, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y'" # Optionally, create a schedule to run the SQL Agent Tast once a week on Sunday @ 1:00AM New-DbaAgentSchedule -SqlInstance $SQLInstance -Job OptimizeIndexes -Schedule RunWeekly -FrequencyType Weekly -FrequencyInterval Sunday -StartTime 010000 -Force
Closing notes: I’ve not added error handling as I’m not that smart. Consider this a starting point to modify and customize as you wish. All of the command syntax detail used can be found in the dbatools link.
Ola Hallengren SQL Server Maintenance Solution: https://ola.hallengren.com/