Category: Backups

Mike Fal ran a survey recently and shares his findings:

This leads me to last week. In order to have some data, I decided to run an informal backup survey targeted at the SQL community. The results floored me: 344 of you decided to take my short survey. This really helps me understand some of the trends out there and now I want to share those results with you.

Before I get started, I want to first thank each and every person who responded from the bottom of my heart. This data is the result of your participation. Secondly, I want to underscore the “informal” nature of this. There’s a lot of holes that can probably be poked in the process, but I think the data is still useful and can give people insight into the trends.

I’ve posted raw data along with a few tools out on GitHub, where you are welcome to download and play with it.

Check out Mike’s findings and then dig into the data on GitHub.

Tracy Boggiano continues her smart backups series, this time looking at differential backups:

SQL Server 2017 introduced a new column for taking smarter backups for differential backups as part of the community-driven enhancements. A new column modified_extent_page_count is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database.  The blog referenced states it takes just as many resources to take a differential backup as a full when there are between 70% and 80% of pages changes. With this field and the allocated_extent_page_count field, we can calculate the percentage of pages changed since the last full backup. So I have added logic into the differential backups that I use in combination with the configuration tables from my Github repository.  To support this change we will be adding two new fields to the DatabaseBackupConfig table:

• SmartBackup
• DiffChangePercent

The main part of the code determines if you are running SQL Server 2017 then determine which databases the percentage is greater than or equal to the value you put in the table.  Then it puts in two separate variables which databases to take full backups of and which ones to take differential backups of.

Click through for the script.

Erik Darling points out that VSS backups aren’t instantaneous and can block queries:

Ah, backups. Why are they so tough to get right?

You start taking them, you find out you’re not taking enough of them, or that they’re not the right kind, or that you’re not using checksums or compression, or that you’re not storing them in the right place, or that the storage isn’t redundant.

It’s just like, why won’t someone make this easy?

Then you read about VSS Snaps, and they look so dead simple. You don’t need your DBA Ph.D to use them.

And look how fast they are! Oh how they blaze.

Read the whole thing.

Tracy Boggiano has started a new series on smart transaction log backups.  Part one involves taking smarter transaction log backups in SQL Server 2017:

SQL Server 2017 introduced two fields to help with taking smart backups.  One was for taking smarter log backups, for this have DMV sys.dm_db_log_stats that have two fields log_backup_time and log_since_last_backup_mb.  With the combination of these two fields, we can put some logic in the jobs that I use for Ola’s scripts that use my config tables from my Github repository.  To support this change we will be added three new fields to the DatabaseBackupConfig table:

• SmartBackup

• LogBackupTimeThresholdMin

• LogBackupSizeThresholdMB

Click through for scripts.

Parikshit Savjani explains how the SQL Server team was able to use indirect checkpoints to improve backup performance:

In RDBMS, whenever tables get larger, one of the technique to tune and optimize the scans on the tables is by partitioning it. With indirect checkpoints, we do the same.

In indirect checkpoint, for every database which has target_recovery_time set, a dirty page manager and dirty page list is created. The dirty page list is further partitioned by scheduler allowing the dirty page tracking to scale further. This decouples the dirty page scan for a given database from the size of the buffer pool and allows the scan to scale and be much faster than automatic checkpoint algorithm.

As Bob Dorr mentions in his blog here, a new database creation process in SQL Server 2016 requires only 250 buffers to scan as opposed to 500 Million buffers with former algorithm. This is the rationale for making indirect checkpoint a default which is much more scalable algorithm to track dirty pages in the buffer pool compared to automatic checkpoints.

Read on to see how this technology led to faster backups.