SSMS Back-up and Restore SQL Database to Blob Stopped Working After Switch to Azure Storage Gen2 with AD DS

Question

A couple of months ago, our team followed this Microsoft Tutorial on backing up and restoring databases to a blob storage container, allowing us to keep large databases attached to our SQL Server instance, but backed by blob rather than expensive disk.

RESTORE DATABASE SomeDatabase
    FROM URL = 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/SomeDatabase.bak'
    WITH
        MOVE 'some_database_data' TO 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/SomeDatabase.mdf',
        MOVE 'some_database_log'  TO 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/SomeDatabase.ldf'
, REPLACE

We were successful and had about 20 databases attached this way. Just last week, we provisioned a new Azure Storage Gen2 account with "Hierarchical namespace" enabled, copied all our old storage account configuration and data, and switched everything over to it. The only thing that broke was this script. Now when it runs, we get the following error (formatted for readability):

Operation on target Move Database to Blob failed: Execution fail against sql server. Sql error number: 3634. Error Message:

The operating system returned the error '13(The data is invalid.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/SomeDatabase.mdf'. File 'some_database_data' cannot be restored to 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/SomeDatabase.mdf'. Use WITH MOVE to identify a valid location for the file.

The operating system returned the error '13(The data is invalid.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/SomeDatabase.ldf'. File 'some_database_log' cannot be restored to 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/SomeDatabase.ldf'. Use WITH MOVE to identify a valid location for the file.

Problems were identified while planning for the RESTORE statement. Previous messages provide details. RESTORE DATABASE is terminating abnormally. Processed 816 pages for database 'SomeDatabase', file 'some_database' on file 1. Processed 2 pages for database 'SomeDatabase', file 'some_database_log' on file 1. BACKUP DATABASE successfully processed 818 pages in 0.431 seconds (14.827 MB/sec). Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. Cannot shrink file '2' in database 'SomeDatabase' to 1280 pages as it only contains 352 pages. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

My guess is that the error cited 13(The data is invalid.) is a bit of a red herring. As a sanity check, we tried restoring the back-up (still on blob) to the SQL server's local disk, and that worked fine (as does RESTORE VERIFYONLY FROM URL = 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/SomeDatabase.bak') - so there's nothing wrong with the .bak file itself, and no issues reading it off of blob, just something suddenly going wrong with SSMS putting the .mdf/.ldf on blob.

I'm hoping someone else who has taken advantage of this feature is aware of some nuance of Gen2 blob storage that may need tweaking for the scripts to continue working.

Some details that might be salient:

• Our SSMS / SQL Server is running on an Azure VM
• We're using SAS tokens in both cases - with all the same access rights granted.
• Neither storage account has any kind of firewall of virtual network settings configured. Microsoft network routing is being used.
• On both storage accounts: Blob public access is disabled, Secure transfer required enabled, Allow shared key access Enabled, NFS v3 is disabled.
• As far as I can tell, both storage accounts are configured the same, save for the new one having "Azure Active Directory Domain Services (Azure AD DS) amd Hierarchical namespace enabled at creation time. That said, we haven't actually used this feature yet and are still using SAS tokens for access.

---

It's worth noting that I cannot even follow the most basic tutorial for creating a new database on blob storage. The same error occurs when the destination blob server is an Azure Data Lake Gen2 storage container:

CREATE DATABASE testdb   
ON     ( NAME = testdb_dat, FILENAME = 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storagee/TestData.mdf' )  
LOG ON ( NAME = testdb_log, FILENAME = 'https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/TestLog.ldf')  

Msg 5120, Level 16, State 156, Line 1 Unable to open the physical file "https://azeusanblbstranonimized.blob.core.windows.net/sql-storage/TestData.mdf". Operating system error 13: "13(The data is invalid.)". Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Answer 1

Got a reply from paid microsoft support on this.

Hello Alain,

We were able to complete the investigation and confirm we cannot use ADLS to store SQL Server datafiles because it uses Block Blob. We only use Page Blobs for storing data on Azure Storage due to requirement for Random writes.

We are working on updating the document to explicitly document the same about the supportability of ADLS Gen2.

Thank you once again for bring this to us and we hope to have this feature in future.

In short, Gen2's Blob storage has as a known issue that it does not (yet?) support the Blob REST APIs for PUT Page and GET Page Ranges. SQL uses these APIs behind the scenes, hence the feature breaks when pointed towards Gen2 containers.

---

This is unfortunately a detail not disclosed on the tutorial for placing databases on blob. In fact, the above article claims that both Page and Block blob can be used, with Block blob being preferred - but this has proven false. Even when following steps to use block blob, the page blob API is used behind-the-scenes, and so the above error is raised.

There is only one other tutorial I've found on Microsoft that alludes to the feature of storing SQL files on blob: SQL Server data files in Microsoft Azure

In this case, there's actually a hint in the documentation of where things might go south:

This new feature uses Page blobs, which are more efficient when ranges of bytes in a file are modified frequently.