Creating SQL Server Agent Job Schedules for Ola Hallengren’s Maintenance Solution

Data Platform MVP Ola Hallengren has created and maintained his free SQL Server Maintenance Solution script for over eleven years now. His script creates some objects in your master system database (by default). It also creates and enables twelve SQL Server Agent jobs.  These jobs do things like database backups, index maintenance, and database integrity checks. It is a great solution that many SQLskills.com clients use. In order to have the jobs run, you will need to create SQL Server Agent job schedules for each one.

Creating the Schedules

An enabled SQL Server Agent job that does not have a schedule associated with it will never automatically run. I frequently help clients setup and configure the Ola Hallengren SQL Server Maintenance Solution. After many years, I finally got tired of manually creating a job schedule for each of the SQL Server Agent jobs. I decided to create a T-SQL script that you can run to create a schedule for each of the twelve jobs, which you can get here.

Modifying the Schedules

You can (and probably should) modify the schedules in my script to suit your business requirements and infrastructure. For example, depending on your Recovery Point Objective (RPO) SLA, you would probably want to change how often you run transaction log backups. Another example is deciding when to run resource intensive jobs.  These would the “DatabaseIntegrityCheck – USER_DATABASES” job or the “IndexOptimize – USER_DATABASES” job.

Conclusion

If you have you have shared storage, you would want to be more careful about your job scheduling. In that case, you will want to ensure that every instance is not doing resource intensive jobs at the same time. Please let me know what you think of this script and if you have any suggestions for improvements. Thanks!

New Pluralsight Course Published

On January 10, 2019, Pluralsight published my latest course, SQL Server 2017: Diagnosing Performance Issues with DMVs. This makes fifteen courses that I have done for Pluralsight. Here is the official course description:

Learn how to easily query SQL Server 2017 for performance information to help identify and fix issues that can affect performance and stability. This course is applicable to anyone responsible for SQL Server 2017 and earlier versions.

Essentially, I walk you though the activity and performance-related queries from my SQL Server 2017 Diagnostic Queries. I do this by discussing and demonstrating each query and talking about how to interpret the results of each query. Knowing how to understand what each diagnostic query reveals is extremely useful as you are trying to determine what is going on with your SQL Server instance or database.

This course is a companion to my earlier SQL Server 2017: Diagnosing Configuration Issues with DMVs course that Pluralsight published on July 19, 2018.

Despite the title, this course is still applicable for older versions of SQL Server. This is because many of the queries that I demonstrate and discuss will work on older versions of SQL Server. Ideally, you should be using the correct version of my SQL Server Diagnostic Queries that matches your version of SQL Server so that all of the queries will work. Regardless of that, the core concepts are still relevant for older versions of SQL Server.

You can see all of my Pluralsight courses here.

IEPUM2017: Immersion Event on Planning and Implementing an Upgrade/Migration to SQL Server 2017

We’ve just announced the next round on live, online training classes for the first quarter of 2019. Paul has more details about all six classes that we are offering here. I will be teaching IEPUM2017: Immersion Event on Planning and Implementing an Upgrade/Migration to SQL Server 2017 on January 29-31, 2019.

I think this is a very interesting and useful class that is especially relevant during 2019 because of the end of extended support for SQL Server 2008/2008 R2, the end of mainstream support for SQL Server 2014, the release of Windows Server 2019, the upcoming release of SQL Server 2019, new processor releases from both Intel and a newly competitive AMD, and many new memory and storage-related developments that affect SQL Server. I wrote about some of these factors here.

Here is the module list:

  • Module 1: Upgrade Planning
  • Module 2: Hardware and Storage Selection
  • Module 3: SQL Server 2017 Installation and Configuration
  • Module 4: Upgrade Testing
  • Module 5: Migration Planning
  • Module 6: Production Migration Methods

This class is relevant for upgrade/migrations to SQL Server 2016, SQL Server 2017, and SQL Server 2019. I think a lot of organizations are going to be upgrading/migrating to a modern version of SQL Server during 2019-2020, and I want to teach as many people as possible how to avoid doing a “blind migration”, where they don’t do the necessary planning, analysis, and testing, and end up having poor upgrade/migration experience.

You can jump right to the registration page here. I hope to see you in this class!