olahallengren/sql-server-maintenance-solution
Sql Server Maintenance Solution
This project is a T-SQL maintenance framework and suite of automated scripts for SQL Server. It functions as a backup automator, index optimizer, and integrity checker designed to manage routine database administration tasks through a programmable set of stored procedures.
The solution distinguishes itself through a focus on automated orchestration, including the ability to target or exclude databases within Availability Groups and the use of mirror-write backup streams for redundancy. It employs fragmentation-driven indexing to determine whether to rebuild or reorganize indexes based on user-defined thresholds and utilizes token-based file pathing for dynamic backup naming.
The framework covers broad capability areas including backup management with retention policy enforcement, data integrity monitoring through consistency checks, and performance optimization via statistics updates. It also provides maintenance auditing through centralized relational logging and supports backup encryption using server certificates.
The system is implemented using T-SQL-based procedural logic and a table-based configuration store to execute operations directly within the database engine.
Features
- Scheduled Database Backups - Performs automated full, differential, and transaction log backups to local or cloud storage.
- Automated Backup Systems - Provides a comprehensive system for scheduling and managing periodic database backups.
- Automated Backup Integrity Checks - Runs verification checks on produced backups to ensure data is readable and consistent.
- Database Consistency Checks - Runs database consistency checks to ensure data health across various levels of the database hierarchy.
- Database Indexing Tools - Optimizes query performance by automatically rebuilding or reorganizing fragmented indexes and updating statistics.
- Database Integrity Checks - Performs automated consistency checks and verifies backup readability to detect physical or logical data corruption.
- Maintenance Target Filtering - Provides logic to target specific databases or exclude those in Availability Groups during maintenance.
- Relational Configuration Stores - Stores job settings and retention policies in dedicated tables for dynamic runtime parameterization.
- Stored Procedures - Executes maintenance operations using stored procedures directly within the database engine.
- T-SQL Maintenance Frameworks - Implements a programmable framework of stored procedures and jobs for auditing and executing parallel database administration tasks.
- Index Maintenance - Implements fragmentation-driven routines to rebuild or reorganize indexes and update statistics for improved query performance.
- Index Maintenance - Rebuilds or reorganizes indexes based on fragmentation levels using online or offline operations to ensure retrieval efficiency.
- Fragmentation-Based Index Optimization - Determines whether to rebuild or reorganize indexes by analyzing fragmentation levels against user-defined thresholds.
- Automated Server Maintenance - Provides a comprehensive suite of automated scripts for managing backups, integrity checks, and index optimization in SQL Server.
- Availability Group Orchestration - Targets maintenance and backup operations to specific availability groups or excludes databases belonging to them.
- SQL Server Availability Group Administration - Coordinates maintenance and backup tasks specifically for databases within or excluded from Always On availability groups.
- SQL Server Integrity Monitoring - Runs automated consistency checks to detect physical or logical corruption across databases, filegroups, and tables.
- Multi-Destination Backup Storage - Routes backups to multiple destinations including local disks, network paths, and cloud storage.
- Mirrored Backup Streams - Simultaneously writes backup data to primary and secondary directories for redundancy.
- Statistics Updates - Refreshes index and column statistics for specific objects to ensure the query optimizer chooses efficient execution paths.
- Parallel Task Execution - Executes maintenance tasks across multiple databases simultaneously by distributing workloads across available CPU resources.
- Token-Based Path Resolution - Generates dynamic backup filenames and directory structures using server and database metadata tokens.
- Backup Set Mirroring - Writes backups to a primary directory and one or more mirror directories simultaneously.
- Dynamic File Name Resolvers - Resolves placeholders in filenames to generate dynamic directory structures and backup names.
- Maintenance Activity Logging - Provides centralized relational logging to track the start, end, and outcome of maintenance tasks for troubleshooting and compliance.
- Policy-Based Retention - Deletes old backup files based on expiration dates or retention periods to reclaim storage.
- Execution Audit Trails - Provides structured logging and audit trails for the execution of database maintenance tasks.
- Parallel Maintenance Task Distribution - Implements parallel distribution of maintenance tasks across available CPU resources to accelerate execution.
- Server Activity Logs - Records the start, end, and outcome of maintenance tasks to a central table for auditing and troubleshooting.
