This is a guest post by Debra Shinder, also included in virtualization.info’s Tech Talks section.
Your SQL server database most likely holds some of your business’s most important mission-critical information, the loss of which could be catastrophic. You undoubtedly back up that data regularly – but if you’re keeping those backups on premises, a natural disaster such as a tornado or flood or explosion could still leave you in the lurch.
One big advantage of the cloud is that it makes it easier than ever to make and maintain off-premises backups that will be safe even in the face of such a calamity. In a Windows shop, Microsoft Azure is frequently the “cloud of choice.” Microsoft Azure can be the basis for your business continuity plan.
If you have a Microsoft Azure subscription with an Azure Storage account, you’re good to go, but there are a few things that you’ll need to do in order to enable backing up your SQL server databases to the Azure cloud. Blobs are one of the three types of data that you can store in Azure Storage. What you need to do now is log into the Azure Management Portal and create a Microsoft Azure Blob Storage container.
Now luckily, you don’t have to figure out how to copy the SQL data to your Azure Blob Storage container manually, because Microsoft provides tools that simplify the process. If you’re running Microsoft SQL Server 2014, you have it made. You can use the Managed Backup to Microsoft Azure tool in the SQL Server Management Studio.
This feature is based on the SQL Server Backup to URL technology that was introduced with SQL Server 2012 SP1. You can configure it to back up an individual database or the entire SQL Server instance. You’re able to restore data to a specific point in time as long as it’s inside of the retention period window that has been set.
Since security is always an issue, Microsoft has added support for encrypting the data when you create a backup, with several different encryption methods (e.g. AES 256) supported. Of course you need to back up the encryption key or certificate since the data can’t be restored without it. There is a simple box to check in the Back Up Database dialog box for enabling encryption and you can select the algorithm and certificate or asymmetric key from a drop-down box.
If you prefer the command line to the graphical user interface, you can use Transact-SQL statements or PowerShell to configure SQL Managed Backup to Windows Azure. You can find step-by-step instructions for configuration in this TechNet blog post.
The SQL Server Backup to Microsoft Azure Tool is your best friend if you’re running an older version of SQL Server. It’s a standalone tool that works with all supported versions of SQL Server and allows them to integrate with Azure Blob Storage. It supports encryption and compression for the older versions that don’t themselves support those features. You can install it on Windows 7 SP1, Windows 8/8.1, Server 2008 R2 SP1 and Windows Server 2012/2012 R2.
The tool provides a wizard that walks you through the steps of creating rules for processing the backup files. You’ll need to create a rule to define which SQL Server backup are to be uploaded to Windows Azure Storage and which Windows Azure Storage account and container to use. You can apply the rule to all paths on the local machine or you can specify a specific path, and you’ll need to specify the file name pattern (e.g. *.bak) to which the rule should apply.
Note that you’ll need the Storage Access Key, along with the account name and container, to upload backups to Windows Azure Storage. This video shows how to get the Storage Account access key from an Azure account.
You have two Storage Access Keys for your storage account, a primary one and a secondary one. The keys are used for authentication when you access your storage account. The secondary key is used when you need to temporarily share your key with others or for changing keys (regenerating new keys), as explained in this MSDN blog post.
In summary, backing up your SQL Server database to an Azure cloud can save you a lot of grief, and Microsoft provides multiple methods to make it easier to do. Of course, your SQL databases aren’t the only thing you can back up to Azure. For a detailed discussion of how to back up Windows Server 2012 R2 with Microsoft Azure Recovery Services, check out Richard Hicks’ article on the CloudComputingAdmin web site.