Perspective from a Junior DBA.
Welcome to our month of community tool blog posts! We will discuss some of our favorite and most used tools and how we use them in the coming weeks. Our first post is on Ola Hallengren’s SQL Server maintenance solution. For some, this will be old news; this will be an entry-level offering to refresh the veterans and enlighten the newcomers.
Ola’s maintenance consists of stored procedures and job scripts that handle most of the daily and weekly maintenance a DBA would be concerned with in their SQL Server shops. I will briefly overview each job in the solution and why they are important.
How to Install Ola’s SQL Server Maintenance Solution
Installing Ola’s Maintenance solution on your test environment is simple and free. The scripts can be downloaded directly from https://ola.hallengren.com/, or you can use another community tool we will review later in the month, DBATools. This maintenance solution can also be used on SQL Server Express Edition also. All that needs to be done is to create a job that calls a batch file and an account that can log in as a batch service to run them. Keep in mind that some of these methods do not include a schedule for the jobs, so I will provide a recommended frequency for each job in the relevant paragraph.
I have included a simple script that can enable Ola Maintenance on your SQL Server express edition instance below:
Or a script for all other editions, should you want an easy rollout for testing and production systems:
CommandLog
The first addition is the CommandLog table. During installation, you choose a database to store the CommandLog table and all the stored procedures. When we install this for our clients, we create a separate DB_Administration database for administrative tools like this to help keep the system databases lean and uncluttered. We will discuss other community tools, but another tool we typically add to this DB_Administration database is WhoIsActive. More on that at a later date!
This table is meant to do as titled, log the commands run from the procedures from Ola’s SQL Server maintenance scripts. If you did a quick install, you may need to go back into the jobs and put ‘Y; for @LogtoTable = as shown below:
After you do that, you can look back at when commands were run, even if the job history isn’t being kept for long in the CommandLog table. Some columns are specific to statistics and index maintenance; others are for every job. This can be useful, as mentioned before, especially if your job history is not long enough to have 30 days of history, which is the default retention time for the CommandLog table cleanup job. We recommend running the CommandLog cleanup job weekly. This will help ensure that the CommandLog table does not grow too large. In addition, we typically create our DB_administration database with a growth limit of 5 or 10 GB for our clients for this purpose. However, the CommandLog table is not the main concern for growth; it is typically our WhoIsActive monitoring collection that can snowball on busy systems.
SQL Server Backups
You may not be surprised that there are still those out there who do not understand the criticality of backups on their SQL Server instances. If you are among those who do not regularly take backups or understand how they work, please pay close attention to this section and review our previous blog posts on the topic:
There are four backups jobs in Ola’s Maintenance solution:
- FULL backups for SYSTEM databases
- FULL backups for USER databases
- DIFF backups for USER databases
- LOG backups for USER databases.
Each has its own purpose and use, and we will go over each. As always, we recommend copying backups off-network to a cloud bucket or secondary system of some sort for ransomware protection and doing regular restore tests to verify the time and viability of your disaster recovery scheme. This will help verify that your RPO and RTO are in line with your configured backups and off-site storage. The old adage is that you are only as good as your last good backup; what do you do if you had a disaster or ransomware? Are you out of business, or are you a couple of hours from being back online and serving your customers?
Full Backups for SYSTEM Databases
System databases are Master, Model, MSDB, and TempDB. Of these four, the three that can be backed up are Master, Model, and MSDB. We recommend running this job daily during off hours because these are essential to the function of the SQL Server. Should there be a disaster, the Master database holds all of the system-level records, logons, endpoints, configurations, encryption keys, and more. MSDB holds agent jobs, broker, database mail, backup and restore history, agent alerts, proxies, and policy-based management information. From what I hear, rebuilding is a long and arduous process and it is best to avoid it if possible. It is easy to recover if you have regular backups being taken. Model is the template on which all new databases are made to match. Unless there is an explicit command, all need databases will match. In some environments, the model database has been highly customized and losing that work could take a significant amount of time to fix and troubleshoot. As mentioned before, daily system backups are important. We recommend keeping at least 2 weeks locally, and then having a long-term off-network retention strategy.
FULL, DIFF, and LOG for USER Databases
User databases are any databases besides the four system databases described above. Making sure your business data Is backed up and recoverable is an important step to be ready for disaster recovery. We recommend taking weekly FULL backups of all user databases, we recommend compression for space savings, checksum for integrity, and enabling the verification step to ensure viability. We recommend DIFFS daily except the day the FULL runs, and for mission critical databases where point in time recovery is important, we recommend transaction log backups every 15 minutes.
Database Integrity Checks
Ola’s SQL Server Maintenance solution has two jobs built-in to check SYSTEM and USER database integrity. We recommend at least weekly integrity checks for both. This helps to identify if there is corruption of any sort with your data. We recommend ensuring all your databases are set to CHECKSUM page verification. You can use this script below to detect any databases in your environment with a PAGE_VERIFY option other than checksum:
SELECT @@SERVERNAME AS [Server Name]
, name AS [Database Name]
, page_verify_option_desc AS [Page Verify is not Checksum]
, Concat('ALTER DATABASE ', QUOTENAME(name), ' SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;') AS [Script]
FROM sys.databases
WHERE page_verify_option_desc <> 'CHECKSUM' AND state_desc = 'ONLINE'
ORDER BY name
Finding corruption in a database is a harrowing thing; knowing about it now is better than finding out later, and if you have no current corruption, you know there is a point to restore to should it appear. This is why we recommend the backup strategy mentioned above. Having maximum of 15 minutes of data loss and point-in-time recovery can help to restore the database to a fully consistent state in the fastest time possible. Getting a regular cadence of integrity checks is essential to database health!
Index Optimization
Index and statistics maintenance are some of the more embattled options, causing many of our clients to have unique solutions. We even have a default than Ola’s when we roll out maintenance. The regular maintenance of stats can help ensure performance stays optimal even as data is being added or removed from the database. It helps the database engine estimate appropriately develop the best plans. Indexes likewise need to be maintained as they can become fragmented as changes occur in the database and page splits occur for those with less than 100% page fill. We usually recommend running this job 1 time per week, and if you do not have SQ LServer Enterprise Edition, please make sure it is during a period of low or no workload on the system since doing it ONLINE is not an option.
Backup History
This job clears the backup history from the MSDB database if it is older than 30 days. Some clients hold on to this a bit longer to track monthly backup growths via a simple MSDB script. Most leave it as is. We recommend running this once per week!
Conclusion
Ola’s SQL Server Maintenance Solution is something we use with or recommend to all of our clients. It is a highly customizable and free tool that has the respect and backing of the industry. We recommend it to you to try. Hopefully, it helps you streamline your SQL Server maintenance processes and frees you up to focus on your business! If you ever have any questions, we are here to help!
