Backups just got better with SQL Server 2016

By: Pinal Dave


Every release of SQL Server brings many new capabilities–and opportunities for something new to learn. SQL Server 2016 is nothing new in this regards. Along with many other new features in SQL Server 2016, Microsoft has invested enhancements to backup. After exploring these enhancements further, I believe these features are very much of a cloud enabler.

Managed Backup – Enhancement

Managed backup was introduced in SQL Server 2014, and allowed an automatic database backup to  Microsoft Azure, based on changes done in the database. This feature schedules, performs and maintains the backups–all a DBA needs to do is specify a retention period. In SQL Server 2014, there was not much of control for frequency. In SQL Server 2016, this has been enhanced:

  • System databases can be backed up.
  • Backup of databases in simple recovery model is possible.
  • A backup schedule can be customized based on business need rather than log usage.

There are many objects added in MSDB database in SQL Server 2016 to manage them. They are located under new schema called managed_backup. We can run the below query to find all objects under the new schema.

SELECT name, type_desc FROM msdb.sys.objects

WHERE SCHEMA_ID = SCHEMA_ID('managed_backup')

All the functionality of managed backup is done by SQL Server Agent so it’s important to make sure that SQL Agent is set to start automatically.

Backup to Azure – Enhancement

As of this writing, Microsoft Azure provides four type of storage:

  • Block blobs
  • Page Blobs
  • Disks, Tables and Queues
  • Files

SQL Server 2014 allowed you to take a backup of Page blobs. If we look at the monthly storage price for block blobs, it is cheaper than page blobs. In SQL Server 2016, you can now take backups on block blobs.

It is important to note that the page blog has a limit of 1 TB, while a block blob is limited to 200 GB. Does this mean we can’t take backup more than 200 GB? No, we are allowed to take striped backups and we can split the file into multiple block blobs. The maximum limit of stripes is 64, so now we can backup beyond the earlier limit of 1 TB to 12.8 TB (64*200GB)

The command to take backup of database is “backup … to url” so it is also called as backup2url.

File-Snapshot Backups

SQL Server 2016 now has a File Snapshot backup feature available for databases that are stored in the Azure Blob Store. This feature would greatly help the SQL Server running on an Azure Virtual machine. It uses the Windows Azure blob snapshot functionality to take a backup of the database.

This feature can ONLY be used if the files of the database are residing in Azure blob storage. Here is the error message which is raised if we try to take FILE_SNAPSHOT backup for a regular database for which files are residing on local disk.

Msg 3073, Level 16, State 1, Line 1

The option WITH FILE_SNAPSHOT is only permitted if all database files are in Azure Storage.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

sql2016backup-01

The snapshot backup can be treated as a full database backup or transactional log backup. We can use STOPAT command on restore and do a point in time recovery of the database. We must remember that the point in time restores need to use either traditional backups OR file-snapshot backups. We can’t mix both and do point in time recovery.

Taking a snapshot of the database would take space, which has associated osts. Since storage is not very costly, it might be assumed that keeping many snapshots is OK, which is not the case. Microsoft advises that you keep only those file-snapshot backups which are necessary to support the RPO of the business. This is because maintaining an excessive number of file-snapshot backups can have a negative impact on the I/O performance of the database.

Conclusion

I am glad to see the investment that Microsoft is making in the area of backups. Because backups have been in the industry for a long time, we often assume there is less scope for innovation. But every now and then, things such as these backup enhancements in SQL Server 2016 make me reconsider that statement.

Comments

  1. Hi,
    With Regards to the following points you mentioned about the SQL 2016 backup Enhancements.

    -System databases can be backed up.
    -Backup of databases in simple recovery model is possible.

    I checked my SQL Server 2014 instance where it allows me to take the System database backup to Azure url.

    Microsoft SQL Server 2014 – 12.0.4100.1 (X64)
    Apr 20 2015 17:29:27
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 (Build 10586: ) (Hypervisor)

    Whereas Even in the SQl 2016 it doesn’t allow me to take backups of databases in Simple recovery Mode.
    I get the below Error

    Msg 3271, Level 16, State 1, Line 15
    A nonrecoverable I/O error occurred on file “https://xxxstorage.blob.core.windows.net/backups/Example1907.bak:” Backup to URL received an exception from the remote endpoint.
    Exception Message: Unrecoverable error occurred during Flush operation.
    Msg 3013, Level 16, State 1, Line 15
    BACKUP DATABASE is terminating abnormally.

Leave a Reply