PowerShell script to Deploy Ola Hallengren for Multiple servers

Powershell script to deploy ola hallegren Maintenance Solution Script,jobs creation,job schedules and the reQuired brentozar store procedures with single click.

But before doing that we need to do some manual work as follows:

Dependendencies : Sysadmin access on the instances you wish to apply the solution to

Step 1     : Prepare a script for creating DBA database to store helpful stored procedures    
             and maintenance logging
Step 2     : Download latest scripts to Create the following stored procedures in the DBA 
             database which are very helpful
                - 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     : Prepare to Create the maintenance jobs for backups, DBCC Check DBs, 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.

I have collected all the scripts and kept in my local c:\temp\ola folder

I have downloaded all the scripts and kept in folders like below :

Once Instance Names are enter into the CSV file , we can deploy this ola hallegren Maintenance Solution script with stored procs, jobs and job schedules to all the servers with singe click by executing a PowerShell script ‘ Deploy-SQLMaintenance.ps1

I deployed to one Instance by executing the script and the output looks like below :

I have created stored procedures in a MASTER Database

I initiated running Maintenance jobs and checked ‘ command Log ‘Table , by Querying this table we can see all the job data where it stores the job information like the job start time and end time ..e.t.c.,

To change the Job Schedules , go to Schedules Folder and you can see here 3 files with names prod, dev, test environment which has schedule information for all 3 environments.

You can open the file and change the schedules of jobs as per your environment :

Also you can change the Database Name where these all needs to get stored by opening the file ‘ Database Creation

To change in Job creation file , pls see below :

Find the entire script in below 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.