Select Page

Striped SQL Server Backups with Azure Blob Storage

Eric Russo | | August 2, 2016

Striped Backup Azure

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.

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS