SQL Server Managed Backup to Azure: Interoperability and Coexistence

  • Article

This topic describes SQL Server Managed Backup to Microsoft Azure interoperability and coexistence with several features in SQL Server 2014. These features include the following: AlwaysOn Availability Groups, Database Mirroring, Backup Maintenance Plans, Log Shipping, Ad hoc backups, Detach Database, and Drop Database.

AlwaysOn Availability Groups

AlwaysOn Availability Groups that are configured as an Azure-only solution supported for SQL Server Managed Backup to Microsoft Azure. On-premises only, or Hybrid AlwaysOn Availability Group configurations are not supported. For more information and other considerations, see Setting up SQL Server Managed Backup to Azure for Availability Groups

Database Mirroring

SQL Server Managed Backup to Microsoft Azure is supported only on the principal database. If both the principal and the mirror are configured to use SQL Server Managed Backup to Microsoft Azure, the mirrored database is skipped and will not be backed up. However, in the event of a failover, SQL Server Managed Backup to Microsoft Azure will start the backup process after the mirror has completed role switching and is online. The backups will be stored in a new container in this case. If the mirror is not configured to use SQL Server Managed Backup to Microsoft Azure, in the event of a failover, no backups are taken. We recommend that you configure SQL Server Managed Backup to Microsoft Azure on both the principal and the mirror so backups continue in the event of a failover.

Tip

If you are creating a mirrored database on an instance with SQL Server Managed Backup to Microsoft Azure default settings, it may be preferable to disable SQL Server Managed Backup to Microsoft Azure instance defaults, so they are not applied to the mirrored database, and then re-enable the instance defaults after configuring the Principal and the Mirror.

Maintenance Plan

Using Maintenance Plans for creating backups for a database when SQL Server Managed Backup to Microsoft Azure is enabled is not supported. Maintenance plans will cause broken log chain and SQL Server Managed Backup to Microsoft Azure may not be able to support a guaranteed recoverability of the database during restore. This also applies when SQL Server Managed Backup to Microsoft Azure is enabled at the instance level.

Tip

Maintenance Plans with Copy Only backups is supported with SQL Server Managed Backup to Microsoft Azure configured for the same database or instance.

Log Shipping

You cannot configure Log Shipping and SQL Server Managed Backup to Microsoft Azure for the same database at the same time. Doing so will affect the recoverability of the database using either functionality.

Ad Hoc Backups Using Transact-SQL and SQL Server Management Studio

Ad hoc or one time backups created outside SQL Server Managed Backup to Microsoft Azure using Transact-SQL or SQL Server Management Studio may affect the SQL Server Managed Backup to Microsoft Azure process depending on the type of backup and the storage media used. Log backups to a different Azure storage account than what SQL Server Managed Backup to Microsoft Azure is using, or any other destination than the Azure Blob storage service, will cause a log chain break. We recommend that you use the smart_admin.sp_backup_on_demand (Transact-SQL) stored procedure to initiate a backup on databases that have SQL Server Managed Backup to Microsoft Azure enabled. You can initiate either a full database or log backup using this stored procedure.

Drop Database and Detach Database

If a database that has SQL Server Managed Backup to Microsoft Azure enabled is detached or dropped, although no additional backups are possible, the previous backups remain in the storage until the retention period has elapsed, at which point the backups will be purged.

Changes to Recovery Model

  • If you change the recovery model of a database from Simple to Full or Bulk-Logged, you have the option of configuring SQL Server Managed Backup to Microsoft Azure for the database. This will be considered like a new database from SQL Server Managed Backup to Microsoft Azure perspective.

  • If you change the recovery model of a database from Full or Bulk-Logged to Simple, that has SQL Server Managed Backup to Microsoft Azure enabled, backup operations will no longer be scheduled. The retention period setting will still be active and backup files will remain in the storage account until the retention period has elapsed. If you want to retain the backups, we recommend that you download the files either to a different storage account or to an on-premises location. The configuration settings are retained and can be reused if the recovery model is set back to Full or Bulk-Logged again.

Log Backups Using Other Backup Tools or Custom Scripts

Any two backups that are configured to perform log backups on the same database will cause break in the backup log chain. Although SQL Server Managed Backup to Microsoft Azure will attempt to remedy the break in the backup chain by scheduling full backups when a break in chain is detected, this means keeping up continuously with periodic breaks and log backups performed by two competing tools. This can also potentially affect the recoverability of the database since no one tool can be expected to have a full set of backup in sequence. Although this applies to any two features or tools performing log backups,, it is useful to call out specific examples as described below. This is also the basis for the issues with configuring maintenance plans or log shipping as described in earlier sections of this topic.

Data Protection Manager (DPM) based backups: Microsoft Data Protection Manager allows you to do full and incremental backups. The incremental backups are log backups that do perform a log truncation after creating a T-log backup. So configuring both DPM and SQL Server Managed Backup to Microsoft Azure for the same database is not supported.

Third Party Tools or Scripts: Any third party tool or scripts that perform log backups causing log truncation is incompatible with SQL Server Managed Backup to Microsoft Azure, and is not supported.

If you have SQL Server Managed Backup to Microsoft Azure enabled for a database instance, and you want to take an ad hoc backup you can either use the smart_admin.sp_backup_on_demand (Transact-SQL) stored procedure as described in the earlier section. If you also have a need to schedule or un backups periodically outside of SQL Server Managed Backup to Microsoft Azure, you can use the Copy Only Backup. For more information, see Copy-Only Backups (SQL Server).