0

I have SQL servers running on onprem and Azure VM hardware.

The backups write to a file share located on a network VM.

I am wanting to change the backup destination to Azure blob storage.

For this I have created an Azure storage account and tested the Ola backup script with credentials (account key/identity and also with shared access signature).

Is want some guidance on what is recommended values to use for the following:

  1. Block size
  2. Buffer count
  3. Max transfer size

I understand that the block blob has a 200GB file limit and this is OK for me as my compressed backups come to under 110GB.

I understand that Azure blob has a limitation of 50000 blocks * 4MB maxtransfersize.

What is the idea values to use so that I don't hit the above limit.

1 Answer 1

1

Conclusion: If using Backup to URL to create striped backups of large databases (over 48 GB per stripe), specify MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536 in the BACKUP statement.

You can stripe your backup over multiple files to support larger databases if needed.

3
  • I'm not using stripped backups. My compressed backups size is 110GB.
    – variable
    Commented Mar 8 at 11:24
  • What exactly is block and Max transfer size. So if block size is X and transfer size is Y, then does it mean that SQL will create multiple X blocks until the total size reaches Y and then upload it to the Azure blob storage?
    – variable
    Commented Mar 8 at 11:26
  • The BLOCKSIZE and MAXTRANSFERSIZE have been around before BACKUP to URL and are documented here: learn.microsoft.com/en-us/sql/t-sql/statements/… They are not used that often but are needed now to support larger backups on Azure DB. Commented Mar 8 at 11:44

Your Answer

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

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