Home > Azure, SQL Server, T-SQL > SQL Server 2012 SP1 CU2 Native Backup to Azure Blob Storage

SQL Server 2012 SP1 CU2 Native Backup to Azure Blob Storage

January 30, 2013

There have been plenty of blog posts recently about the new feature in SQL Server 2012 SP1 CU2 that allows you to natively backup from SQL Server to your blob storage in Azure. After seeing the posts, I figured I would take it for a test drive. Based on the following blog post, I started poking around:

http://sqlblog.com/blogs/sqlos_team/archive/2013/01/24/backup-and-restore-to-cloud-simplified-in-sql-server-2012-sp1-cu2.aspx

This post refers to a few MSDN tutorials, the Azure price calculator and a few downloads for SQL Server 2012 CU2 to get this feature working. While all these articles explain most of the details, here are a couple of things to keep in mind while looking to use this feature in production – or just maybe in your development environment:

  • While you can use T-SQL to backup to Azure and perform restores from Azure, the native backup screen in SQL Server does not allow you to backup or restore from Azure. I have some T-SQL examples below, but the article mentioned above and the MSDN tutorials also have examples.
  • Determine if you truly need Geo Redundant storage in Azure as the costs are slightly higher as compare to Locally Redundant storage. For around $100/mo, you can get 1,475GB of Locally Redundant storage as compared to only 1,075GB of Geo Redundant storage. I choose to create one storage account for each type as reflected in my T-SQL and Azure screen shots below for testing purposes.

    You can check out the Azure pricing calculator yourself at:

    http://www.windowsazure.com/en-us/pricing/calculator/?scenario=data-management

  • You MUST turn on SQL Server backup compression (unless you have other 3rd party compression tools) if you want to reduce your Azure costs. I backed up the same empty database below with and without compression just to see what the different would be, obviously a test with your production databases would give you more precise comparisons. The image at the bottom shows 3.69MB uncompressed as opposed to 768KB compressed. You can turn on compressed at the instance level, the database level or at the individual backup level. If you choose to push only an occasional backup to Azure as the first steps of a new off-site DR plan, then you may choose only to compress these backups using the WITH COMPRESSION key words depending on CPU and storage capabilities are in your production environment.

  • There is nothing built in to the current tool set (SQL Server) that will help you manage your backup history in Azure. In other words, if you don’t write an application or manually manage your backup retention in Azure, your monthly costs will quickly get out of control. Might be a good job for a Jr. DBA or Developer to take care of.

Backing up to Azure is a nice to have feature. It allows a development teams or a small shops to create off-site backups with ease – especially if that team has an MSDN account and gets some Azure storage for free (ok, included with the MSDN account as nothing is ever really free). This also applies to Microsoft Partners – some storage is included with your membership at all levels.

This could also be a good stepping stone for shops that already have good in-house backup strategies in place, but no off-site capabilities to just occasionally push a copy-only backup to Azure as added protection against an internal failure – or to just share a database with another office or development team. Make sure you are aware of all your legal requirements pertaining to your backups BEFORE you start pushing them out to Azure (consider HIPPA, SOX and PCI compliance issues).

I would strongly recommend implementing Transparent Data Encryption (TDE) on your database before pushing it to Azure (call me cautious) – keeping your certificate & key backups safely in a different place.

Obviously this is only a preview of what the SQL and Azure Teams can with Azure blog storage – and I assume SP2 will bring more tools to make the integration more robust.

About these ads
Categories: Azure, SQL Server, T-SQL
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: