Ola Hallengren Maintenance Solution (OHMS) is with good reason a well-known and widely used solution for SQL Server database backups and maintenance of indexes and statistics together with database integrity check. The solution is recommended by many great DBAs and SQL Server MVPs. Personally I have used the solution with great success in several financial organisations.
Installing OHMS is quite simple. In this text I will look into some details when installing OHMS in a scalable SQL Server infrastructure. The installation is here done i five steps:
- Create DBA database
- Configure OHMS installation
- Run OHMS installation
- Configure OHMS
- Test OHMS
With dbatools (
dbatools.io) there is the PowerShell CmdLet
Install-DbaMaintenanceSolution, that install or update OHMS in a given database. Check the details on the code in GitHub. I have not personally used dbatools, but they look fine and I might take a deeper look later.
Create DBA Database
Create seperate userdatabase on each instance for SQL Server administration, e.g.
[sqladmin] with a prefix of the organisation. User underscore for space in the name like
[nielsgrove_sqladmin].
This is a general solution that can be used for other database administration solutions.
This database does not require OHMS, but you should still do backups and maintain indexes.
Configure installation
Put OHMS in seperate filegroup to use database for other purposes now and in the future.
ALTER DATABASE [sqladmin] ADD FILEGROUP [OHMS];
GO
ALTER DATABASE [sqladmin] ADD FILE ( NAME = N'ohms_data0', FILENAME = N'C:\Data\sqladmin_ohms_data0.ndf' , SIZE = 8MB , FILEGROWTH = 64MB ) TO FILEGROUP [OHMS];
GO
ALTER DATABASE [sqladmin] MODIFY FILEGROUP [OHMS] DEFAULT;
GO
The filegroup
[OHMS] is marked as default during the OHMS installation.
OHMS is using the schema
[dbo] by default. You could change this, but this would require a rather detailed rewrite of the code. As the code in the scriptfile
ManitenanceSolution.sql is more than 8000 lines I would not recomment changing the schema.
Download script from
ola.hallengren.com. Define database name from above in the USE-statement around line 22.
Create cmd- or PowerShell-file to implement configuration. The simple solution used here is a one-line cmd-script as in the next section. You should always script even simple things, as you then are prepared to scale out with the same quality.
If you are building a automatic installation and upgrade you should get the precise URLs from the general .com-URL. That might require a more complex configuration.
If you want a longer or shorter history on backuphistory, jobhistory or CommandLog from the default 30 days you can edit the file MaintenanceSolution.sql by searching for DATEADD(dd,-30,GETDATE()) and change the value. You should change the history length before running the OHMS installation as it is more simple than alter the jobs afterwards. I like to have 42 days of history...
Run OHMS installation
sqlcmd command-line using configuration above. If you create a more complex configuration, the execution itself will have to be considered in detail.
C:\>sqlcmd.exe -S "(local)\SQLSERVER" -E -d "sqladmin" -i "C:\SysAdmin\MaintenanceSolution.sql"
Changed database context to 'sqladmin'.
And change the default filegroup back to [PRIMARY]
ALTER DATABASE [sqladmin] MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO
Configure OHMS
The sql-script dowes create SQL Agent jobs, but the jobs has no schedule. Add a schedule to each job by the stored procedure
[msdb].[dbo].[sp_add_jobschedule]. This is a quick example:
DECLARE @schedule_id int;
EXECUTE [msdb].[dbo].[sp_add_jobschedule]
@job_name=N'CommandLog Cleanup',
@name=N'OHMS CommandLog Cleanup',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20190203,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT;
SELECT @schedule_id;
GO
OHMS create these 11 jobs in SQL Agent:
- 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
You can get a list of the jobs from the table
[msdb].[dbo].[sysjobs]:
SELECT * FROM msdb.dbo.sysjobs
WHERE [description]=N'Source: https://ola.hallengren.com'
ORDER BY [name];
Glenn Berry has written a script that creates the jobs with a good reference schedule. The script is introduced in the blog post "
Creating SQL Server Agent Job Schedules for Ola Hallengren’s Maintenance Solution".
All OHMS jobs are placed in the SQL Agent job category
Database Maintenance. You could change the category with the stored procedure
[msdb].[dbo].[sp_update_job], but I think that the benefits will be rather limited.
Log files are placed in SQL Server ErrorLogPath where the SQL Server ErrorLog and default traces are placed by default. If you want the OHMS log files another place, then change the job steps with the stored procedure
[msdb].[dbo].[sp_update_jobstep]. You should only do this if you are forced to by stupid policies.
Put configuration in script file(-s) and make it idempotent. Also put everything in version control, with the documentation.
If you want to remove the OHMS jobs then this query on the table [msdb].[dbo].[
sysjobs] can generate the statements:
SELECT N'EXECUTE [msdb].[dbo].[sp_delete_job] @job_name=N''' + [name] + N''', @delete_unused_schedule=1;'
FROM [msdb].[dbo].[sysjobs]
WHERE [description]=N'Source: https://ola.hallengren.com';
Test OHMS
Run each job starting with database integrity checks, then full data backup moving to differential backup and closing with transaction log backup spiced up with index and statistics maintenance jobs.
Check output of each job and check SQL Server errorlog after each SQL Server Agent job execution.
You can generate the statements th start the OHMS jobs refering the stored procedure
[msdb].[dbo].[sp_start_job] in a query on the table [msdb].[dbo].[sysjobs] like above:
SELECT N'EXECUTE [msdb].[dbo].[sp_start_job] @job_name=N''' + [name] + N''';'
FROM [msdb].[dbo].[sysjobs]
WHERE [description]=N'Source: https://ola.hallengren.com';
Improvements
- The installation script handles both installation and update. Some identification on which version is installed and the version about to be deployed.
- Integrate installation with your Definitive Software Library (DSL). Put the complete installations set with Ola's and you own scripts in a combined installation set.
- More robust errorhandling. Especially in the tests.
History
2019-02-03 : First text.
2021-06-15 : Section on changed history added.
Posts
