Compartilhar via

Native database backup in Azure SQL Managed Instance

  • Artigo

Azure SQL Managed Instance is a new fully managed PaaS offering in Azure cloud that will be publicly available in the near future. It will expose entire SQL Server Instance, support almost all features available in SQL Server 2017 and combine this with instant scaling and intelligence that is available in Azure PaaS service. Managed Instance will support native BACKUP like SQL Server. In the beginning, only backup to URL/Azure Blob Storage will be supported. In this post you will see how to backup database to Azure Blob Storage using Managed Instance.

Access to Azure Blob Storage

First, you would need to store credentials that would be used to access Azure Blob Storage:

 CREATE CREDENTIAL [https://myacc.blob.core.windows.net/testcontainer] 
 WITH IDENTITY='SHARED ACCESS SIGNATURE'
 , SECRET = 'sv=2014-02-14&sr=c&sig=GV0T9y%2B9%2F9%2FLEIipmuVee3jvYj4WpvBblo%3D&se=2019-06-01T00%2A%3AZ&sp=rwdl';

The name of the credential should be the same as URL of target Azure Blob Storage container. In this case, this is myacc account and testcontainer container stored on the URL: https://myacc.blob.core.windows.net/testcontainer

COPY_ONLY Backup

Once you create a credential, you can backup any database using standard BACKUP T-SQL command:

 BACKUP DATABASE tpcc2501
 TO URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501.bak'
 WITH COPY_ONLY

The only mandatory property is COPY_ONLY. Azure SQL Managed Instance owns the backup chain. This means that you cannot perform database backup that breaks the backup chain. Azure SQL Managed Instance allows you to perform only COPY_ONLY backups.

Stripping backup

Azure Blob Storage block blobs have 200GB size limitation. If your database is bigger than 200GB, you should use stripped backup by providing several URL locations where parts of the backup will be placed:

 BACKUP DATABASE tpcc2501
 TO URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-1.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-2.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-3.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-4.bak'
 WITH COPY_ONLY

This way, every stripe will contain a part of the backup and you will not reach the 200GB limit. You can divide the size of your database with 200GB to find out how many stripes you would need.

MAXTRANSFERSIZE

If you have a big database, it would be good to specify MAXTRANSFERSIZE=4194304 option:

 
BACKUP DATABASE tpcc2501
 TO URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-1.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-2.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-3.bak',
 URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-4.bak'
 WITH COPY_ONLY, MAXTRANSFERSIZE = 4194304, COMPRESSION

Dimitri Furman explained why is this option important to backup very large databases.

Also, a good idea would be to use COMPRESSION option to decrease bandwidth between Managed Instance and Azure Blob storage.

You also can specify additional options in the WITH clause such as CHECKSUM or STATS = <number>. Backups can be performed across Azure regions, but it would be good to check are the database and storage account placed within the same region to speedup the backup.