In a previous blog post called Backup a SQL Server database from On-Premise to Azure, I presented the different tools to backup your on-premise databases on Azure Storage. SQL Server Managed Backup to Windows Azure was one of these tools.

In my opinion, Managed Backup is a great tool. That is why I decided to dedicate an entire blog to this feature.

Understanding Managed Backup

Managed Backup is a new feature introduced in SQL Server 2014 working with Windows Azure. This feature allows to manage and automate SQL Server backups (from your on-premise or Azure SQL Server instance), configurable by … script only (T-SQL or PowerShell)!

Microsoft recommends to use Managed Backup for Windows Azure virtual machines.

Managed backup only works with user databases in Full or Bulk-logged Recovery Model, and can only perform Full and Log backups.

SQL Backups supports a point in time restore, and are stored following a retention period. This setting indicates the desired lifespan of a backup stored in Azure Storage. Once the period is reached, the backup is deleted.

SQL Backups are scheduled following the transaction workload of the database.

A full database backup is scheduled when:

  • The Managed backup feature is enabled for the first time
  • The log growth is 1 GB or larger
  • The last full database is older than 1 week
  • The log chain is broken

A transaction log backup is scheduled when:

  • No log backup history is available
  • The log space is 5 MB or larger
  • The last log backup is older than 2 hours
  • A full database has been performed

Configuring Managed Backup

First, you need to activate SQL Server Agent service in order to use the feature.

In this example, I have 3 user databases as follows:

 

Database Named

Recovery Model

Data Files Location

AdventureWorks2012

Simple

On-premise

AdventureWorks2014

Full

On-premise

Hybriddb

Bulk-logged

Azure Storage

Managed Backup can be enabled at the instance level or database level.

If you decide to activate the feature at the instance level, the configuration will be set for all user databases of your instance (even for databases added after the configuration).

On the other hand, you can activate the feature for specific user databases. If the feature is also configured at the instance level, it will be overridden by the configuration at the database level.

To configure the feature, you must provide a set of parameters:

  • The URL of the Azure Storage
  • The retention period in days
  • The credential name
  • The encryption algorithm

If the encryption algorithm is not set to ‘NO_ENCRYPTION’, you also need to provide these parameters:

  • The encryptor type
  • The encryptor name

Moreover, when you configure your Managed Backup, you need to specify if you want to activate your Managed Backup.

You can perform a database backup with COPY_ONLY. To do this, you need to use ‘smart_admin.sp_backup_on_demand’ stored procedure, by specifying the database name.However, this stored procedure will use the configuration of the Managed Backup at the database level. That means you must configure and enable the Managed Backup for your database.

We need to create a credential in order to be able to connect to Azure Storage:

CREATE CREDENTIAL dbiservicesWITH IDENTITY = ‘dbiservices’,

SECRET = ‘password’

Let’s configure our Managed Backup at the instance level:

USE msdb;GO

EXEC smart_admin.sp_set_db_backup

@enable_backup = 0,

@storage_url = ‘https://dbiservices.blob.core.windows.net’,

@retention_days = 15,

@credential_name = ‘dbiservices’;

@encryption_algorithm = ‘NO_ENCRYPTION’;

If I want to display the instance configuration:

USE msdb;GO

SELECT * FROM smart_admin.fn_backup_instance_config();

Here is the result:

display-configuration.png

We will override the Managed Backup configuration for ‘hybriddb’ database:

USE msdb;GO

EXEC smart_admin.sp_set_db_backup

@database_name = ‘hybriddb’,

@enable_backup = 0,

@credential_name = ‘dbiservices’,

@storage_url = ‘https://dbiservices.blob.core.windows.net,

@retention_days = 25,

@encryption_algorithm = ‘NO_ENCRYPTION’;

If I want to display the database configuration of all databases of the instance:

USE msdb;SELECT db_name, is_managed_backup_enabled, retention_days, storage_url, encryption_algorithm

FROM smart_admin.fn_backup_db_config(NULL)

Here is the result:

diplay-databases-configuration.png

Notice that ‘AdventureWorks2012’ database has ‘is_managed_backup_enabled’ set to ‘NULL’. Indeed, this database is not sensitive to Managed Backup because it has his Recovery Model set to Simple.

Now, I activate the Managed Backup at the instance level:

USE msdb;GO

EXEC smart_admin.sp_set_db_backup

@enable_backup = 1;

GO

Now, I activate the Managed Backup for ‘hybriddb’ database:

USE msdb;GO

EXEC smart_admin.sp_set_db_backup

@database_name = ‘hybriddb’,

@enable_backup

If I explore Azure Storage, I can find my backups:

backups.png

Conclusion

As I said in Introduction, Managed Backup is a great feature. Easily and quicly, you can configure and enable backups for your user databases.

However, it has some serious limitations… We can expect Managed Backup to be extended to system databases. Moreover, we can also expect Managed Backup to allow backups from user databases in Simple Recovery Model.

Furthermore, this feature is only available to Azure Storage. Indeed, I would like to choose my storage destination. I do not understand why we cannot back up to local disks for example.

Post Views: 1,019
Thumbnail [60x60]
by
Nathan Courtine