Requirements

• Download the MaintainanceSolution.sql from Ola Hallengren's web site. Open it in SQL Server Management Studio, and execute it on your server.
• Install PowerShell 7 (or higher) on your machine. Maybe this will work with an older version of PowerShell as well, but I did not test it.
• Install the PowerShell SQL Server Module.

Installing the PowerShell SQL Server Module

Installing the SQL Server PowerShell Module is quite straightforward. Open a PowerShell window and enter

Install-Module SQLServer

If you don't have Internet access on your machine, go to another machine that has access and enter

Save-Module SQLServer -Path C:\Temp\.

(Replace the text highlighted in yellow with your own values)

This creates a subfolder in your destination folder called SqlServer. Copy this folder to C:\Program Files\WindowsPowerShell\Modules\ on your server - that's it.

Creating the jobs

I assume that you create these scripts and the Scheduled Task on the same machine where SQL Server is running, otherwise you have to take care about firewall rules, service user, access permissions etc.

First, let's create a folder on the server, e.g. C:\Scripts. I will call this "scripts-folder" in the rest of the article. Also create a folder called "Logs" inside the scrips-folder.

When you installed Ola Hallengren's maintenance solution, you normally get one table (CommandLog) and a couple of stored procedures in the master database:

• CommandExecute
• DatabaseBackup
• DatabaseIntegrityCheck
• IndexOptimize

Database Integrity Check

I am not going into detail about what these procedures are doing - the name of them should explain what they are used for. Let's start with checking the integrity of the databases. Examples and explanations of the parameters can be found here, for regular tasks on a web server we should use this weekly to check the databases.

SQL Script

Open your favourite text editor (e.g. Visual Studio Code, Notepad++, etc.) and enter the following lines:

EXECUTE dbo.DatabaseIntegrityCheck
 @Databases = 'ALL_DATABASES',
 @LogToTable = 'Y'

Save this file as DatabaseIntegrityCheck.sql in your scripts-folder.

PowerShell script

Create a new file with the following content and save it as DatabaseIntegrityCheck.ps1 in your scripts-folder:

$SqlServer = "DNN-SQL\SQLEXPRESS"
$SqlScriptFile = "{0}\DatabaseIntegrityCheck" -f $PSScriptRoot
$LogFile = "{0}\Logs\DatabaseIntegrityCheck-{1}.log" -f $PSScriptRoot, (Get-Date).ToString("yyyy-MM-dd")

Invoke-Sqlcmd -InputFile $SqlScriptFile -ServerInstance $SqlServer -Database master…
 -Verbose 4> $LogFile

$FailurePattern = Select-String -Path $LogFile -Pattern "Outcome: Failed"…
 -AllMatches
if ($FailurePattern.Matches.Count -gt 0) {
 $subject = "{0}: Database Integrity Check failed" -f $SqlServer
 $body = Get-Content -Path $LogFile -Raw
 Send-MailMessage -From "SQL Service User <[email protected]>"…
  -To "SQL Server Admins <[email protected]>"…
  -Subject $subject -SmtpServer "mail.yourdomain.com" -body $body…
  -WarningAction silentlyContinue
 # Quit on error
 exit 1
}

(The … means that the line continues in the next line. Remove it and the line break in your script)

Any questions? ;-)

The first three lines assign some values to variables:

• The name (and instance) of the SQL Server,
• the path to the SQL script created above, and
• the path to the logfile.

$PSScriptRoot is the folder where the PowerShell script is residing, so there is no need to change if both scripts are in the same folder.
The log file will be stored in the Logs directory created above, and has the following naming convention: DatabaseIntegrityCheck-yyyy-MM-dd.log (Year-Month-Day). As the script will not run more often than once a day this should be enough, otherwise add something like "-hh-mm" to the format string at the end of the line.

Invoke-Sqlcmd takes these variables as parameters and uses the master database (where the stored procedures were created). To get the output, the -Verbose switch produces an verbose output - this output has different channels is the one with the decimal number 4 - so the output from this channel is redirected to the log file by 4>.

All scripts from Ola Hallengren produce a line Outcome: Failed if anything goes wrong. The next line checks for that pattern, and if it is found it sends an email to the SQL Server Administrators mailing list (that you hopefully have set up). The mail contains the complete log from that operation.
It finally quits with errorlevel 1 in this case, that shows you 0x1 in the Task Scheduler as Last Run Result instead of 0x0, when the job was finished successfully.

Create a Scheduled Task

Finally, create a Scheduled Task on your Server. You can do that in the Task Scheduler, or use PowerShell (in elevated mode, aka "Run as Administrator") and run the following lines:

$taskName = "Database Integrity Check"
$description = "Checks database integrity of all databases"
$principal = New-ScheduledTaskPrincipal -UserID "NT AUTHORITY\SYSTEM"…
 -LogonType ServiceAccount -RunLevel Highest
$trigger = New-ScheduledTaskTrigger -Daily -At 6:00am
$action = New-ScheduledTaskAction -Execute "C:\Program Files\Powershell\7\pwsh.exe"…
 -Argument ".\DatabaseIntegrityCheck.ps1" -WorkingDirectory "C:\Scripts\"
$task = New-ScheduledTask -Action $action -Description $description…
 -Principal $principal -Trigger $trigger
Register-ScheduledTask $taskName -InputObject $task

For simplicity I used the ALL_DATABASES option in the SQL script. I prefer to run database integrity checks separated for user and system databases, running weekly checks of the system databases and daily checks for the user databases. If you want to do this, double the scripts and task above using SYSTEM_DATABASES and USER_DATABASES instead of ALL_DATABASES in the SQL Scripts and adjust the file names, PowerShell scripts and tasks.

Index Optimization

You should have enough ideas now how it works. For your convenience (and as I already have the scripts), here is the same for index optimizattion. There is normally no need to run this script on the system databases, and it is best practice to run it once a day, e.g. at 5 am.

SQL Script

IndexOptimizeUser.sql

EXECUTE [dbo].[IndexOptimize]
  @Databases = 'USER_DATABASES',
  @LogToTable = 'Y'

PowerShellScript

IndexOptimizeUser.ps1

$SqlServer = "DNN-SQL\SQLEXPRESS"
$SqlScriptFile = "{0}\IndexOptimizeUser.sql" -f $PSScriptRoot
$LogFile = "{0}\Logs\IndexOptimizeUser-{1}.log" -f $PSScriptRoot, (Get-Date).ToString("yyyy-MM-dd")

Invoke-Sqlcmd -InputFile $SqlScriptFile -ServerInstance $SqlServer -Database master…
 -Verbose 4> $LogFile

$FailurePattern = Select-String -Path $LogFile -Pattern "Outcome: Failed"…
 -AllMatches
if ($FailurePattern.Matches.Count -gt 0) {
 $subject = "{0}: Index Optimize - USER Databases failed" -f $SqlServer
 $body = Get-Content -Path $LogFile -Raw
 Send-MailMessage -From "SQL Service User <[email protected]>"…
  -To "SQL Server Admins <[email protected]>"…
  -Subject $subject -SmtpServer "mail.yourdomain.com" -body $body
…   -WarningAction silentlyContinue
 # Quit on error
 exit 1
}

A description of all parameters for the stored procedure can be found here.

Happy DNNing!

PS. Feel free to download the slides from my session at DNN Connect 2022 and additional material from here: DNN Connect Association DNN Platform Events > Events > 2022 > Sessions (dnn-connect.org)