Setting up SQL Server Managed Backup to Azure

Article
08/04/2016

This topic includes two tutorials:

Set up SQL Server Managed Backup to Microsoft Azure at the database level, enable email notification, and monitor backup activity.

Setting up SQL Server Managed Backup to Microsoft Azure at the instance level, enable email notification, and monitor backup activity.

For a tutorial on setting up SQL Server Managed Backup to Microsoft Azure for Availability Groups, see Setting up SQL Server Managed Backup to Microsoft Azure for Availability Groups.

Setting Up SQL Server Managed Backup to Microsoft Azure

Enable and Configure SQL Server Managed Backup to Microsoft Azure for a Database

This tutorial describes the steps necessary to enable and configure SQL Server Managed Backup to Microsoft Azure for a database (TestDB), followed by steps to enable monitoring SQL Server Managed Backup to Microsoft Azure health status.

Permissions:

Requires membership in db_backupoperator database role, with ALTER ANY CREDENTIAL permissions, and EXECUTE permissions on sp_delete_backuphistorystored procedure.
Requires SELECT permissions on the smart_admin.fn_get_current_xevent_settingsfunction.
Requires EXECUTE permissions on the smart_admin.sp_get_backup_diagnostics stored procedure. In addition, it requires VIEW SERVER STATE permissions as it internally calls other system objects that require this permission.
Requires EXECUTE permissions on the smart_admin.sp_set_instance_backup and smart_admin.sp_backup_master_switch stored procedures.

Create a Microsoft Azure storage account: The backups are stored in the Microsoft Azure storage service. You must first create a Microsoft Azure storage account, if you do not already have an account.
- SQL Server 2014 uses page blobs, which are different than block and append blobs. Therefore you must create a general purpose account, and not a blob account. For more information, see About Azure storage accounts.
- Make a note of the storage account name, and the access keys. The storage account name and access key information is used to create a SQL Credential. The SQL Credential is used to authenticate to the storage account.
Create a SQL Credential: Create a SQL Credential using the name of the storage account as the Identity and the storage access key as the password.
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 instance where the database is installed. From the query window run the following statement after you modify the values for the database name, SQL Credential, retention period, and encryption options per your requirements:

For more information on creating a certificate for encryption, see the Create a Backup Certificate step in Create an Encrypted Backup.
```
Use msdb;  
GO  
EXEC smart_admin.sp_set_db_backup   
                @database_name='TestDB'   
                ,@retention_days=30   
                ,@credential_name='MyCredential'  
                ,@encryption_algorithm ='AES_128'  
                ,@encryptor_type= 'Certificate'  
                ,@encryptor_name='MyBackupCert'  
                ,@enable_backup=1;  
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 smart_admin.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. For more information, see Monitor SQL Server Managed Backup to Microsoft Azure.
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:
1. Setup Database Mail if it is not already enabled on the instance. For more information, see Configure Database Mail.
2. Configure SQL Server Agent Notification to use Database Mail. For more information, see Configure SQL Server Agent Mail to Use Database Mail.
3. Enable e-mail notifications to receive backup errors and warnings: From the query window, run the following Transact-SQL statements:
```
EXEC msdb.smart_admin.sp_set_parameter  
@parameter_name = 'SSMBackup2WANotificationEmailIds',  
@parameter_value = '<email1;email2>'  
```
  For more information, and a full sample script see Monitor SQL Server Managed Backup to Microsoft Azure.
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 a container for the instance of SQL Server that hosts the database you configured to use SQL Server Managed Backup to Microsoft Azure. You may also see a database and a log backup within 15 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 smart_admin.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 smart_admin.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 smart_admin.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 procedure smart_admin.sp_set_db_backup and provide the new values. For more information, see SQL Server Managed Backup to Microsoft Azure - Retention and Storage Settings.

Enable SQL Server Managed Backup to Microsoft Azure for the Instance with Default Settings

This tutorial describes the steps to enable and configure SQL Server Managed Backup to Microsoft Azure for the instance, 'MyInstance',\. It includes steps to enable monitoring the SQL Server Managed Backup to Microsoft Azure health status.

Permissions:

Requires membership in db_backupoperator database role, with ALTER ANY CREDENTIAL permissions, and EXECUTE permissions on sp_delete_backuphistorystored procedure.
Requires SELECT permissions on the smart_admin.fn_get_current_xevent_settingsfunction.
Requires EXECUTE permissions on the smart_admin.sp_get_backup_diagnostics stored procedure. In addition, it requires VIEW SERVER STATE permissions as it internally calls other system objects that require this permission.

Create a Microsoft Azure storage account: The backups are stored in the Microsoft Azure storage service. You must first create a Microsoft Azure storage account, if you do not already have an account.
- SQL Server 2014 uses page blobs, which are different than block and append blobs. Therefore you must create a general purpose account, and not a blob account. For more information, see About Azure storage accounts.
- Make a note of the storage account name, and the access keys. The storage account name and access key information is used to create a SQL Credential. The SQL Credential is used to authenticate to the storage account.
Create a SQL Credential: Create a SQL Credential using the name of the storage account as the Identity and the storage access key as the password.
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. Once SQL Server Managed Backup to Microsoft Azure is enabled at the instance level with the defaults all new databases created thereafter will inherit the settings. Only databases that are set to full or bulk-logged recovery models are supported and will be configured automatically. You may disable SQL Server Managed Backup to Microsoft Azure for a specific database at any time if you do not want SQL Server Managed Backup to Microsoft Azure configured. You can also change the configuration for a specific database by configuring SQL Server Managed Backup to Microsoft Azure at the database level.
Enable and configure SQL Server Managed Backup to Microsoft Azure : Start SQL Server Management Studio and connect to the instance of SQL Server. From the query window run the following statement after you modify the values for the database name, SQL Credential, retention period, and the encryption options per your requirements:

For more information on creating a certificate for encryption, see the Create a Backup Certificate step in Create an Encrypted Backup.
```
Use msdb;  
Go  
   EXEC smart_admin.sp_set_instance_backup  
                 @enable_backup=1  
                ,@retention_days=30   
                ,@credential_name='sqlbackuptoURL'  
                ,@encryption_algorithm ='AES_128'  
                ,@encryptor_type= 'Certificate'  
                ,@encryptor_name='MyBackupCert';  
GO  
```
SQL Server Managed Backup to Microsoft Azure is now enabled on the instance.
Verify the configuration settings by running the following Transact-SQL statement:
```
Use msdb;  
GO  
SELECT * FROM smart_admin.fn_backup_instance_config ();  
```
Create a new database on the instance. Run the following Transact-SQL statement to view the SQL Server Managed Backup to Microsoft Azure configuration settings for the database:
```
Use msdb  
GO  
SELECT * FROM smart_admin.fn_backup_db_config('NewDB')  
```
It may take up to 15 minutes for the settings to show and backup operations on the database to start to run.
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. To receive such notifications, you must enable run the stored procedure which creates a SQL Server Agent Job. The following steps describe the process to enable and configure e-mail notifications:
1. Setup Database Mail if it is not already enabled on the instance. For more information, see Configure Database Mail.
2. Configure SQL Server Agent Notification to use Database Mail. For more information, see Configure SQL Server Agent Mail to Use Database Mail.
3. Enable e-mail notifications to receive backup errors and warnings: From the query window, run the following Transact-SQL statements:
```
EXEC msdb.smart_admin.sp_set_parameter  
@parameter_name = 'SSMBackup2WANotificationEmailIds',  
@parameter_value = '<email address>'  
```
  For more information about how to monitor, and a full sample script see Monitor SQL Server Managed Backup to Microsoft Azure.
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 a container for the instance of SQL Server that hosts the database you configured to use SQL Server Managed Backup to Microsoft Azure. You may also see a database and a log backup within 15 minutes of creating a new database.

--  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 smart_admin.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 smart_admin.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 smart_admin.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek;

SQL Server Managed Backup to Microsoft Azure default settings can be overridden for a specific database by configuring the settings specifically at the database level. You can also pause and resume SQL Server Managed Backup to Microsoft Azure service temporarily. For more information, see SQL Server Managed Backup to Microsoft Azure - Retention and Storage Settings

Share via

Setting up SQL Server Managed Backup to Azure

Setting Up SQL Server Managed Backup to Microsoft Azure

Enable and Configure SQL Server Managed Backup to Microsoft Azure for a Database

Enable SQL Server Managed Backup to Microsoft Azure for the Instance with Default Settings

Additional resources