Enable SQL Server Managed Backup to Microsoft Azure
Published: October 3, 2016
Updated: October 3, 2016
Applies To: SQL Server 2016
This topic describes how to enable SQL Server Managed Backup to Microsoft Azure with default settings at both the database and instance level. It also describes how to enable email notifications and how to monitor backup activity.
This tutorial uses Azure PowerShell. Before starting the tutorial, download and install Azure PowerShell.
Create an Azure storage account: If you already have a storage account, go to the next step. Otherwise, you can use the Azure Management Portal or Azure PowerShell to create the storage account. The following
New-AzureStorageAccountcommand creates a storage account named
managedbackupstoragein the East US region.
New-AzureStorageAccount -StorageAccountName "managedbackupstorage" -Location "EAST US"
For more information about storage accounts, see About Azure Storage Accounts.
Create a blob container for the backup files: You can create a blob container in the Azure Management Portal or with Azure PowerShell. The following
New-AzureStorageContainercommand creates a blob container named
Generate a Shared Access Signature (SAS): To access the container, you must create a SAS. This can be done in some tools, code, and Azure PowerShell. The following
New-AzureStorageContainerSASTokencommand creates SAS token for the
backupcontainerblob container that expires in one year.
$context = New-AzureStorageContext -StorageAccountName managedbackupstorage -StorageAccountKey (Get-AzureStorageKey -StorageAccountName managedbackupstorage).Primary New-AzureStorageContainerSASToken -Name backupcontainer -Permission rwdl -ExpiryTime (Get-Date).AddYears(1) -FullUri -Context $context
The output for this command will contain both the URL to the container and the SAS token. The following is an example:
In the previous example, separate the container URL from the SAS token at the question mark (do not include the question mark. For example, the previous output would result in the following two values.
Container URL: https://managedbackupstorage.blob.core.windows.net/backupcontainer SAS token: sv=2014-02-14&sr=c&sig=xM2LXVo1Erqp7LxQ%9BxqK9QC6%5Qabcd%9LKjHGnnmQWEsDf%5Q%se=2015-05-14T14%3B93%4V20X&sp=rwdl
Record the container URL and SAS for use in creating a SQL CREDENTIAL. For more information about SAS, see Shared Access Signatures, Part 1: Understanding the SAS Model.
Create a SQL Credential for the SAS URL: Use the SAS token to create a SQL Credential for the blob container URL. In SQL Server Management Studio, use the following Transact-SQL query to create the credential for your blob container URL based on the following example:
Ensure SQL Server Agent service is Started and Running: Start SQL Server Agent if it is not currently running. SQL Server Managed Backup to Microsoft Azure requires SQL Server Agent to be running on the instance to perform backup operations. You may want to set SQL Server Agent to run automatically to make sure that backup operations can occur regularly.
Determine the retention period: Determine the retention period for the backup files. The retention period is specified in days and can range from 1 to 30.
Enable and configure SQL Server Managed Backup to Microsoft Azure : Start SQL Server Management Studio and connect to the target SQL Server instance. From the query window run the following statement after you modify the values for the database name, container url, and retention period per your requirements:
To enable managed backup at the instance level, specify
Use msdb; GO EXEC msdb.managed_backup.sp_backup_config_basic @enable_backup = 1, @database_name = 'yourdatabasename', @container_url = 'https://managedbackupstorage.blob.core.windows.net/backupcontainer', @retention_days = 30 GO
SQL Server Managed Backup to Microsoft Azure is now enabled on the database you specified. It may take up to 15 minutes for the backup operations on the database to start to run.
Review Extended Event Default Configuration: Review the Extended Event settings by running the following transact-SQL statement.
SELECT * FROM msdb.managed_backup.fn_get_current_xevent_settings()
You should see that Admin, Operational, and Analytical channel events are enabled by default and cannot be disabled. This should be sufficient to monitor the events that require manual intervention. You can enable debug events, but the debug channels include informational and debug events that SQL Server Managed Backup to Microsoft Azure uses to detect issues and solve them.
Enable and Configure Notification for Health Status: SQL Server Managed Backup to Microsoft Azure has a stored procedure that creates an agent job to send out e-mail notifications of errors or warnings that may require attention. The following steps describe the process to enable and configure e-mail notifications:
Setup Database Mail if it is not already enabled on the instance. For more information, see Configure Database Mail.
Configure SQL Server Agent Notification to use Database Mail. For more information, see Configure SQL Server Agent Mail to Use Database Mail.
Enable e-mail notifications to receive backup errors and warnings: From the query window, run the following Transact-SQL statements:
EXEC msdb.managed_backup.sp_set_parameter @parameter_name = 'SSMBackup2WANotificationEmailIds', @parameter_value = '<email1;email2>'
View backup files in the Microsoft Azure Storage Account: Connect to the storage account from SQL Server Management Studio or the Azure Management Portal. You will see any backup files in the container you specified. Note that you might see a database and a log backup within 5 minutes of enabling SQL Server Managed Backup to Microsoft Azure for the database.
Monitor the Health Status: You can monitor through e-mail notifications you configured previously, or actively monitor the events logged. The following are some example Transact-SQL Statements used to view the events:
-- view all admin events Use msdb; Go DECLARE @startofweek datetime DECLARE @endofweek datetime SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) SET @endofweek = DATEADD(Day, 7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) DECLARE @eventresult TABLE (event_type nvarchar(512), event nvarchar (512), timestamp datetime ) INSERT INTO @eventresult EXEC managed_backup.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek SELECT * from @eventresult WHERE event_type LIKE '%admin%'
-- to enable debug events Use msdb; Go EXEC managed_backup.sp_set_parameter 'FileRetentionDebugXevent', 'True'
-- View all events in the current week Use msdb; Go DECLARE @startofweek datetime DECLARE @endofweek datetime SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) SET @endofweek = DATEADD(Day, 7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) EXEC managed_backup.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek;
The steps described in this section are specifically for configuring SQL Server Managed Backup to Microsoft Azure for the first time on the database. You can modify the existing configurations using the same system stored procedures and provide the new values.