Azure - SQL Server on VM: backup DBs directly to blob storage RAGZRS

Question

I'm managing AG of 4 nodes in AZ VMs. SQL Server version is 2016 CU19, largest DB is ~2 TB.

Right now we are using Ola Hallengren's scripts with options as below:

@MaxTransferSize = 4194304
@NumberOfFiles = 5 (or 10)
And backing up to blob storage ZRS. We decided to move from ZRS to RAGZRS and there begins a problem.

It seems that the the storage is not supporting MAXTRANSFERSIZE, stripped backups and BLOCKSIZE backup options. Backup with single file without the above options, works. unfortunately, with one file - no backup stripping, VLDB will not work - there is blob storage limitation.

One more point, there is an option to connect to Azure container using SSMS. unfortunately, attempt to connect to this RAGZRS container, ends up with the connection windows is disappearing.

what is the problem with the blob storage RAGZRS?

Here are the exceptions I received while attempting to test. there is direct correlation to each attempt explained above.

Script used:
BACKUP DATABASE [DB] TO
URL = N'https://container.blob.core.windows.net/sqlbackups/FULL_20210205_160556_1.bak'
URL = N'https://container.blob.core.windows.net/sqlbackups/FULL_20210205_160556_2.bak',
URL = N'https://container.blob.core.windows.net/sqlbackups/FULL_20210205_160556_3.bak',
URL = N'https://container.blob.core.windows.net/sqlbackups/FULL_20210205_160556_4.bak',
URL = N'https://container.blob.core.windows.net/sqlbackups/FULL_20210205_160556_5.bak'
WITH CHECKSUM, COMPRESSION, FORMAT, MAXTRANSFERSIZE = 4194304, CREDENTIAL = 'DBBACKUPCRED02'

I'm getting these errors as below
while attempting to run stripped backup:

Msg 3294, Level 16, State 1, Line 1
Use of the URL device type is limited to a single device during Backup and Restore operations.

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

while attempting to run with BLOCKSIZE option:
Msg 3291, Level 16, State 2, Line 1
URL device type was specified, and a disallowed option BLOCKSIZE was specified.

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

while attempting to run with MAXTRANSFERSIZE option:
Msg 3291, Level 16, State 3, Line 1
URL device type was specified, and a disallowed option MAXTRANSFERSIZE was specified.

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Answer 1

so here is the answer for these who ants to know what was the problem:

there is a bug with RAGZRS and you cannot access the container from SSMS.

It is also not possible to create credential by GUI- it returns error:
Error converting value "Standard_RAGZRS" to type 'Microsoft.SqlServer.Management.ServiceManagement.ResourceManagement.AzureStorageAccountSkuName'. Path 'sku.name', line 1, position 32.

Nevertheless, it is possible to create credential by script, please refer to the below URL
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql?view=sql-server-ver15#d-creating-a-credential-using-a-sas-token

AND THE MOST IMPTRANT PART you should pay attention to, is the below statement:
The SHARED ACCESS SIGNATURE secret should not have the leading ?.

After creating SAS credential w/o leading ?, the magic works and the backup files are being written as block blob which allows creating striped SQL backups along with rest of the options I have mentioned in my original post.

Enjoy!

Answer 2

Hi @Vlad Kirov ,

> SQL Server version is 2016 CU19

Please using select @@version to check your SQL Server version. There is no version of SQL server 2016 CU19. Refer to SQL Server Versions and Build Numbers.

> It seems that the the storage is not supporting MAXTRANSFERSIZE, stripped backups and BLOCKSIZE backup options.

Yes, you are right. There are definitely restriction there as troubleshooted in this document.

Here are some of other restrictions that you may face - Limitations. Also here is a document to handle larger DB with different methods rather than dividing it into different files.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.