If you are a DBA, you understand the importance of having a robust Database Maintenance Solution.
Ola Hallengren has developed an excellent maintenance implementation using T-SQL scripts. It’s a free solution replacing the traditional GUI maintenance plan (for a variety of databases, applications, and sizes).
The SQL Server community recommends this solution for running backups, integrity checks, and index and statistics maintenance plan.
Installing Ola Hallengren’s scripts
The installation of Ola Hallengren’s (OLA) should not be a problem, as the solution supports all Windows versions of SQL Server, starting from SQL Server 2008 (on all editions). It is also supported on Linux 2017, Azure SQL Database, and Azure SQL Database Managed Instance.
The installation has been described in Ola’s FAQ, but I will highlight the steps:
- Download the sql script, and open it in SSMS.
- In the script (around line 32), find this line “SET @BackupDirectory = NULL” and replace NULL with the path to your backup directory. For example: “SET @BackupDirectory = N’D:\Backup‘”. If you use a network share/UNC path, both SQL Server and SQL Server Agent services need to be run under a domain account with full control of the directory.
- Set the integer value for the CleanupTime. In the script, find this line:
SET @CleanupTime = NULL. This value specifies how long (in hours) will each backup file be kept on the drive. The backup files will never be deleted automatically If the default NULL value is left in the script.
- Before executing the MaintenanceSolution.sql script, we recommend changing the installation database from the master database to a “Tools” database. For this, you just need to change the line “USE [master]” to point to the database in which the objects will be created. The jobs will automatically be configured to run against this database. It also creates a few SQL Server Agent jobs that will be discussed separately.
- Go into [SQL Server Agent] / [Jobs] and start the jobs that have been created. Verify that these jobs are completing successfully. Also, verify that the backup files are being made. Check the output files in the error log directory.
- Schedule the SQL Agent jobs.
The maintenance components
SQL Server Backup
The following four database backup tasks use Ola’s stored procedure DatabaseBackup:
- DatabaseBackup – SYSTEM_DATABASES – FULL: Performs the full backup of the three system databases: master, model and msdb.
- DatabaseBackup – USER_DATABASES – DIFF: Make the differential backup of all user databases.
- DatabaseBackup – USER_DATABASES – FULL: It performs the full backup of all user databases.
- DatabaseBackup – USER_DATABASES – LOG: Make all user databases’ transactional backup using the Full recovery model.
How to backup a SQL database to an Azure storage account?
- Create a storage account with block blob support (general-purpose v2 accounts in most cases).
- Generate a SAS token that allows full read/write access to the container and copy the URL. Should look like this:
- Store the SAS credentials in SQL Server. To do this, in SSMS, run the following TSQL script (Replacing the YOUSTORAGEACCOUNTNAME string):
IF NOT EXISTS (SELECT * FROM sys.credentials
WHERE name = 'https://YOUSTORAGEACCOUNTNAME.blob.core.windows.net/')
CREATE CREDENTIAL [https://YOUSTORAGEACCOUNTNAME.blob.core.windows.net/]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '';
- Alter the @Url parameter in the SQL Agent backup Jobs. The key to this working is that the Url must match the SQL Server credential name created above. The agent job command job should look like:
@Databases = 'USER_DATABASES',
@BackupType = 'FULL',
SQL Server Integrity Check
The following two database integrity jobs use the stored procedure DatabaseIntegrityCheck:
- DatabaseIntegrityCheck – SYSTEM_DATABASES: This task performs an integrity check of all user databases.
- DatabaseIntegrityCheck – USER_DATABASES: It performs an integrity check of all user databases.
SQL Server Index and Statistics Maintenance
The agent job below, created by the installation script, uses the IndexOptimize stored procedure. Many parameters can be used to configure the behavior of the process. To get full details on the procedure parameters, see this documentation page.
- IndexOptimize – USER_DATABASES This task performs index optimization of all user databases.
There are four cleanup tasks.
- CommandLog Cleanup: By default, the task deletes records older than 30 days logged into the table dbo.CommandLog.
- Output File Cleanup: It deletes files named *_*_*_*.txt in the SQL Server log directory older than 30 days. This task uses the tokens ESCAPE_SQUOTE and SQLLOGDIR, which are only available within SQL Server agent job steps.
- sp_delete_backuphistory: This task deletes backup history records in the msdb database for older than 30 days. An overloaded MSDB Backup History backup can severely affect the backup performance in older versions of SQL Server.
- sp_purge_jobhistory: Deletes SQL Server agent job history records in the msdb database for older than 30 days.
How should I schedule jobs?
It depends on many factors like your maintenance window, your databases’ size, on predefined RPO (Recovery Point Objective) and RTO (Recovery Time Objective).
Here are some guidelines that you can start with:
- Full backup one day per week
- Differential backup all other days of the week
- Transaction log backup every hour
- Integrity check one day per week
- Index maintenance one day per week
- Full backup every day
- Integrity check one day per week
I recommend that you run a full backup after the index maintenance. The subsequent differential backups will then be small. I also recommend that you perform the full backup after the integrity check. Then you know that the integrity of the backup is OK.
- sp_delete_backuphistory one day per week
- sp_purge_jobhistory one day per week
- CommandLog cleanup one day per week
- Output file cleanup one day per week
To define a schedule for any of the jobs, perform the following steps:
- In Object Explorer, expand SQL Server Agent and Jobs nodes.
- Right-click on the backup job to open the context menu, and click on Properties.
- In the Job Properties window, select the Schedules tab, and click on the New button to create a new schedule.
- Provide the schedule name, and set the schedule’s type and frequency. Optionally, select the start and end dates for the plan. Click OK to save changes.
- Click the OK button to create the schedule. The job will run automatically on the specified schedule from now on.
Automation is excellent but must always be monitored.
Using the parameter @LogToTable = ‘Y’ when running the OLA commands, it is easy to see if any errors occurred by inspecting the CommandLog table. You can use the script below to check:
SELECT ID, DatabaseName, CommandType, StartTime, EndTime, ErrorNumber
WHERE ErrorNumber > 0
Also, remember to Keep your jobs safe using SQL Agent notifications.