SQL Server Managed Backup to Azure
SQL Server Managed Backup to Microsoft Azure manages and automates SQL Server backups to the Azure Blob storage service. The backup strategy used by SQL Server Managed Backup to Microsoft Azure is based on the retention period and the transaction workload on the database. SQL Server Managed Backup to Microsoft Azure supports point in time restore for the retention time period specified.
SQL Server Managed Backup to Microsoft Azure can be enabled at the database level or at the instance level to manage all the databases on the instance of SQL Server. The SQL Server can be running on-premises or in hosted environments like the Azure virtual machine. SQL Server Managed Backup to Microsoft Azure is recommended for SQL Server running on Azure Virtual Machines.
Benefits of Automating SQL Server Backup Using SQL Server Managed Backup to Microsoft Azure
Currently automating backups for multiple databases requires developing a backup strategy, writing custom code, and scheduling backups. Using SQL Server Managed Backup to Microsoft Azure, you only are required provide the retention period settings and the storage location. SQL Server Managed Backup to Microsoft Azure schedules, performs and maintains the backups.
SQL Server Managed Backup to Microsoft Azure can be configured at the database level or at configured with default settings for an instance of SQL Server. Automating backup using SQL Server Managed Backup to Microsoft Azure has the following benefits:
By setting the defaults at the instance level, you can apply these settings to any database created thereafter, thus removing the risk of new databases not being backed up and data loss.
The option of enabling SQL Server Managed Backup to Microsoft Azure and setting the retention period at the database level, allows you to override the default settings set at the instance level. This allows you to have more granular control on the recoverability for a specific database.
With SQL Server Managed Backup to Microsoft Azure, you do not have to specify the type or frequency of the backups for a database. You specify the retention period, and SQL Server Managed Backup to Microsoft Azure determines the type and frequency of backups for a database stores the backups on Azure Blob storage service. For more details on the set of criteria that SQL Server Managed Backup to Microsoft Azure uses to create the backup strategy,, see the Components and Concepts section in this topic.
When configured to use encryption, you have additional security for the backup data. For more information, see Backup Encryption
For more details on the benefits of using Azure Blob storage for SQL Server backups, see SQL Server Backup and Restore with Azure Blob Storage Service
Terms and Definitions
SQL Server Managed Backup to Microsoft Azure
A SQL Server feature that automates database backup and maintains the backups based on the retention period.
Retention Period
The retention period is used by SQL Server Managed Backup to Microsoft Azure to determine what backup files should be retained in the storage in order to recover a database to a point in time within the time frame specified. The supported values are in the range of 1-30 days.
Log Chain
A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database.
Requirements, Concepts, and Components
Permissions
Transact-SQL is the main interface used to configure and monitor SQL Server Managed Backup to Microsoft Azure. In general, to run the configuration stored procedures, db_backupoperator database role with ALTER ANY CREDENTIAL permissions, and EXECUTE permissions on sp_delete_backuphistory stored procedure is required. Stored procedures and functions used to review information typically require Execute permissions on the stored procedure and Select on the function respectively.
Prerequisites
Prerequisites:
Azure Storage service is used by SQL Server Managed Backup to Microsoft Azure to store the backup files. The concepts, structure, and requirements for creating an Azure storage account is explained in detail in the Introduction to Key Components and Concepts section of the SQL Server Backup to URL topic.
SQL Credential is used to store the information required to authenticate to the Azure storage account. The SQL Credential object stores the account name and the access key information. For more information, see the Introduction to Key Components and Concepts section in the SQL Server Backup to URL topic. For a walkthrough on how to create a SQL Credential to store Azure Storage authentication information, see Lesson 2: Create a SQL Server Credential.
Concepts and Key Components
The SQL Server Managed Backup to Microsoft Azure is a feature that manages the backup operations. It stores the metadata in the msdb database and uses system jobs to write full database and transaction log backups.
Components
Transact-SQL is the main interface to interact with SQL Server Managed Backup to Microsoft Azure. System stored procedures are used for enabling, configuring, and monitoring SQL Server Managed Backup to Microsoft Azure. System functions are used to retrieve existing configuration settings, parameter values, and backup file information. Extended events are used to surface errors and warnings. Alert mechanisms are enabled through SQL Agent jobs and SQL Server Policy Based Management. The following is a list of the objects and a description of its functionality in relation to SQL Server Managed Backup to Microsoft Azure.
PowerShell cmdlets are also available to configure SQL Server Managed Backup to Microsoft Azure. SQL Server Management Studio supports restoring backups created by SQL Server Managed Backup to Microsoft Azure by using the Restore Database task
| System Object | Description |
| MSDB | Stores the metadata, backup history for all the backups created by SQL Server Managed Backup to Microsoft Azure. |
| smart_admin.set_db_backup (Transact-SQL) | System stored procedure for enabling and configuring SQL Server Managed Backup to Microsoft Azure for a database. |
| smart_admin.set_instance_backup (Transact-SQL) | System stored procedure for enabling and configuring default settings SQL Server Managed Backup to Microsoft Azure for the SQL Server instance. |
| smart_admin.sp_ backup_master_switch (Transact-SQL) | System stored procedure to pause and resume SQL Server Managed Backup to Microsoft Azure. |
| smart_admin.sp_set_parameter (Transact-SQL) | System stored procedure to enable and configure monitoring for SQL Server Managed Backup to Microsoft Azure. Examples: enabling extended events, mail settings for notifications. |
| smart_admin.sp_backup_on_demand (Transact-SQL) | System stored procedure that is used to perform an ad-hoc backup for a database that is enabled to use SQL Server Managed Backup to Microsoft Azure without breaking the log chain. |
| smart_admin.fn_backup_db_config (Transact-SQL) | System function that returns the current SQL Server Managed Backup to Microsoft Azure status and configuration values for a database, or for all the databases on the instance. |
| smart_admin.fn_is_master_switch_on (Transact-SQL) | System function that returns the status of the master switch. |
| smart_admin.sp_get_backup_diagnostics (Transact-SQL) | System stored procedure used to return the events logged by Extended Events. |
| smart_admin.fn_get_parameter (Transact-SQL) | System function that returns the current values for backup system settings such as monitoring and mail settings for alerts. |
| smart_admin.fn_available_backups (Transact-SQL) | Stored Procedure used to retrieve available backups for a specified database or for all the databases in an instance. |
| smart_admin.fn_get_current_xevent_settings (Transact-SQL) | System function that returns the current extended event settings. |
| smart_admin.fn_get_health_status (Transact-SQL) | System function that returns the aggregated counts of errors logged by Extended Events for a specified period. |
| Monitor SQL Server Managed Backup to Azure | Extended Events for monitoring, email notification of errors and warnings, SQL Server Policy Based Management for SQL Server Managed Backup to Microsoft Azure . |
Backup Strategy
Backup Strategy used by SQL Server Managed Backup to Microsoft Azure:
The type of backups scheduled and the backup frequency is determined based on the workload of the database. The retention period settings are used to determine the length of time a backup file should be retained in the storage and the ability to recover the database to a point-in-time within the retention period.
Backup Container and File Naming Conventions:
SQL Server Managed Backup to Microsoft Azure names the Azure storage container using the SQL Server Instance Name for all databases except availability databases. For availability databases, the Availability Group GUID is used to name the Azure storage container.
The backup file for non availability databases are named using the following convention: The name is created using the first 40 characters of the database name, the database GUID without the '-', and the timestamp. The underscore character is inserted between segments as separators. The .bak file extension is used for full backup and .log for log backups. For Avaialbility Group databases, in addition to the file naming convention described above, the Availability Group database GUID is added after the 40 characters of the database name. The Availability Group database GUID value is the value for group_database_id in sys.databases.
Full Database Backup: SQL Server Managed Backup to Microsoft Azure agent schedules a full database backup if any of the following is true.
A database is SQL Server Managed Backup to Microsoft Azure enabled for the first time, or when SQL Server Managed Backup to Microsoft Azure is enabled with default settings at the instance level.
The log growth since last full database backup is equal to or larger than 1 GB.
The maximum time interval of one week has passed since the last full database backup.
The log chain is broken. SQL Server Managed Backup to Microsoft Azure periodically checks to see whether the log chain is intact by comparing the first and last LSNs of the backup files. If there is break in the log chain for any reason, SQL Server Managed Backup to Microsoft Azure schedules a full database backup. The most common reason for log chain breaks is probably a backup command issued using Transact-SQL or through the Backup task in SQL Server Management Studio. Other common scenarios include accidental deletion of the backup log files, or accidental overwrites of backups.
Transaction Log Backup: SQL Server Managed Backup to Microsoft Azure schedules a log backup if any of the following is true:
There is no log backup history that can be found. This is usually true when SQL Server Managed Backup to Microsoft Azure is enabled for the first time.
The transaction log space used is 5 MB or larger.
The maximum time interval of 2 hours since the last log backup is reached.
Any time the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.
Retention Period Settings
When enabling backup you must set the retention period in days: The minimum is 1 day, and maximum is 30 days.
SQL Server Managed Backup to Microsoft Azure based on the retention period settings, assesses the ability to recover to a point in time in the specified time, to determine what backup files to keep and identifying the backup files to delete. The backup_finish_date of the backup is used to determine and match the time specified by the retention period settings.
Important Considerations
There are some considerations that are important to understand their impact on SQL Server Managed Backup to Microsoft Azure operations. They are listed below:
For a database, if there is an existing full database backup job running, then SQL Server Managed Backup to Microsoft Azure waits for the current job to be completed before doing another full database backup for the same database. Similarly, only one transaction log backup can be running at a given time. However, a full database backup and a transaction log backup can run concurrently. Failures are logged as Extended Events.
If more than 10 concurrent full database backups are scheduled, a warning is issued through the debug channel of Extended Events. SQL Server Managed Backup to Microsoft Azure then maintains a priority queue for the remaining databases that require a backup until the all backups are scheduled and completed.
Support Limitations
The following are some limitations specific to SQL Server 2014:
SQL Server Managed Backup to Microsoft Azure agent supports database backups only: Full and Log Backups. File backup automation is not supported.
SQL Server Managed Backup to Microsoft Azure operations are currently supported using Transact-SQL. Monitoring and troubleshooting can be done by using Extended Events. PowerShell and SMO support is limited to configuring storage and retention period default settings for an instance of SQL Server, and monitoring the backup status and overall health based on SQL Server Policy Based Management policies.
System Databases are not supported.
Azure Blob Storage service is the only supported backup storage option. Backups to disk or tape are not supported.
Currently, the maximum file size allowed for a Page Blob in Azure Storage is 1 TB. Backup files larger than 1 TB will fail. In order to avoid this situation, we recommend that for large databases, use compression and test the backup file size prior to setting up SQL Server Managed Backup to Microsoft Azure. You can either test by backing up to a local disk or manually backing up to Azure storage using
BACKUP TO URLTransact-SQL statement. For more information, see SQL Server Backup to URL.Recovery Models: Only databases set to Full or Bulk-logged model are supported. Databases set to simple recovery model are not supported.
SQL Server Managed Backup to Microsoft Azure may have some limitations when it is configured with other technologies supporting backup, high availability, or disaster recovery. For more information, see SQL Server Managed Backup to Azure: Interoperability and Coexistence.
Related Tasks
| Task descriptions | Topic |
| Basic tasks like configuring SQL Server Managed Backup to Microsoft Azure for a database, or configuring default settings at the instance level, disabling SQL Server Managed Backup to Microsoft Azure at instance or database level, pausing and restarting SQL Server Managed Backup to Microsoft Azure. | SQL Server Managed Backup to Azure - Retention and Storage Settings |
| Tutorial: Step by Step instructions to configuring and monitoring SQL Server Managed Backup to Microsoft Azure. | Setting up SQL Server Managed Backup to Azure |
| Tutorial: Step by Step instructions to configuring and monitoring SQL Server Managed Backup to Microsoft Azure for databases in Availability Group. | Setting up SQL Server Managed Backup to Azure for Availability Groups |
| Tools and Concepts and tasks related to monitoring SQL Server Managed Backup to Microsoft Azure . | Monitor SQL Server Managed Backup to Azure |
| Tools and steps to troubleshooting SQL Server Managed Backup to Microsoft Azure. | Troubleshooting SQL Server Managed Backup to Azure |
See Also
SQL Server Backup and Restore with Azure Blob Storage Service
SQL Server Backup to URL
SQL Server Managed Backup to Azure: Interoperability and Coexistence
Troubleshooting SQL Server Managed Backup to Azure