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 :