PowerShell script to Deploy Ola Hallengren for Multiple servers

Using PowerShell, deploy the ola hallegren Maintenance Solution Script. When we run the script below, we receive the following results.

-> Jobs in SQL will be created.

-> SQL Job Schedules will be created as well.

-> Some stored procedures will also get created ( SP’s are from Brentozar site )

Above all, we must do some manual work in order to be produced.

Requirements: We’ll need Sysadmin access to all of the machines where we’ll be running our PS script.

Step 1     : Ready a script to create DBA database for storing helpful stored procedures    
             and maintenance logging
Step 2     : Download and save the latest scripts to Create the below stored procedures in         
             the DBA database which are very useful
                - Sp_whoisactive: https://github.com/amachanic/sp_whoisactive/
                - Sp_Blitz: http://www.brentozar.com/blitz/
                - Sp_BlitzIndex: http://www.brentozar.com/blitzindex/
                - Sp_BlitzCache: http://www.brentozar.com/blitzcache/
                - Sp_AskBrent: http://www.brentozar.com/askbrent/
Step 3     : Download Latest script to Create Ola Hallengren Maintenance procedures: 
             https://ola.hallengren.com/
Step 4     : Get ready with the scripts to Create the maintenance jobs for backups, DBCC 
             CheckDB, Index  
             Maintenance and maintenance cleanup 
Step 5     : Schedule the maintenance jobs created in step 2
Step 6     : Create CSV file and enter your Instance Names on each line, save the file and 
             close.

Downloaded all the latest scripts and kept in my local c:\temp\ola folder

Please notice the snippet below, which shows how all the scripts are organized into folders:

If the Instance Names are entered into the CSV file as described in Step 6, we can begin deploying the ola hallegren Maintenance Solution script by running the PowerShell script ‘Deploy-SQLMaintenance.ps1’.

I’ve deployed this to a single instance in my system, and the result is as follows:

All stored procedures are deployed to the MASTER Database in the instance (you can alter the database name to deploy the SP’s depending on your setup).

I ran Maintenance jobs and checked the ‘command Log’ Table by querying this table, we can see all of the job details such as job start and end times, etc.

If you wish to update the Job Schedules, go to the Schedules Folder and look for three files with the titles prod, dev, and test environment. These files include schedule information for all three environments.

You can access the file and make the necessary adjustments to the job schedules to suit your needs. Once you’ve finished making changes, click Save and close the file :

You can also alter the Database Name by opening the file ” Database Creation “, where everything will be saved.

Please see the following excerpt to make changes to the Job creation file:

You may download the whole script from the following Google Drive link:

https://drive.google.com/drive/u/3/folders/1bGHvfW0KutOtmNLznL8xWEzJtF7Z4pp_

Leave a Reply

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