In this blog installment we configure some critical SQL maintenance checks and run a configuration check script to see how well 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 2014 Always-on AG Pt. 1: Introduction
SQL 2014 Always-on AG Pt. 2: VM Deployment
SQL 2014 Always-on AG Pt. 3: Service Accounts
SQL 2014 Always-on AG Pt. 4: Node A Install
SQL 2014 Always-On AG Pt. 5: Unattended Node B
SQL 2014 Always-on AG Pt. 6: Cluster Configuration
SQL 2014 Always-on AG Pt. 7: TempDB
SQL 2014 Always-on AG Pt. 8: Max Mem & Email
SQL 2014 Always-on AG Pt. 9: SQL Maintenance
SQL 2014 Always-on AG Pt. 10: AAG Setup
SQL 2014 Always-On AG Pt. 11: File Share Witness
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. 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. After we configure the Always-On Availability group we can run it again, for thorough coverage.
As always, defer to your professional SQL DBAs when configuring the following settings. Workloads vary wildly, so the settings below may not be the best for your environment. These are just a baseline configuration for simple low intensity databases.
Remember to run all the steps below on each of your two SQL nodes. They aren’t in a traditional failover cluster, so each node is independently configured. Just doing it once won’t cut it.
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 configured and scheduled maintenance jobs to preserve DB performance and validate their integrity. As I said before, defer to your in-house professional DBA for the recommended settings in your environment. These are just a starting point for a small environment. Next up in Part 10 we configure the Windows firewall, prep for AAG setup, then run the AAG setup wizard.
