Monday, 9 October 2017

Azure Azure SQL Database Long-Term Backup Retention Long-Term Backup Retention

Azure SQL Database provides a number of benefits that leverage resiliency and redundancy built into the underlying cloud infrastructure. You can take advantage of this functionality in order to perform backup, restore, and failover tasks, which help you recover from human errors, service outages, or even regional disasters. We have already described some of the corresponding features on this forum, including Point-in-Time Restore as well as Geo-Restore and Active Geo-Replication. Each one of them is an integral part of the Azure SQL Database service offering, regardless of the pricing tier. However, it is important to note that automatic backups created by Point-in-Time Restore, while convenient, have a relatively limited retention period (up to 35 days, with the Premium pricing tier). While this typically suffices to accommodate scenarios that involve restores due to an accidental data loss, it is not sufficient to satisfy archival and compliance requirements of an overwhelming majority of customers. To remediate this shortcoming, you have the option of implementing long-term backup retention of Azure SQL Database. Note that this functionality is in public preview at the time of authoring of this article.

Long-term backup retention of Azure SQL Database takes advantage of the capabilities offered by the Azure Recovery Services vault, which provides vast amounts of persistent (and optionally geo-replicated) storage at low cost (incidentally, also used by Azure Backup and Azure Site Recovery services). The vault used to store backups must reside in the same resource group, the same region and the same subscription as the SQL Server instance hosting the databases you intend to protect. Note however, that you have the ability to restore backups to any SQL Server instance in any Azure region in the same subscription. A single vault can service up to 1000 databases, which considering the limit of 25 vaults per subscription, gives you the ability to provide long-term backup retention for up to 25,000 databases in a single subscription. Keep in mind that there is also a limit (currently set to 200) on the number of databases you can assign to the same vault within a 24-hour period.

Azure SQL Database

Just as with Point-in-Time Restore, frequency and exact schedule of backups are not configurable, but instead managed by the underlying platform, which attempts to minimize performance impact across multiple SQL Databases. Unlike with Point-in-Time Restore, the vault stores only weekly, full database backups. For each database, you can designate its own, individual retention period of up-to 10 years.

Long-term backup retention is available in all pricing tiers and is supported regardless of whether a database is part of an elastic pool. Its cost reflects the pricing model of the Azure Recovery Services vault offering.

As we mentioned earlier, to implement long-term backup retention, you need to create an Azure Recovery Services vault in the same Azure subscription, the same Azure region, and the same resource group as the SQL Server instance hosting the database you intend to protect. The resource group requirement is somewhat surprising but hopefully this restriction will be eliminated once the offering reaches general availability. In the meantime, if you have an existing vault that you intend to use or if you have SQL Server instances in multiple resource groups, you have the option of moving individual SQL Server instances along with their databases to the resource group containing the vault (note that it is currently not supported to move vaults across resource groups or subscriptions).

To create an Azure Site Recovery vault in the Azure portal, click + New in the upper left corner of the portal. On the New blade, in the Search the Marketplace text box, type Backup and Site Recovery (OMS), and in the list of results, click Backup and Site Recovery (OMS). On the Backup and Site Recovery (OMS) blade, click Create. On the Recovery Services vault blade, specify the vault name (the name can include letters, digits, and hyphens), provide the name of the resource group and the Azure region where the vault will reside (matching the resource group and the Azure region hosting the SQL Server instance).

Next, you need to register that SQL Server instance with the vault. Since, as we mentioned earlier, at the time of authoring this article, Long-term backup retention is in preview, you first need to accept the preview terms. In the Azure portal, navigate to the SQL Server instance blade, click Long-term backup retention in the vertical menu on its left hand side, and click the label Preview terms: Not Accepted. To sign up for preview, click here. On the Preview terms blade, select the checkbox that confirms that you are acknowledging the preview terms and click OK. Back on the Long-term backup retention of the SQL Server blade, select one or more databases for which you intend to provide long-term backup retention and click Configure. On the Configure blade, click Recovery services vault and select the vault you created in the previous step. Provide a name of a new retention policy, specify the retention period (between 1 week and 10 years) and click OK. Back on the Long-term backup retention, click Save to save your changes and click OK to confirm.

Note that the registration applies to all Azure SQL databases on the same Azure SQL Server instance. In other words, currently it is not possible to back up different databases hosted on the same logical server to separate vaults. In addition, Azure SQL database backups are not exposed in the vault interface in the Azure portal. The only indication that this feature has been implemented from the vault's perspective is the Cloud - GRS entry in the Backup Usage tile on the Overview page of the vault's blade. This gives you an estimated Geo-Redundant Storage usage (assuming, of course, that you are not using the same vault to protect other services, for example, Azure VMs). Keep in mind that it might take up to a week before the first full backup gets stored in the vault and the usage data appears in its Azure portal interface.