6

I am tuning SQL Server backups using compression, writing to multiple files, and changing the BufferCount and MaxTransferSize options. I have a SQL Server instance with a 4 TB database and several smaller databases from a few MB to 150 GB.

My question is, can I start tuning with the 150 GB DB and then apply those settings to the 4 TB backup with minimal changes, or does the 4 TB require its own "tuning session" from scratch? Since it usually takes several backup attempts to dial in the settings I'm trying to get a jump-start on tuning the 4 TB database if possible.

Improve this question
1
  • 1
    it would be great if you could publish your findings, so that we can all benefit from them.
    – Henrik Staun Poulsen
    Jun 22, 2016 at 11:18

2 Answers 2

Reset to default
3

Backing up the entire database to a single file would be much slower. You can consider below points

  • Stripe your backups across different spindles on a SAN.
  • Use backup compression (costs you more CPU) but it results in fewer overall I/O operations during backup and restore operations.
  • you can use the trace flags 3605 and 3213 to find out the default BUFFERCOUNT value used in your backup and then tune accordingly.
  • Set your max memory away from default as when you increase BUFFERCOUNT and MAXTRANSFERSIZE, they require additional memory.
  • For your 4TB database, assuming you have different filegroups for putting recent and historical data, you can mark the historical data as READ_ONLY and then just back it up once. You should perform regular backups of the read/write filegroups.
  • Use BACKUP ... WITH CHECKSUM as it places a checksum on every page in the backup file which helps in detection of corrupt pages in the backup file when doing restores.
  • Best is to backup locally on the server (provided you have enough disk space) and then transfer the backup file to a remote backup location -- or -- if you are backing up directly to a network share, then best is to use Multiple NICs and/or just use differnt logical subnets in the network.

Below is from the Whitepaper - A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network :

enter image description here

As a side note, its more important to test your restore strategy as a backup is ONLY GOOD if it can be restored without any issues. Enabling Instant File Initialization will considerably cut down the restore time.

Be careful when you are striping your backups as there are chances that a stripe might get corrupted or might become missing (deleted) due to machine or human error :-)

References :

Improve this answer
2
  • I just wanted to point out that the backup doesn't place anything on the pages, it just verifies the CHECKSUM on the pages and generates a checksun for the entire backup. Please see here -- msdn.microsoft.com/en-us/library/ms189055(v=sql.120).aspx
    – DenisT
    Nov 12, 2014 at 21:02
  • If you're feeling really savvy, roll your own VDI, so you can inject your own compression algorithm into it, which will allow you to restore directly to another SQL server without having to go through the file system at all. Unless you really want to. At that point you could even go to both a filesystem and SQL server simultaneously...
    – Brain2000
    Jul 12, 2019 at 0:13
1

You really need to make a best guess and then give it a try. You need to be sure that you have enough memory kept free so as to support your BufferCount and MaxTransferSize options. The tuning includes both getting enough speed and avoiding using too many resources for the other operations on the server.

I would suggest that you work with your 150 GB database and then try applying that to the 4 TB database and compare the overall results. A useful technique for testing the read speed of the backups, which is what you are trying to oversome, is to backup to:

... DISK = N'NUL'

Since 'NUL' just throws away the data, this can help you test your tuning more quickly.

NOTE: If you are using Ola Hallengren's backup solution, these parameters are also supported

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.