Striped SQL Server Backups with Azure Blob Storage

Microsoft has been investing in their cloud platform, Azure, heavily. The best part of this investment is the added capabilities that will be realized in the next version of SQL Server 2016. A number of features initially added to the Azure SQL Databases are now transferring to the on premise version too. This is a good sign because if a particular feature/capability works well for millions of databases (on the cloud), then it has great potential in enterprise databases.

In this blog, I want to take a look at one of the more mundane activities a DBA does: Backups. Backups have been a standard practice for DBAs for more than 2-3 decades, but the innovations have been almost nonexistent in the past few years. This is why I’m very excited that, with the introduction of Azure, Microsoft has brought a feel of freshness to the whole backup strategy. Several interesting enhancements were brought from SQL Server 2014, including the ability to take a backup to blob directly using the BACKUP TO URL construct. Further details can be found in this blog by Pinal Dave.

The syntax looks like this:

BACKUPDATABASE [AdventureWorks2016] TOURL='https://mystorageaccount.blob.core.windows.net/privatecontainers/AdventureWorks2016.bak'

/* URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file*/

WITHCREDENTIAL='mycredential';

GO

Backup to URL introduced the ability for users to perform backups that are stored directly in Cloud-based Storage without the need for any intermediate local storage. This enables customers in either on premise, IAAS, or even hybrid systems deployments to maintain their backups in a persistent storage location which is available, redundant, Geo-replicate-able and which provides for off-site archival needs. This feature was designed and delivered in a SQL cumulative update for SQL server 2012. The current Backup to URL communicates with Cloud Storage and is performed through a REST API over HTTP. Backup to URL employs the Virtual Device Interface (VDI), implemented in a companion process to facilitate communication and data transfer to the Cloud Storage service.

What is new with SQL Server 2016?

With SQL Server 2016, we will be able to perform striped SQL Server backups pointing to a Block Blob. Here are some of the benefits of this functionality:

• Striping a backup increases the overall throughput of your backups hence reducing backup time.
• By striping, we are able to read/write to multiple files in parallel.
• With Block blobs and striping, now the backup files sizes can be up to 12.8 TB on Azure.
• In SQL 2016, the backup to blob is baked into the backup engine natively.
• Appending to a backup is currently not supported.

Let us look at the syntax:

BACKUPDATABASE AdventureWorks2016

TOURL='https://mystorageaccount.blob.core.windows.net/b2bb/AdventureWorks-Part1.BAK',

URL='https://mystorageaccount.blob.core.windows.net/b2bb/AdventureWorks-Part2.BAK',

URL='https://mystorageaccount.blob.core.windows.net/b2bb/AdventureWorks-Part3.BAK'WITHFORMAT

It doesn’t look any different from what we used to do earlier with DISK’s; they have simply been replaced with the URL. It is really simple to use now. If you have used the Backup to URL feature before, let me know your feedbacks via comments below.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.