Endpoint Insights
Ola Hallengren’s SQL Server Maintenance Solution
Topics: Endpoint Insights
I originally posted these steps back in 2016, but with Ola Hallengren updating his SQL Server Maintenance Solution recently, I felt it was time to review, update and refresh my blog post. All of this is to say that it is a mix of content from 2016 and now. I did notice that there have been minor changes to the script over time, nothing major that I saw, but there are some differences.
SQL Server performance is one of the biggest items that will affect SCCM performance, so when you ask for help about SCCM performance, you will almost always be pointed to Ola Hallengren’s solution. Many ConfigMgr administrators, however, are NOT SQL Server experts, so how do you install this solution? This blog post answers that question.
Installing SQL Server Maintenance Solution
- Start by reviewing Ola’s blog post: https://ola.hallengren.com/.
- Next, download MaintenanceSolution.sql from the blog post link.
- Since it is a SQL script, it might get blocked by AV software. Keep an eye out for that, and, unblock the script if it does get blocked.
- Open SQL Server Management Studio (SSMS), connect to your SQL Server and open the MaintenanceSolution.sql file that you just downloaded.
The first thing you notice is that Ola keeps the script up-to-date. You can see that the copy I’m using is from 2020-01-26 and it supports all versions of SQL from SQL Server 2008 to 2019.
STOP
There is a question that you need to ask yourself. Are you okay with adding tables to the master database? This script does that and I’m of two minds where this is concerned. First, I’m fine with allowing this script to do that, but not with others. In those instances, I would not allow it and I would make those other scripts use their own database. On second thought, how is Ola’s script different from any other script? Why shouldn’t it be within its own database? This second idea comes from my friend Steve Thompson. He would say that he was NOT okay with Ola’s script adding tables to the master database.
Now, just to put a wrinkle into the mix. If you are using a ConfigMgr licensed SQL Server, it is by the letter of the law, NOT okay to create another database just to keep ConfigMgr healthy. However, most admins believe that in the spirit of SQL Server licensing, it is okay. Why? Because the sole purpose of the script is for ConfigMgr only.
You must decide which way you will go: let this script add tables to the master database or not allow it to do that. For the purposes of this blog post, I am going to allow Ola’s script to add tables to the master database.
- There is only one line that needs to be edited. Adjust the backup directory to where you want to store your backup. In my case this is E:SQLbak. DECLARE @BackupDirectory nvarchar(max) = ‘E:SQLbak’ — Specify the backup root directory. If no directory is specified, the default backup directory is used.
- Click the Execute button.
Note: If you receive a message that states, “SQLServerAgent is not currently running so it cannot be notified of this action,” like the one above, this means that your SQL Server Agent Service is not running. Correct this and execute the query again. For tips on how to enable the agent see my blog post, “How Do I Enable SQL Server Agent Service?”
- About a minute later, you should receive this message within the messages window: “Commands completed successfully.”
Confirm SQL Server Maintenance Solution Successfully Installed
- How can you confirm that the SQL Server Maintenance Solution successfully completed or that it is installed? There are two places to check.
A. Under the Jobs node, you will find a number of jobs configured (highlighted above). Once you see these jobs, you know that Ola’s script installed successfully. The main one for most ConfigMgr admins is this one: IndexOptimize – USER_DATABASES
B. In the master database, you’ll see a new table called, dbo.CommandLog. You can query the table by typing Select * from dbo.CommandLog. You should see results similar to the ones in the screenshot above. When you see these results, you will know that Ola’s script completed successfully. Keep in mind that the results could be null until you run the first job.
- Now that you have confirmed that Ola’s solution is successfully installed and the jobs are created, first test the IndexOptimize – USER_DATABASES job. It’s a good idea to test it before you schedule it, so I definitely advise testing it first. Remember that this task takes some time to complete, so it might take hours before it finishes the first time!
- Once you are happy with the job, you can schedule it. I recommend adjusting the scheduled time to whatever makes sense for you. I personally like to perform indexing daily, but you will need to gauge your own environment to see if this makes sense. I also recommend that you not have this task occur on the quarter hour increment. Why? A number of other tasks within ConfigMgr happen on the quarter hour, so you want this task to run at a random time in the middle of the night. Ideally, it’s when SQL Server usage time is at its lowest.
If you would like more details about how to run a job or how to schedule a job, please review my blog post, “How to Backup Your ConfigMgr Database Using SQL Server.” Please feel free to contact me at @GarthMJ if you have any additional questions.
