Configuration Manager Database Maintenance

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, and SQL Server 2019. The solution is based on stored procedures. The solution has been designed for the most mission-critical environments, and it is used in many organizations around the world. The SQL Server Maintenance Solution has been voted as Best Free Tool in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.

Information from ola.hallengren.com here

Download MaintenanceSolution.sql from GitHub here

You can check for SQL fragmentation using this T-SQL: sp_helpdb - to get database id (dbid)

  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.

Using Configuration Baseline deployment

Preparation

Create dynamic collection for SQL Server in the environment by using this WQL query

  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"

Disable all existing SQL maintenance jobs (if exist) but not backup up (if exist)

Ensure SQL Server agent service is set to start automatically and running

Prepare scripts

Allocate/create network share to host SQL maintenance script and PowerShell script

Copy MaintenanceSolution.sql and Deploy-SQLMaintScript.ps1 to the network share

Copy PowerShell SQL module from PowerShell Gallery to the same network share

Modify Deploy-SQLMaintScript with the following change

  Line 32 - $OlaHallengrenScriptLocation = <UNC path to MaintenanceSolution.sql>

  Line 37 - $SQLServerPoshModule = <UNC path to SqlServer Module .psm1>

  Line 45 - $SQLJobsConfig array = Modify per your environment

Deployment

Create new Configuration Item with Deploy-SQLMaintSript.ps1 as a discovery script

  PowerShell_script::SQLMaintScript_Compliance

  Type: Script, Data type: String

Add dummy compliance rule

  Rule type: Existential, The specified script return at least one value

Create new configuration baseline consisting of above configuration item

Deploy the baseline to SQL collection

Thing to note

  PowerShell script setting in Client setting need to be set to Bypass because our script is not signed. If not set to Bypass the Configuration baseline will fail with Error: 0x87d00327 - Setting Discovery Error - Script is not signed

  Configuration baseline compliance evaluation setting is in the client settings --> Compliance Settings :: Default is every 3 days

  CIAgent.log in %SystemRoot%\CCM\Logs folder in client machine can be used to monitor the CI/CB deployment

Manually run the script

Open SQL Management Studio

Open MaintenanceSolution.sql

Modify @BackupDirectory to proper backup location

Modify @OutputFileDirectory to proper location

Modify @CleanupTime to the number of hours which the backup files are to be deleted

Execute MaintenanceSolution.sql

Verification

Open SQL Management Studio

Navigate to SQL Server Agent in Object Explorer

Verify existence of the following jobs

  CommandLog Cleanup

  DatabaseBackup - SYSTEM_DATABASES - FULL

  DatabaseBackup - USER_DATABASES - DIFF

  DatabaseBackup - USER_DATABASES - FULL

  DatabaseBackup - USER_DATABASES - LOG

  DatabaseIntegrityCheck - SYSTEM_DATABASES

  DatabaseIntegrityCheck - USER_DATABASES

  IndexOptimize - USER_DATABASES

  Output File Cleanup

  sp_delete_backuphistory

  sp_purge_jobhistory

  syspolicy_purge_history

Perform first backup and schedule maintenance

Start job "DatabaseBackup - USER_DATABASES - FULL"

Verify completion of Database backup

Start job "DatabaseBackup - USER_DATABASES - LOG"

Verify completion of Database backup

Start job "IndexOptimize - USER_DATABASES"

Schedule "DatabaseBackup - USER_DATABASES - FULL" for the interval align with disaster recovery plan

Schedule "DatabaseBackup - USER_DATABASES - LOG" for the interval align with disaster recovery plan (should be the same interval as above DIFF backup

Schedule "Output File Cleanup" for a monthly interval to clean up output log files