29 Apr

SQL Server Managed Backup to Microsoft (Windows) Azure | Part-2

In the last post we discussed about the features of SQL Server Managed Backup to Windows Azure, how it is different from conventional backup schedules, how the backup and container gets named and based on what conditions SQL Server will initiate a Full\Transactional Log backup or skip it.


What are the benefits of using this technology:

1. If you want to automate backups without using this feature you need to develop a backup strategy, you may also have to write your own code and schedule the jobs etc. But by using this feature all you need to do is provide the retention period & storage location that’s it and SQL Server Managed backup to windows azure will schedule, perform and maintain the backups for you.

2. This feature can be configured at DB level or at the instance level.

a. If you enable this at the instance level this feature will be automatically applied to all DBs created thereafter that means you don’t have to worry about the newly created DBs.

b. If you enable this at the DB level and specify the retention period this will override the default settings set at the instance level; means more granular control.

3. You don’t even have to worry about the type of backup and frequency, all of these things gets determined automatically by SQL Server Managed Backup to Azure based on the retention period you specify and the workload on the database.

4. If you configure to use Encryption it gives you additional Security. Backup Encryption is new feature added in SQL 2014 and you can also use it in Native backups on disk\tape and you can take backup with Encryption using T-SQL or GUI both.


Things you must know about this feature:

1. A full backup and a transaction log backup can run concurrently, however if there are more than 1 Full DB backup jobs running for the same database it will wait for the current job to complete before initiating another backup; the same applies for transaction log backup also.

2. If more than 10 concurrent full DB backups are scheduled, it will issue a warning through debug channel of Extended Events; then maintains a priority queue for the remaining DBS that require a backup until all the backups are scheduled and completed.

3. Failures are logged as Extended Events.


Limitations of Managed Backup in SQL 2014

Don’t you think features discussed till now are too good to be true, yes just like any other technology there are some limitations for SQL Server Managed Backup to Windows Azure, these are:

1. Only Full and Transaction Log backup is possible through Managed Backup.

2. System Databases are not supported.

3. Only full & Bulk-Logged recovery models are supported so the recovery model of the DB should be one of them.

4. File backup automation is not supported as of now.

5. SQL Server Managed backup to Azure is currently supported only by T-SQL; you can monitor and troubleshoot by using Extended Events though. If you are a PowerShell & SMO freak then watch out, because PS & 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.

6. Windows Azure Blog Storage service is the only supported backup storage option. Backup to disk and tape are not supported.

7. Currently the maximum file size allowed for a page blog in windows azure storage is 1 TB that means you can’t backup the DB if the backup size goes beyond 1 TB & it will fail. To avoid such scenarios you can use Compression and test the size before configuring the backup & keep a check on the backup size.

8. It also may have some limitations when it is configured with other technologies supporting backups, HA & DR.


Sarabpreet Singh

25 Apr

SQL Server Managed Backup to Microsoft (Windows) Azure | Part-1

SQL Server Managed backup is a new feature introduced in SQL Server 2014, this feature is simply outstanding – you don’t have to worry about the backup strategy anymore, this feature manages and automates the backup process.

This feature uses sort of artificial intelligence – if you see at current scheduling features they just keep on taking backups at a specified time interval whereas, in this feature it checks couple of things and based on the situation it decides whether a backup needs to be initiated for a Database or not. This Feature is based on Retention period selected for backups and transaction workload on the database and it also provides Point-in-time recovery. You want to know how? Keep reading… you’ll get your answers soon.

You can enable this feature at two levels Instance as well as Database level. It doesn’t really matter if your SQL Server is running On-Premise or on Cloud. SQL Server Managed Backup to Microsoft (Windows) Azure is in-fact recommended for SQL Server running on a VM in Cloud.

The Retention period is a value which determines what backup files to keep and what needs to be deleted in the Azure Storage so that the underlying DB can be recovered to a point in time within the time frame specified. The valid retention period values are from 1 till 30 days.  The backup_finish_date of the backup is used to determine and match the time specified by the retention period settings.

Naming convention it follows for the backup and container

The backup files are named using a combination of first 40 characters of the database name, the GUID of DB without “-“& the timestamp; all these values are separated using Underscore to make it readable. The .bak and .log extensions are used for Full and Log Backups respectively. In case of Availability Group databases the Availability group database GUID is added after the first 40 characters of the DB name, you can get this GUID value from group_database_id column of Sys.databases.

The same way Backup container of (Windows) Microsoft Azure also get named using the SQL Instance name and in the case of Availability databases it gets named using GUID of Availability group.


Conditions that trigger a backup

As we discussed earlier also SQL Server managed backup doesn’t initiate a backup merely on the basis of schedule which has been fixed but takes a calculative decision whether a backup is really required for the given DB or not. It decides based on the below mentioned conditions:

A. Full backup gets triggered:

1. At the time when Managed Backup is configured on DB or when it is configured on the complete instance with default settings.

2. If the log generation since last full backup is greater than or equal to 1 GB in size.

3. If maximum time interval of one week has passed since the last full backup. (it tries to take at least one full back up every week)

4. When the log chain is broken; it checks periodically whether the log chain is intact or not by comparing first and last LSN of backup files, if it finds there is a gap it initiates a full DB Backup. There could be n number of reasons for log chain breaks, most common would be:
a. Switching recovery model to simple.
b. Taking back up through any other job\manual process.
c. Deleting some backup files
d. Accidental overwrites of backup files.
e. Truncating the log.

B. Transaction Log backup gets triggered:

1. When the Transaction Log space used is equal to or greater than 5 MB.

2. The maximum time interval of 2 hours has passed since the last log backup.

3. If no log backup history is found; for example when you enable Managed backup for the first time for the respective DB.

4. Anytime the TLog backup is lagging behind the full backup, it always tries to keep the log chain ahead of the Full backup. In simple words after every full backup it will initiate one TLog backup.


Sarabpreet Singh Anand