Getting the Most Out of the SQL Server Maintenance Solution

Explore the latest features and best practices of the "SQL Server Maintenance Solution" in this 48-minute conference talk from PASS Data Community Summit. Dive into advanced topics such as availability groups, smart backup strategies, managing environments with numerous databases, Managed Instances, and SQL Server on Linux. Learn how to optimize backup performance, implement dynamic backup file numbers, and utilize smart transaction log backups. Discover techniques for backing up and performing integrity checks on availability groups, as well as strategies for parallel database backups and checkdb operations. Gain insights into backing up to Azure Blob Storage and adapting the solution for SQL Server on Linux. Walk away with practical knowledge to maximize the potential of this powerful maintenance tool in various scenarios.

Intro
Session Evaluation
Intro to the SQL Server Maintenance Soluti
Improving backup performance • BUFFERCOUNT and MAXTRANSFERSIZE
Dynamic number of backup files
Smart transaction log backup • Do more frequent log backups when the volume of transactions is high
Backup of availability groups - The basics
The Availability Group Backup Preference
Pros and cons of backups on the secondary
Strategies for backup of availability groups
Checkdb of availability groups - The basics
Backing up databases in parallel • Very large number of databases Backing up databases sequentially takes a very long time
Real-world case: Parallel backup
Checkdb of databases in parallel
Real-world case: Parallel checkdb
Backing up to Azure Blob Storage Legacy Azure Blob Type Page Max size per file 1 TB Max total backup size 1 TB Striping
Backup on SQL Server on Linux • Forward slash instead of backslash in the directory path
Downloads, Documentation and Issues