Has anyone setup Brent Ozar's sp_AllNightLog using Azure Blob Storage? Trying to start incorporating both but not sure of the steps or how to get started. Any tips or links to similar operations?


Brent Ozar's sp_AllNightLog uses Ola Hallengren's backup script.

Inside each of those Agent jobs, they call sp_AllNightLog @Backup = 1, which loops through the table to find databases that need to be backed up, then call Ola Hallengren’s DatabaseBackup stored procedure.

So you just need to configure Ola's script to write to Azure Blob Storage.

Striping of backups to Azure Blob Storage

The SQLCAT - team has a blog post about how you can optimize performance when backing up to Azure Blob Storage. You can stripe the backups to multiple files, and use the options MAXTRANSFERSIZE, and BLOCKSIZE.

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@URL = 'https://myaccount.blob.core.windows.net/mycontainer',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@NumberOfFiles = 8,
@MaxTransferSize = 4194304,
@BlockSize = 65536

This is the important part:

@URL = 'https://myaccount.blob.core.windows.net/mycontainer'

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