<img src="https://track.adform.net/Serving/TrackPoint/?pm=268420" width="1" height="1" alt="">

Backup On-Permiss SQL Server Databases to Azure Storage

10 min. læsetid
4. november 2013 Skrevet af: Martin Schmidt Del med en ven     

With the release of SQL Server 2012 SP1 CU2, an enhancement to the BACKUP command has been added. Now the BACKUP command support “TO URL” when you specify a backup device. This gives you the option to use an Azure Storage account as destinations for your backups.

Many people before me have already written about how to configure this, so I’ll skip that in this blog post. If you can’t find the articles yourself, let me know and I’ll post some URLs.

Personally I think that this feature sounds pretty cool. The last couple of weeks I have seen so many poorly performing remote backup solutions where the customer no way near get what they pay for. This has the potential to be a remote backup killer.

The main purpose of this blog post is to do some monkey testing. What will work and what will break. Backup to Azure is one thing, but what about the old MIRROR TO command. How will keywords like “BufferCount” and “Maxtransfersize” react to the new TO URL destination? And maybe the most important question; what speeds will we be able to get, and will the speed be stable?

Let’s do some monkey testing
For this test I have a little database called “Loader” – it contains approximately 8 GB data spread evenly across 4 data files.

Test 1
This very first test is just to verify that my setup works, no magic added just a standard compressed backup.

<span style="color:blue;">BACKUP DATABASE </span>Loader
<span style="color:blue;">TO
</span>URL <span style="color:gray;">= </span><span style="color:red;">'http://insparisupport.blob.core.windows.net/inspari/loaderbackup.bak'
</span><span style="color:blue;">WITH CREDENTIAL </span><span style="color:gray;">= </span><span style="color:red;">'myCredential'</span><span style="color:gray;">, </span><span style="color:blue;">Compression
</span>

 

Output:
Processed 249848 pages for database 'Loader', file 'Loader' on file 1.
Processed 249528 pages for database 'Loader', file 'Loader02' on file 1.
Processed 249520 pages for database 'Loader', file 'Loader03' on file 1.
Processed 249528 pages for database 'Loader', file 'Loader04' on file 1.
Processed 2 pages for database 'Loader', file 'Loader_log' on file 1.
BACKUP DATABASE successfully processed 998426 pages in 89.323 seconds (87.325 MB/sec).

Success - Rock n roll :) if I browse the Azure portal I’m able to find the file that I just posted. The speed maxed out my NIC – so far so good.

Test 2
Azure is a remote backup location, so I would like to use the MIRROR keyword, and also save a backup to my local backup server. Here is the syntax that I would normally use for that:

<span style="color:blue;">BACKUP DATABASE </span>Loader
<span style="color:blue;">TO </span>URL <span style="color:gray;">= </span><span style="color:red;">'http://insparisupport.blob.core.windows.net/inspari/loaderbackup.bak'
</span>MIRROR <span style="color:blue;">TO DISK </span><span style="color:gray;">= </span><span style="color:red;">'\BackupServertestloaderbackup.bak'
</span><span style="color:blue;">WITH CREDENTIAL </span><span style="color:gray;">= </span><span style="color:red;">'myCredential'</span><span style="color:gray;">, </span><span style="color:blue;">Compression
</span>

 

Error message:
Msg 3294, Level 16, State 1, Line 1
Use of the URL device type is limited to a single device during Backup and Restore operations.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Ok, as the error message tells me, URL only supports a single device. The MIRROR command is a no go.

Test 3
I like to split my backup sets into multiple files, one thing is the performance boost another is that it is so much easier to handle x smaller files, than one TB sized file. Here is some code for test 3:

<span style="color:blue;">BACKUP DATABASE </span>Loader
<span style="color:blue;">TO
</span>URL <span style="color:gray;">= </span><span style="color:red;">'http://insparisupport.blob.core.windows.net/inspari/loaderbackup01.bak'</span><span style="color:gray;">,
</span>URL <span style="color:gray;">= </span><span style="color:red;">'http://insparisupport.blob.core.windows.net/inspari/loaderbackup02.bak'</span><span style="color:gray;">,
</span>URL <span style="color:gray;">= </span><span style="color:red;">'http://insparisupport.blob.core.windows.net/inspari/loaderbackup03.bak'</span><span style="color:gray;">,
</span>URL <span style="color:gray;">= </span><span style="color:red;">'http://insparisupport.blob.core.windows.net/inspari/loaderbackup04.bak'
</span><span style="color:blue;">WITH CREDENTIAL </span><span style="color:gray;">= </span><span style="color:red;">'myCredential'</span><span style="color:gray;">, </span><span style="color:blue;">Compression
</span>

 

Error message:
Msg 3294, Level 16, State 1, Line 2
Use of the URL device type is limited to a single device during Backup and Restore operations.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

Ok, as the error message tells me, URL only supports a single device. This means one file and one file only.

Test 4
In an on-premise scenario you would tune your source system for optimal read performance for backups with keywords like: BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE. When using the TO URL destination the only supported keyword is BUFFERCOUNT, if you try to use one of the others you will get this error message:

Msg 3291, Level 16, State 2, Line 3
URL device type was specified, and a disallowed option BLOCKSIZE was specified.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

Your internet connection is most likely going to be your bottleneck when doing backups to Azure storage, but anyway it is still a good plan to get the most out of the source system as possible

Compression
Compression is a gift, especially in this scenario where you have to transfer data to Azure. The better the compression rate you get the faster your backup will be. Less data = less time spent = better backup. In the tests that I have done my compression ratio is close to 8. When enabling backup compression my backup time went from 690 seconds to 85

pic1

Backup speed
This was one of the things that I was very skeptical about. When SQL azure was very young it took forever to load data into the tables in the cloud. But I must say that I am surprised in a very positive way. The infrastructure here is 100 Mbit, so that is the upper speed limit. Let’s have a look at the Windows task manager Networking tab.

pic2

As you can see we are running full throttle, my NIC is maxed out – wicked I did not expect that. If we take a closer look at Performance Monitor the picture is exactly the same

pic3

The counter displayed here is: Network Interface -> Bytes Sent/sec.

Take a look at how stable the data stream is and of course the numbers, 12+ million bytes / sec – it’s hard to get much out of a 100 Mbit connection. I have spent days speed testing and the results are always the same.