This guide describes how to migrate from locally made SQL Server database backups to using Azure Recovery Vault - this is mainly relevant for SQL Server’s hosted in Azure, but can also be used for on-premise database servers.
Typically organizations use automated tools to configure and deploy servers, so manual processes are likely not required.
Benefits of Azure Recovery Services Vault
- Disaster recovery: Azure Recovery Services Vault provides a secure and reliable offsite backup for your SQL Server databases, protecting them from natural disasters, hardware failures, and other catastrophic events.
- Point-in-time recovery: Azure Recovery Vault allows you to restore your SQL Server databases to a specific point in time, minimizing data loss in the event of a data corruption or accidental deletion.
- Scalability: Azure Recovery Vault is highly scalable, allowing you to back up and recover large SQL Server databases with ease.
- Security: Azure Recovery Vault uses industry-standard encryption to protect your backed-up data from unauthorized access.
- Cost-effectiveness: Azure Recovery Vault is a cost-effective solution for SQL Server backup and recovery, with pricing based on the amount of data stored.
SQL Server Backup Chains
It's important to understand the types of backups you can perform on a database. SQL Server backup chains are a series of interrelated backups that allow you to recover your database to a specific point in time. There are three main types of backups in a backup chain:
- Full backup: A full backup is a complete copy of your database at a specific point in time.
- Differential backup: A differential backup contains all the changes that have been made to your database since the last full backup.
- Log backup: A log backup contains all the transaction log entries that have been generated since the last transaction log backup (incremental)
Backup chains are important because they allow you to recover your database to any point in time within the chain. For example, if you accidentally delete some data, you can restore your database to the point in time just before the deletion occurred.
A Full backup resets the start of the chain, so care needs to be taken to avoid breaking the chain with ad-hoc backups, if these need to be done, you can use a COPY ONLY backup to make a one off copy.
Azure Recovery Services Vault
A Recovery Services vault (not to be confused with a key vault) is an Azure resource which will take regular backups of Azure and on-premise data sources - both databases and virtual machines.
A backup policy is configured, indicating how frequently backups should be performed and what type - full, differential and logs. Compression is also available if you are using a suitable edition of SQL Server.
Multiple policies can be created, perhaps one for production and one for non-production (development) purposes with different retention periods.
Recovery Vault Restrictions
Azure Recovery Services Vault has certain restrictions on the deletion of data and immutability to ensure the integrity and security of your backups. These restrictions include:
Immutability:
- Backups in Azure Recovery Services Vault can be configured with immutability protection. This means that the backups cannot be deleted or modified for a specified period of time.
- Immutability protection is useful for compliance purposes or to prevent accidental deletion of backups.
- Backups with immutability protection can only be deleted after the immutability period has expired.
Deletion of Backups:
- Backups cannot be deleted individually. You must delete the entire recovery point.
- Recovery points can only be deleted after the retention period has expired. The retention period is the length of time that backups are kept in the vault.
- If you delete a recovery point, all backups within that recovery point will also be deleted.
Other Restrictions:
- You cannot modify the backup policy after it has been created.
- You cannot restore a database to a point in time that is older than the oldest recovery point in the vault.
- You cannot restore a database to a different Azure subscription or region.
Configuring Backups
Backups are configured directly from the Azure portal by accessing the “SQL virtual machine” resource and the backup tab.
You can enable backups using an “auto protect” feature, which will automatically include all the databases on the server, including new ones which are created after the backup is configured.
The SQL Server will have a new Azure agent installed to handle the backup traffic, and no local disk space is required for backup purposes - the data is “streamed” directly to the vault for retention.
Typically organizations will use a standard policy template generated automatically by Terraform or similar.
Database Restore
You can restore databases directly from the Recovery Services Vault, select “SQL Database in Azure VM” as an option.
You can select Restore next to the database you are interested in.
You can restore to a different server, the original location, as a new database (different name) or even just to get the “.BAK” files on the server.
You can also use this option to make restores to an alternative database server.
Use the Restore Point to select the exact date/time to restore up to. If you are using logshipping, this could be up to 1 second granularity.
Limitations
Whilst you can use Recovery Services vault to perform a one-off backup at an ad hoc time, you cannot give that backup a useful name or description. Therefore if you require backups for dev/test purposes or one-off snapshots, these are still performed directly off the server using SQL Server Management Studio.
If you require a one-off backup, you must ensure that you don’t break the existing backup chain created by Recovery Services Vault - do this by using the TSQL command…
BACKUP DATABASE [Sales] TO DISK = 'E:\Backups\Sales_Copy.bak' WITH COPY_ONLY;
These backups are local to the SQL Server and won’t appear in the Azure Portal, they only reside on the servers local disks.