By Garth Jones
SQL Server performance is one of the biggest items that will affect ConfigMgr’s performance, so when you ask for help about ConfigMgr’s performance, you will almost always be pointed to Ola Hallengren’s SQL Server Maintenance Solution. Many ConfigMgr administrators, however, are NOT SQL Server experts, so how do you install this solution? This blog post will answer that question.
1. Start by reviewing Ola’s blog post.
2. Next, download the 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.
3. 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 will notice is that Ola keeps the script up-to-date. You can see that the copy I’m using is from 20 June 2016 and it supports all versions of SQL from SQL Server 2005 to 2016.
4. 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 D:\SQLbak.
5. 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,” 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?”
6. About a minute later, you should receive this message within the messages window, “Command(s) completed successfully.”
7. 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 Master database, you will see a table called dbo.CommandLog
b. Under the Jobs node, you will find a number of jobs configured. The main one for most ConfigMgr admins is this one: IndexOptimize – USER_DATABASES
8. Now that the jobs are created, I would test the IndexOptimize – USER_DATABASES job before you schedule them. Remember that this task will take some time to complete.
9. 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.
For 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.” If you have any questions, please feel free to contact me at @GarthMJ.