Optimizing SQL Server Backup Performance Using Data Science Techniques to Solve a Classic DBA Dilemma

  • Kevin Feasel

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

You're watching a preview of subscription content. Log in to check access

Learn how to optimize the performance of your SQL Server backups using data science techniques. Microsoft provides default backup settings which work but which are decidedly sub-optimal against large databases. This video shows what these settings are, explains their defaults, and educates you on the available values that provide you room in which to optimize your backup times. The end game is that you’ll be able to optimize your backups and be confident that you’ve optimized to the extent that’s possible given your specific environment.

After an introduction to the available settings, you’ll learn a method built for tuning your backup settings to provide faster backups and shorter maintenance windows in your own environment. That method includes knowing how to sample timing data and other statistics, and how to apply data science techniques by which to understand your findings and to implement your findings in two of the most popular, community-based backup tools: MinionWare’s Minion Backup and Ola Hallengren’s SQL Server Maintenance Solution. You’ll be able to confidently schedule maintenance windows and to defend those schedules to management using the data that this video helps you to collect.

What You Will Learn

  • Know the key settings that control SQL Server backup performance

  • Create database backup statements using these key settings

  • Use sampling within PowerShell to test performance of different settings

  • Create models using algorithms such as random forest for statistical learning

  • Analyze the results of these models using evolutionary programming techniques

  • Implement the results using free, community-based backup solutions

Who This Video Is For

Database administrators who wish to minimize the size of their backup windows and those wanting to get their backups done in the least-possible time. Viewers should be familiar with SQL Server and the T-SQL language. The video will also include PowerShell and R, so familiarity with either of these languages is nice but not required.

This video helps you to optimize SQL Server backup performance and control the length of maintenance windows through data science techniques.

About The Author

Kevin Feasel

Kevin Feasel is a Microsoft Data Platform MVP and CTO at Envizage, where he specializes in data analytics with T-SQL and R, forcing Spark clusters to do his bidding, fighting with Kafka, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL and author of PolyBase Revealed. A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather’s nice enough.

 

Supporting material

View source code at GitHub.

About this video

Author(s)
Kevin Feasel
DOI
https://doi.org/10.1007/978-1-4842-7194-0
Online ISBN
978-1-4842-7194-0
Total duration
1 hr 7 min
Publisher
Apress
Copyright information
© Kevin Feasel 2021

Related content

Video Transcript

[MUSIC PLAYING]

Hi, I’m Kevin Feasel and this is Optimizing SQL Server Backup Performance Using Data Science Techniques to Solve a Classic DBA Dilemma. In this video, we are going to help you solve a classic problem for DBAs, making database backups run faster. We’ll review several techniques for improving backup performance and focus on key backup configuration settings which will help speed things up. From there, we’ll use data science techniques to train a model and show you how you can speed up database backup times on your own servers.

I’m going to assume that you are a DBA who needs to shrink backup times. Furthermore, I’m also going to assume that you’re familiar enough with SQL Server and T-SQL that I won’t need to explain the process of backing up databases. Hopefully, you have at least a little bit of PowerShell knowledge, but you certainly don’t need to be an expert for this.

As far as data science goes, we’ll work with the R programming language. But don’t worry, if all you know about R is that it’s the punch line of a pirate joke, you’ll still do fine here. Once we train a model, I’ll show you how to implement those findings in two popular community based backup tools, Minion Backup and Ola Hallengren’s Maintenance Solution. So join me as we make those backups zoom in optimizing SQL Server Backup Performance Using Data Science Techniques to Solve a Classic DBA Dilemma.