Create the Optimize Database Solution using PowerShell and dbatools

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:

  1. Create a new database named ‘DBA’
  2. Install the latest version of Ola Hallengren database maintenance solution into ‘DBA”
  3. Install a custom SQL Agent job and task specifically with optimum MEMCM database tuning settings
  4. 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.

References:

dbatools: https://docs.dbatools.io/

Ola Hallengren SQL Server Maintenance Solution:  https://ola.hallengren.com/

This entry was posted in DBA, PowerShell, SQL Server. Bookmark the permalink.

6 Responses to Create the Optimize Database Solution using PowerShell and dbatools

  1. Ken Kinkopf says:

    Fantastic. Thank you for sharing. I just shared your old article the day before yesterday with a colleague.

  2. Pingback: Optimizing SCCM Databases Revisited | Steve Thompson [MVP]

  3. I’ve run this in my lab several times for clients and it’s been working great! Several clients have since rolled this into labs and even production as well. Thank you!

  4. Pingback: SQL Performance Tips for MEMCM – Part 2 | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.