SQL Server managed backups to Azure

By: Burt King   |   Updated: 2017-01-09   |   Comments (3)   |   Related: > Azure Backup and Restore

Problem

I’ve been tasked by management to investigate SQL Server 2014 managed backups to AZURE, so that development teams around the country can backup their own servers without needing a full time DBA to manage that process. We have offices around the world and we are hoping to be able to put database backups in the cloud, so that the different offices don't need to worry about the storage or the backup process.

Solution

SQL Server backups to a blob service were first introduced with SQL 2012 Service Pack 1, CU2. The functionality was termed “backup to a URL” and allowed DBA’s to backup to a SQL Azure blob by specifying credentials and a URL to use. With that solution SQL Server DBA’s determine how often to run the backups and log backups much like they are currently doing.

SQL Server 2014 offers that service and also extends the functionality to be a fully managed backup solution for user databases; system databases cannot be backed up with SQL 2014 managed backups. The major difference between backing up to a URL and SQL managed backups is that the SQL Server itself is managing the entire backup process including times of the day and frequency. SQL Managed backups in SQL 2014 offer only 2 configurable settings. The retention period in days and the databases which will be backed up. Microsoft completely manages the rest of the process including the frequency of Full backups and the frequency of transaction log backups. SQL 2016 offers additional settings, but that will not be covered here. There is also a user interface in SQL 2014 Management Studio, but there are a number of restrictions to its use and it will not be covered in this tip.

Caveats

• Azure backup blob must be the correct ‘type’. Azure storage offers more than one type of blob storage but not all will work. You will need to insure the correct blob type is available.
• System databases cannot be backed up in SQL 2014 nor can databases running in simple recovery model.
• You should not use maintenance plans or other scripts to take backups. This will create an issue for the SQL managed backup to create and maintain a valid retention period worth of backups.
• Availability groups can be backed up if the SQL Server is an Azure based host. On premises and hybrids of SQL Azure and on premises solutions are not supported.

Preparing the blob storage

The first step in setting up managed backups usually requires working with a storage team to allocate the blob storage. The storage team needs to insure the blob storage is compatible with the backups. An Azure general purpose blob account is required to work with SQL 2014 managed backups. A definition of SQL Azure blob accounts is provided here: About Azure storage accounts.

This tip doesn't discuss Azure storage administration, but the following image may assist you when asking your storage team for assistance. With SQL 2014 you must request a general-purpose storage account which accepts blobs, tables, and other objects as noted in the previous article. SQL 2016 changes this limitation, but that is beyond the scope of this article.

Non-Encrypted backups of a single database

There is a procedure for backups that are not encrypted and another process for encrypted backups. This section will focus on non-encrypted backups, but don’t skip ahead to the encrypted section because this material applies there too and is needed when you get there.

Whether running backups of all the databases or just a single database the starting point is to prepare the server with a credential which is used for identification to the storage. Credentials are used by SQL Server when authentication outside of SQL is needed. In this case we will be connecting to AZURE blob storage and need the account information to make that connection. We think of these things in terms of user names and passwords, but the storage administrator should be asked for the identity and the secret. The identity is the name of the storage account and the secret is an access key which allows connecting into the storage. The syntax to create the credential is simple.

create credential sqlblobbackups
with identity = 'sqlblobbackups',
secret = 'AEIF361AbjkADNIilKRRchaKHng72yz4ocEp1zCX00tWOeo5N2w==';

Once the credential is established we can configure backups. The first procedure establishes backups for a single database running in either bulk log mode or full recovery mode. The command to run is:

EXEC smart_admin.sp_set_db_backup   
    @database_name='Adventure Works 2014'   
   ,@retention_days=1   
   ,@credential_name=sqlblobbackups
   ,@encryption_algorithm = NO_ENCRYPTION
   ,@enable_backup=1;  

With this command notice that the encryption_algorithm is required to be set to NO_ENCRYPTION if we are not encrypting the backups. After running the command we can check on the configuration of the backups.

SELECT * 
FROM smart_admin.fn_backup_db_config ('Adventure Works 2014')

The results of this function demonstrate an interesting point about SQL Server managed backups. At no point do we specify where the backups reside. SQL Server internally understands that we're using Azure storage and interrogates the Azure service to determine what endpoint to use based on the identity of the storage and the secret we passed in. Further in this article we'll discuss verifying that backups have run. At this point let's look at backing up all databases on a server.

Non-Encrypted backups of all databases

Thus far we've created a backup of a single database. Enabling backups of all databases that are running in Full recovery mode or bulk-logged is simple.

EXEC smart_admin.sp_set_instance_backup  
    @retention_days=7   
   ,@credential_name='sqlblobbackups' 
   ,@encryption_algorithm ='NO_ENCRYPTION'  
   ,@enable_backup=1;  

As with single databases, we still need to add 'NO_ENCRYPTION' and can check on the configuration of each individual database by using smart_admin.fn_backup_db_config. In the next section we'll discuss verifying backups have run.

Verifying backups

After configuring backups a period of up to 15 minutes is needed before backups start running. To check on the backup status of our database Microsoft documentation offers one solution -- check the azure storage from within SQL Server Management Studio. That's demonstrated below, but we're going to show you two other methods.

In SQL Server Management Studio connect to the storage providing the identity and the secret we previously used.

Once the SQL Server Management Studio is connected we see the container for the backups and the blob names. These backups have a timestamped value.

While this method shows that backups are occurring it isn't really a workable solution with more than a couple of databases to check. Noticeably absent in SQL 2014 managed backups is a method of verifying backups are running. A function for SQL 2016, however, does work with SQL 2014 Service pack 2.

SELECT * 
FROM msdb.smart_admin.fn_available_backups ('Adventure Works 2014') 
ORDER BY backup_finish_date desc;

It's also possible to right click a database and look for the backups by selecting the restore option. This will prompt you for the identity and the secret and by default shows the most recent full backup and logs.

The restore database window is the same interface DBAs are used to working with.

Encrypting backups

Encrypting backups with SQL Managed backups is simple once you worked through the issues of getting regular backups in place. For our purposes we’re adding encrypted backups after managed backups are already in place, but they can be enabled as part of the initial configuration. My preference is to add them after since the initial configuration can be difficult considering all of the new technologies we are using and diagnosing errors with the configuration can be difficult. That said, you cannot simply run the encrypted backups script and have SQL Managed backups pick up the change from non-encrypted backups to encrypted backups. Instead, it is necessary to disable all backups and then start with a clean slate. To disable all backups run the following script provided by Microsoft:

--Disable a single database backup
EXEC smart_admin.sp_set_db_backup   
    @database_name='Adventure Works 2014'   
   ,@enable_backup=0;

-- Disable all backups
-- Create a working table to store the database names  
 DECLARE @DBNames TABLE  
    (  
  RowID int IDENTITY PRIMARY KEY  
 ,DBName varchar(500)  
 )  
-- Define the variables  
 DECLARE @rowid int  
 DECLARE @dbname varchar(500)  
 DECLARE @SQL varchar(2000)  
-- Get the database names from the system function  
 INSERT INTO @DBNames (DBName)  
 SELECT db_name  
 FROM   
 smart_admin.fn_backup_db_config (NULL)  
 WHERE is_smart_backup_enabled = 1  
 
--Select DBName from @DBNames  
    SELECT @rowid = min(RowID)  
    FROM @DBNames  
 WHILE @rowID IS NOT NULL  
 Begin  
 Set @dbname = (Select DBName From @DBNames Where RowID = @rowid)  
 Begin  
    Set @SQL = 'EXEC smart_admin.sp_set_db_backup    
        @database_name= '''+'' + @dbname+ ''+''',   
        @enable_backup=0'  
    EXECUTE (@SQL)  
 END  
 SELECT @rowid = min(RowID)  
 FROM @DBNames Where RowID > @rowid  
 END  
   

Encrypting backups requires a certificate be installed on the server. This kb article from Microsoft describes the process.

For our example we’ll create a certificate to be used for backups and then enable SQL Managed backups which utilize that certificate.

CREATE CERTIFICATE azureBackupCert
WITH SUBJECT = 'Backup Encryption Certificate';

SELECT * FROM sys.certificates;
  

With the certificate in place and backed up you can then enable backups by adjusting the previous script to include security.

EXEC smart_admin.sp_set_instance_backup  
    @retention_days=7
   ,@credential_name=sqlblobbackups   
   ,@encryption_algorithm ='AES_128'  
   ,@encryptor_type= 'Certificate'  
   ,@encryptor_name='azureBackupCert'  
   ,@enable_backup=1;  

From here managing the server and backups is the same as non-encrypted backups. You use the same functions and techniques to make sure backups are running and to restore the backups. As with other certificate enabled backups, a copy of the certificate is required on any server you expect to recover those databases to. For more on the encryption of the backups Microsoft offers this article on Backup encryption.

Restoring databases backups

Restoring databases was mentioned in the Verifying Backups section. Other than providing the identity and the secret, the process of selecting a backup to restore and restoring it through the user interface is the same.

Scenarios to be aware of

• Adding databases. We've seen the way to determine if a backup is configured is to use the function, smart_admin.fn_backup_db_config. When moving from unencrypted backups to encrypted backups we used a script from Microsoft which disabled the backups which allowed us to go back and then set them again using encryption. When adding new databases or changing the recovery model it is necessary to review the results of this function for each database affected to insure it is picked up and backups run. Microsoft states that backups will run within 15 minutes of being configured (or presumably switched to full recovery mode).
• Monitoring the server. SQL Server managed backups are not implemented using the SQL Agent. DBAs may use third party solutions or write their own custom scripts to determine backup status. Since backups are documented in msdb we can still run many of the same queries we regularly use to monitor backups. The script below will determine backups that have completed recently.

SELECT distinct(bms.backup_finish_date) as backupFinishDate, 
   (bmf.media_set_id) as mediaid, 
   (bmf.physical_device_name) as backupfilename, 
   sd.name as dbname, 
   bms.[type] as backuptype
FROM msdb..backupmediafamily bmf 
INNER JOIN msdb..backupset bms ON bmf.media_set_id = bms.media_set_id 
INNER JOIN master..sysdatabases sd ON bms.database_name = sd.name
WHERE bms.backup_finish_date > dateadd(hh,-23,getdate())
   AND bms.type <> 'L'
   AND bmf.media_set_id IN 
      (SELECT max(bmf.media_set_id)
       FROM msdb..backupmediafamily bmf 
       INNER JOIN msdb..backupset bms ON bmf.media_set_id = bms.media_set_id 
       INNER JOIN master..sysdatabases sd ON bms.database_name = sd.name
       GROUP BY bms.type, sd.name
       HAVING bms.type IN ('D','L'))

• Disabling Backups. The procedure to disable backups was previously mentioned in the encrypted backups section, but we'll mention it again here. There is no command to run which will disable backups instance wide. Instead, we make use of the smart_admin.sp_set_db_backup function to disable each backup separately. The code below illustrates disabling the backup and then reviewing the new configuration.

EXEC smart_admin.sp_set_db_backup @database_name='Adventure Works 2014' ,@enable_backup=0;

SELECT * FROM smart_admin.fn_backup_db_config ('Adventure Works 2014');  

Next Steps

• Review this Microsoft article on how to Monitor SQL Server Managed Backup to Windows Azure.
• Review this Microsoft article on the user interface configuration and decide if that's a better tool for you. There are limitations with the interface that you need to pay attention to.
• Review this documentation on setting retention and storage settings.
• Check out these other Azure and Backup tips.

About the author

Burt King is Senior Database Administrator at enservio with more than 15 years experience with SQL Server and has contributed to MSSQLTips.com since 2011.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips