Never before I used Ola Hallengren Scripts .
I am planning to deploy the Ola Hallengren scripts for Index Optimization for the first time. I need to deploy them in PROD so I want to be more careful. Anyhow will first test in Dev environment. But please help me understanding the important things to keep in mind before deploy OH Index optimization scripts.
I copied the media from link and below commands :
As I mentioned I never deployed the OH scripts before.
Can anyone who already deployed and successfully using the OH Index Optimization scripts guide me on below:
- Important parameter Values which I need to modify?
- Where will these commands create the SP and related Command Execute and Command Log, all in Master Database or MSDB?
- Series of code execution – Which code I need to execute first?
- What is command Log here?
- We are with AOAG set up on SQL Server 2014. Please guide me keep this in view.
NOTE : We are using AOAG 2014.
Please help me keeping this in mind. I can first test in Dev and later deploy in PROD.
Best Answer
Ola Hallengren's solution is based on beset practice and years of experience. There are some quirks though. Take a look....
Before you start
Before you run the initial script, check the first few parameters and the database used:
use [master]
@CreateJobs
@BackupDirectory
@CleanupTime
@OutputFileDirectory
@LogToTable
Objects created
The solution is deployed as one script named
MaintenanceSolution.sql
, which itself will create stored procedures, tables and jobs .Following is a list of objects that are created:
Table
Stored Procedures
Getting started
Out of the box Ola's
MaintenanceSolution.sql
script will create the following jobs:Now if you know that these jobs belong to Ola's solution, then you can leave them as is. Otherwise if you would prefer to mark them, then you might consider prefixing a string inside the
MaintenanceSolution.sql
before you execute the script. Do a regex search for the stringSET @JobName[0-9][0-9] = '
with a suitable text-editor and add a prefix to the job's namee.g.
OLA Database Backup - SYSTEM_DATABASES - FULL
IndexOptimize
Ola created his
IndexOptimize
stored procedure according to the following Microsoft recommendations:Database backups
The backup job create sub-directories for each level of the database's instance. So if you are running the script on a server
MyServer
on an instance namedMyInstance
and the database is namedMyDatabase
and you are looking at a Full backup, then the backup file will be stored in the following directory with the following name:A DIFF backup will look like this:
etc.
Best practice
Consider deploying the solution to a development server as is (ok, modify the database you will be using) and then have a look at the individual scripts, jobs and tables. Familiarise yourself with the solution and then modify according to your requirements.
And of course consider reading Ola's documentation on his site: