SQL 2012 Failover Cluster Pt. 11: Jobs n More

The SQL 2012 failover cluster series is finally starting to wind down. While this is not the last installment, it’s pretty darn close. In this session we configure some critical SQL maintenance checks and run a configuration check script to see how badly our SQL server is configured. A big shout out to Brent Ozar and Ola Hallengren for their outstanding SQL Server scripts and blog resources. These scripts are must-haves for all SQL installations.

Blog Series

SQL Maintenance Script

Many people, it seems, just install SQL server and forget it about. They don’t realize that SQL server is just like a race car. If you leave it alone and don’t do routine maintenance it will fall apart. To that end, Ola Hallengren has the de-facto database maintenance scripts, that I’ve been told Microsoft uses extensively internally.

Download the MaintenanceSolutions.SQL script from here. Just run the script in SQL studio and it will create a number of maintenance jobs for you. It gets regular updates, so check back every few months for improvements. After you run the script you should see the following list of jobs under the SQL Agent.

SQL Blitz Script

I first heard Brent Ozar at TechEd a couple of years ago in a SQL performance session. Besides being able to double as a stand up comedian, he’s one of the world’s few Microsoft Certified SQL Masters and an outstanding speaker. Much of the material for this series came from his site. He has some really funny tweets.

Besides having a blog full of great material, his team has developed the sp_BLITZ script. It performs dozens of SQL configuration checks and warns you about potential issues. This script is also regularly updated to add new checks and support new SQL versions. Plus his site has links to many common issues that the script highlights and provide remediation guidance. You can download the script here.

After you download the script, execute it in SQL studio. You won’t see much happen, since this execution just installs the stored procedure it doesn’t actually run it. We will execute the checks a bit later.

Database Integrity Checks

1. In SQL Server Management Studio locate the SQL Server Agent node. Right click on DatabaseIntegrityCheck – SYSTEM_DATABASES and select Properties.

2. On the Schedules page configure the daily job as shown below, and then click OK.

And the end result is….

Click on the notifications page and enable email and application log events. I suggest ‘when the job completes’ email alerting.

3. Repeat the same configuration process for DatabaseIntegrityCheck – USER_DATABASES but stagger the job run, say an hour later.

4. Run both jobs.

Index Optimizations

1. In SQL Server Management Studio locate the SQL Server Agent node. Right click on IndexOptimize – USER_DATABASES and select Properties.

2. On the Schedules page configure the weekly job and then click OK.

Configure the same notification settings…

Database Backups

1. Turning on backup compression is recommended. Do this by running the following SQL query.

USE master;
GO
EXEC sp_configure "backup compression default", "1";
RECONFIGURE WITH OVERRIDE;

2. In SQL Server Management Studio locate the SQL Server Agent node. Right click on DatabaseBackup – SYSTEM_DATABASES – Full and edit the job steps. Open step 1 and modify the backup path as needed. In my case I changed it to S:\Backup. This should be on the S drive, so that backups are available to each node.

3. Modify the other backup scripts to change the path as well.

4. Execute the DatabaseBackup – SYSTEM_DATABASES – Full job and validate that it runs successfully.

SP_Blitz

Now that we’ve performed backups and integrity checks, its time to run Brent’s store procedure. Type exec sp_blitz and review the output at the bottom of the screen. Not all issues need to be corrected. Some are informational. Brent has a lot of detail for some issues the script finds here. I would run this on a periodic basis, and make sure you check his site for the latest version.

Summary

In this section we have installed scripts from two of the foremost SQL authorities in the world. We also configured scheduled maintenance jobs to kick off to preserve DB performance and validate their integrity. In the last installment of this series we will configure Kerberos and SSL before calling it a day. Check out Part 12 here.