2

Recently I ran into an error when I tried to backup a database to Azure Blob storage. The error was this:

1117(The request could not be performed because of an I/O device error.)

The database .mdf file was about 80gb.

Upon reading a few postings, it turns out Azure Blob storage does have some type of limitation. If I understand correctly, there are only 50,000 blocks available and each block can hold roughly 4Mb.

I played around with the following BACKUP DATABASE TO URL with varying parameters:

  1. BACKUP DATABASE [mydb] TO URL WITH INIT (failed)
  2. BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION (succeeded)
  3. BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION, MAXTRANSFERSIZE = 4194304 (failed)
  4. BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536 (succeeded)

When the operation was successful, the backup file produced was about 7.8Gb. Again, the .mdf file was about 80Gb.

I'm a bit confused as to how the parameter BLOCKSIZE is used as part of the BACKUP T-SQL command. Can someone explain why the operation would fail when this parameter is omitted?

1 Answer 1

4

Azure Blob Storage has the following blob and block size limitations:

The following table describes the maximum block and blob sizes permitted by service version.

Service version Maximum block size (via Put Block) Maximum blob size (via Put Block List) Maximum blob size via single write operation (via Put Blob)
Version 2019-12-12 and later 4000 MiB Approximately 190.7 TiB (4000 MiB X 50,000 blocks) 5000 MiB (preview)
Version 2016-05-31 through version 2019-07-07 100 MiB Approximately 4.75 TiB (100 MiB X 50,000 blocks) 256 MiB
Versions prior to 2016-05-31 4 MiB Approximately 195 GiB (4 MiB X 50,000 blocks) 64 MiB

As you noted in your question:

BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION

completed successfully, without the blocksize parameter.

Interestingly, I just attempted another backup, and received the 1117 error. Looking at the SQL Server Error log, I see the following:

Error: 3063, Severity: 16, State: 1.
Write to backup block blob device https://xxx.blob.core.windows.net/sql-backups/StackOverflowCS.bak failed. Device has reached its limit of allowed blocks.

Since SQL Server 2019's maximum block size is 65536 bytes, and the maximum number of blocks per blob is 50,000, that equates to a maximum blob file size of 3,125 MB. SQL Server's BLOCKSIZE= parameter does not control the size of Azure Block Blob block sizes. The only BACKUP... parameter that has any bearing on block size for backup to Azure is the MAXTRANSFERSIZE parameter. Dimitri Furman talks about that in this TechCommunity post, where they mention:

How can we make SQL Server use larger block sizes, specifically 4 MB blocks? Fortunately, this is as simple as using the MAXTRANSFERSIZE parameter in the BACKUP DATABASE statement. For 4 MB blocks, we used the following statement:

BACKUP DATABASE … TO
URL = 'https://storageaccount.blob.core.windows.net/backup/DB_part01.bak',
…
URL = 'https://storageaccount.blob.core.windows.net/backup/DB_part20.bak',
WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 5;

I'm was able to backup a 150GB database to Azure Blob Storage using this statement:

BACKUP DATABASE StackOverflowCS 
TO URL = N'https://xxx.blob.core.windows.net/sql-backups/StackOverflowCS.bak'
WITH COPY_ONLY
    , STATS = 1
    , INIT
    , MAXTRANSFERSIZE = 4194304
    , BLOCKSIZE = 512;

With compression, the backup consumed 81.2GB of space in a single file, and as you can see I did the backup with BLOCKSIZE=512. Since that is the default blocksize, I could have left that parameter out.

Here's what the blob looks like in Azure Storage Explorer:

enter image description here

To calculate the number of stripes you need for a given backup, you need to know the number of 4MB blocks SQL Server will create with the MAXTRANSFERSIZE = 4194304 parameter set. Then divide that number by the maximum number of blocks per blob which is 50,000. So for a backup that will consume 200GB, that's 214,748,364,800 bytes / 4194304 = 51,200. Since 51,200 is slightly over the maximum of 50,000 blocks per blob, you'd need two stripes.

1

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.