Export (0) Print
Expand All

SQL Server Managed Backup to Windows Azure: Interoperability and Coexistence

SQL Server 2014
 

This topic describes SQL Server Managed Backup to Windows 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 that are configured as a Windows Azure–only solution supported for SQL Server Managed Backup to Windows 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 Windows Azure for Availability Groups

SQL Server Managed Backup to Windows Azure is supported only on the principal database. If both the principal and the mirror are configured to use SQL Server Managed Backup to Windows Azure, the mirrored database is skipped and will not be backed up. However, in the event of a failover, SQL Server Managed Backup to Windows 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 Windows Azure, in the event of a failover, no backups are taken. We recommend that you configure SQL Server Managed Backup to Windows Azure on both the principal and the mirror so backups continue in the event of a failover.

System_CAPS_tipTip

If you are creating a mirrored database on an instance with SQL Server Managed Backup to Windows Azure default settings, it may be preferable to disable SQL Server Managed Backup to Windows 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.

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

System_CAPS_tipTip

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

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

Ad hoc or one time backups created outside SQL Server Managed Backup to Windows Azure using Transact-SQL or SQL Server Management Studio may affect the SQL Server Managed Backup to Windows Azure process depending on the type of backup and the storage media used. Log backups to a different Windows Azure storage account than what SQL Server Managed Backup to Windows Azure is using, or any other destination than the Windows 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 Windows Azure enabled. You can initiate either a full database or log backup using this stored procedure.

If a database that has SQL Server Managed Backup to Windows 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.

  • 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 Windows Azure for the database. This will be considered like a new database from SQL Server Managed Backup to Windows 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 Windows 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.

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 Windows 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 Windows 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 Windows Azure, and is not supported.

If you have SQL Server Managed Backup to Windows 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 Windows Azure, you can use the Copy Only Backup. For more information, see Copy-Only Backups (SQL Server).

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2015 Microsoft