Sql-server – SQL Server 2016 SP1 Managed Backup to blob storage

backupsql serversql-server-2016

I'm trying to configured managed backup for SQL Server 2016 SP1 to Azure's new cool blob storage and am only receiving errors. This is for a VM running in Azure. The documentation I've found is conflicting, but here's what I understand so far:

  • Cool blob storage only supports block blobs, not page blobs (reference)
  • "SQL Server Managed Backup to Microsoft Azure uses the Backup to Block Blob feature" (reference)
  • "Only a SAS URL is supported" by the managed_backup.sp_backup_config_basic procedure (reference)

With that in mind, I created a new cool blob storage account and a container called sqlbackups. I then created a shared access signature for the container with full permissions.

With this data, I then attempt to set the default backup configuration for the server:

EXEC msdb.managed_backup.sp_backup_config_basic
@enable_backup=1,
@container_url='https://mycoolblobstorage.blob.core.windows.net/sqlbackups?st=...',
@retention_days=30

Unfortunately, this just results in an error of:

SQL Server Managed Backup to Microsoft Azure cannot configure the default backup settings for the SQLServer instance because the container URL was invalid. It is also possible that your SAS credential is invalid.

I've regenerated the SAS token several times without success. I've also tried creating a credential, which I think is needed only for the legacy page blobs method.

CREATE CREDENTIAL
[https://mycoolblobstorage.blob.core.windows.net/sqlbackups]
WITH IDENTITY = 'Shared Access Signature',
SECRET = '?st=...'

That seems to have no effect. Can someone help me get this working?

Best Answer

While Microsoft corrects this bug in SQL 2016 a workaround is the following:

  • Create a credential to your azure storage but use an Access Key as password instead of a SAS (this was the procedure for SQL 2014).
  • Enable the Managed Backup (it will work correctly)

At this point MB is enabled but backups created by it (and any manual backup using the same storage) will fail because of the credential being created in 2014 mode, so

  • Modify the credential changing the Password to the SAS.

Now backups will start to run. If you need to disable or configure MB again you will have to switch to Access Key and then back again to SAS.

Related Question