Backup Performance best practices

Question

I'm in the process of re-writing our maintenance plans and at the moment the focus is backups. Whilst preparing this plan, I'd like to try and ensure that procedures are in place to ensure that backup performance is optimal. I am aware of implementing the below, however I'd like to get an idea of how I can improve beyond that scope.

Current Considerations:

• Backup Compression
• Multiple data files for larger databases - perhaps over 100GB?
• Writing backups to another I/O subsystem
• Defining values for parameters including BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT
• Purging old files
• Deleting backup history

Beyond the above, is there anything that would be benificial to implement. Also whilst setting the values for BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT, are there any considerations I should take/how do I go about defining the correct values? I appreciate it will be a bit of trial and error with testing but it would be useful to get an idea of best practices.

To give an idea of my routine, I'm to run hourly transaction log backups, daily differentials and weekly full backups.

Thanks

Answer 1

Start planning to upgrade to SQL Server 2017 as backup operation has been made faster in SQL Server 2017. How Do We Made Backups Faster With SQL Server 2017

Beyond the above, is there anything that would be benificial to implement. Also whilst setting the values for BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT, are there any considerations I should take/how do I go about defining the correct values?

For SQL Server 2014 I believe you already know most of the things, I would help you in deciding how to choose appropriate value for BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT. Here trace flags can be used to dump additional information into errorlog. For example

dbcc traceon(3605, 3004, 3014, 3213, -1)
go
backup database [AdventureWorks2012] to
disk='D:\Backup Parallelism\Adventureworks.Bak'

This command will force SQL Server to write additonal parameters into errorlog about internal backup operations.

Memory limit: 249MB
BufferCount:                7
Sets Of Buffers:            1
MaxTransferSize:            1024 KB
Min MaxTransferSize:        64 KB
Total buffer space:         7 MB
Tabular data device count:  1
Fulltext data device count: 0
Filestream device count:    0
TXF device count:           0
Filesystem i/o alignment:   512
Media Buffer count:            7
Media Buffer size:          1024KB

Now if you see for the simple backup the SQL Server internally chooses 7 buffer buckets and and maxtransfer size of 1 MB. You can perform similar test on UAT and can play around with above 2 values. Please note these parameters are selected considering various resources available and SQL Server takes best decision to make sure the backup runs faster. Make sure not to make the values too large or you will end up with OOM error.

Choosing optimum number of backup files is also important. Brent Ozar in This Blog did some test on backup speed with single to multiple files, this may be helpful to you.

Finally some good readings about SQl Server backup and restore operation from MSDN blogs.

• How It Works: How does SQL Server Backup and Restore select transfer sizes
• How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)

PS: The trace flags are undocumented so please use it ONLY in UAT environment. The same is mentioned in the MSDN links I have shared.

Answer 2

Firstly, I'd encourage you to stop using maintenance plans for your backups. Ola Hallengren's maintenance scripts are used by a LOT of people. Simply schedule them using the SQL Agent.

I agree that taking steps to ensure your backup process is optimal, but I'd also encourage you to think about your restore strategy as well.

By taking transaction log backups every hour, you are saying it's ok to lose up to 1 hour of updates should you need to restore. IMO, implementing more granular backups is really no more difficult and brings some additional piece of mind. See Back Up Transaction Logs Every Minute. Yes, Really.

When researching and developing a restore strategy, the main two things you'll tend to see over and over are: RTO (Recovery Time Objective) and RPO (Recovery Point Objective).

RTO basically is the maximum time allowed to get things back to 'normal' or at least functioning. You would need to take into consideration small problems (server went down) to big problems (the building just exploded).

RPO basically is, how much data could we 'possibly' lose should a bad problem happen? 5 minutes, 1 hour, 1 day, NONE?

Take some time to read The 9 Letters That Get DBAs Fired (Basically RPO, RTO and CYA).

Also, The Accidental DBA (Day 8 of 30): Backups: Planning a Recovery Strategy, discusses Designing a Restore Strategy.

One last thing - develop a plan to routinely test your backups.

The only way to verify a backup is good/usable is to restore it.

REPEAT – you don’t know you have a good backup until you have restored it!

I provided an example in my answer to How to conduct integrity test on SQL Server database backup file?

Answer 3

In addition to what has already been suggested, it is recommended to pay attention to the following points:

Backup WITH COMPRESSION - a backup with on-the-fly compression can be faster than a backup without compression. This is due to the fact that the bottleneck in performance when creating a backup is writing to disk, and the less you write to disk, the faster the backup.

Backup WITH CHECKSUM - this option will add checksums to the backup file, which will be checked during restoring. If a backup contains broken data, SQL Server will still be able to restore such a backup, but at least you will know that the data that was restored contains errors. Please note - this option has a negative impact on backup performance (which is why it is not enabled by default) - but it is extremely useful.

By the way, you can check the correctness of the checksums earlier if you run restore verify only. This can be done on the test server immediately after the backup is completed.

Useful materials on the topic

Media errors: Backup and Restore - SQL Server | Microsoft Learn

SQL Backup Mastery: Tips, Tricks, Recommendations

Top 5 Tips for Speeding Up SQL Server Backups