3

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 :

enter image description here

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:

  1. Important parameter Values which I need to modify?
  2. Where will these commands create the SP and related Command Execute and Command Log, all in Master Database or MSDB?
  3. Series of code execution - Which code I need to execute first?
  4. What is command Log here?
  5. 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.

3

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

Hint: Because the tables and procedures are created in the database specified at line 20 in the script (use [master]), you might want to consider storing these objects in a different database, by modifying the script to point to e.g. the msdb database, or some other user specified database.

Hint: Read the description for each parameter and modify according to your requirements or restrictions.

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

  • [dbo].[ComandLog]

Stored Procedures

  • [dbo].[CommandExecute]
  • [dbo].[DatabaseBackup]
  • [dbo].[DatabaseIntegrityCheck]
  • [dbo].[IndexOptimize]

Getting started

Out of the box Ola's MaintenanceSolution.sql script will create the following jobs:

  • Database Backup - SYSTEM_DATABASES - FULL
  • Database Backup - USER_DATABASES - DIFF
  • Database Backup - USER_DATABASES - FULL
  • Database Backup - USER_DATABASES - LOG
  • DatabaseIntegrityCheck - SYSTEM_DATABASES
  • DatabaseIntegrityCheck - USER_DATABASES
  • IndexOpitmize - USER_DATABASES
  • sp_delete_backuphistory
  • sp_purge_jobhistory
  • Output File Cleanup
  • CommandLog Cleanup

Hint: Please be aware that Ola's solution does not create any job schedules! This is something you will have to plan for and create yourself.

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 string SET @JobName[0-9][0-9] = ' with a suitable text-editor and add a prefix to the job's name

e.g. OLA Database Backup - SYSTEM_DATABASES - FULL

Consideration: Please keep in mind that if you update the scripts later on with a newer version from Ola and don't alter the names from the original to match your naming convention, then you will have duplicate jobs. You might want to keep the job names "as-is".

IndexOptimize

Ola created his IndexOptimize stored procedure according to the following Microsoft recommendations:

  • General Index Maintenace when data/index > 1000 pages
    • Fragmentation >5% and <= 30%
      • Do: Index Reorg
    • Fragmentation > 30%
      • Do: Index Rebuild

Hint: Depending on your data and/or the amount of records in some of your tables, you might want to consider creating individual jobs with different parameters that suit your requirements for larger tables.

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 named MyInstance and the database is named MyDatabase and you are looking at a Full backup, then the backup file will be stored in the following directory with the following name:

H:\MyServer\MyInstance\MyDatabase\FULL\MyServer_MyDatabase_FULL_20170712_105500.bak

A DIFF backup will look like this:

H:\MyServer\MyInstance\MyDatabase\DIFF\MyServer_MyDatabase_DIFF_20170712_105500.bak

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:

3
  1. The parameters will depend on what tasks you want, how often and when you want to run them. This is really up to you.

  2. The default location is master db, you can put them elsewhere. But i dont see much benefit in doing that.

  3. Run the script to create the command log table first.

  4. The command log table can be used by the scripts to log events.

  5. Availability groups are supported.

Most of these questions are answered in Ola's FAQ. I highly reccomend you read it.

Personally, I find the full maintenance solution is too much. I prefer to just call the tasks I want using the examples provided.

But it's worth running it in dev as it will give you working examples of how to create the agent jobs. There is no "right answer" with this stuff, you really need to experiment and select what is right for you.

One thing I struggled with is that if you want to back up to a network address you need to grant permission to the account that SQL Server uses. Therefore it should be a domain account.

1

We use the entire Maintenance solution provided by Ola Hellengren i.e.Backup, Database Integrity and Index Optimization.

What we do is create a new database for DBAs and then use it deploy the maintenance solution. This keeps the system dbs separate from any other user /DBA created objects. Just need to change the script a bit for that (Use databasename).

1.Important parameter Values which I need to modify?

For Index Optimize check the SP and configure it according to your need for Index fragmentation in the script itself.

2.Where will these commands create the SP and related Command Execute and Command Log, all in Master Database or MSDB?

Yes, if you use the script as it is it will use master to create all objects. This can however be customized as per your requirement. We do use a different database to get this deployed.

3.Series of code execution - Which code I need to execute first?

Once the solution is deployed ,it will create jobs and then you can schedule it from there.

This is the best available solution with lots of freedom to customize. Once you start using it, it will reduce a lot of burden from your daily activity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.