We've been using the defacto standard Ola Hallengrin maintenance solution for many years, even after we moved to Azure and started using blobs for backup targets. However, for performance reasons, I've been looking into backing up via block blob rather than page blob, leveraging a storage container with a shared access signature rather than a credential, and using 4-8 URL paths at once for backups and restores. Outcomes are crystal clear – 30MB/s restore performance on uncompressed backups, 70MB/s restore performance on compressed backups, each with 1x URL. With 4x URL, MAXTRANSFERSIZE = 4194304, & BLOCKSIZE = 65536, .. 131MB/s. With 8x URL, 245MB/s. Similar story for backup as well.
I'm writing to see if anyone knows of a way to continue leveraging Ola's package. It seems the URL support structure in the package works for Credential based page blobs but doesn't have any logic for SAS & striping URLs. I would've never thought this made a difference, but it was night and day.
I started from this document and worked my way to success:
https://blogs.msdn.microsoft.com/sqlcat/2017/03/10/backing-up-a-vldb-to-azure-blob-storage/
The test database below is roughly 85GB on disk, compresses to 44GB consistently.
BACKUP DATABASE [yeahokyeahok] TO
URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194161.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194161_2.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194161_3.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194161_4.bak'
--,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194161_5.bak'
-- URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194161_6.bak'
WITH NOFORMAT, NOINIT, NAME = N'yeahokyeahok-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--with 2x backup file; 174MB/s average ; 44GB (416s RT) DS14 no read cache, cool storage
--with 3x backup file; 208MB/s average ; 44GB (348s RT) DS14 no read cache, cool storage
--with 4x backup file; 236MB/s average ; 44GB (306s RT) DS14 no read cache, cool storage
--with 5x backup file; 208MB/s average ; 44GB (348s RT) DS14 no read cache, cool storage
--with 6x backup file; 227MB/s average ; 44GB (320s RT) DS14 no reac cache, cool storage
RESTORE DATABASE [yeahokyeahok] FROM
URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194157.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194157_2.bak'
--,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194159_3.bak'
--,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194159_4.bak'
--,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194159_5.bak'
--,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194159_6.bak'
WITH STATS = 10, REPLACE
--with 2x backup file; 47MB/s average ; 44GB (1519s RT) DS14 no cache, cool storage
--with 4x backup file; 83MB/s average ; 44GB ( 874s RT) DS14 no cache, cool storage
--with 6x backup file; 113MB/s average ; 44GB ( 642s RT) DS14 no cache, cool storage
BACKUP DATABASE [yeahokyeahok] TO
URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194162.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194162_2.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194162_3.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194162_4.bak'
--,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194161_5.bak'
-- URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194161_6.bak'
WITH NAME = N'yeahokyeahok-Full Database Backup', STATS = 10, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536
GO
--with 4x backup file; 236MB/s average ; 44GB ( 305 RT) DS14 no cache, cool storage, mtx & bs
RESTORE DATABASE [yeahokyeahok] FROM
URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194162.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194162_2.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194162_3.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194162_4.bak'
with STATS = 10, REPLACE, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536
--with 4x backup file; 131MB/s average ; 44GB ( 508 RT) DS14 no cache, cool storage, mtx & bs
BACKUP DATABASE [yeahokyeahok] TO
URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_2.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_3.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_4.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_5.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_6.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_7.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_8.bak'
WITH NAME = N'yeahokyeahok-Full Database Backup', STATS = 10, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536
GO
--with 8x backup file; 228MB/s average ; 44GB ( 316 RT) DS14 no cache, cool storage, mtx & bs
RESTORE DATABASE [yeahokyeahok] FROM
URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_2.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_3.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_4.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_5.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_6.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_7.bak'
,URL = N'https://az1yeahok01.blob.core.windows.net/adamtest01/yeahokyeahok_backup_2018_03_08_194165_8.bak'
with STATS = 10, REPLACE, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536
--with 8x backup file; 245MB/s average ; 44GB ( 295s RT) DS14 no cache, cool storage
Best Answer
I have added support for striping of backups to Azure Blob Storage. https://ola.hallengren.com/versions.html
Here is how it works: