Microsoft’s own Windows Azure cloud is one of the most popular backup options for storage of SQL Server backups. It is especially attractive as an offsite storage medium for SQL Server backups because it is a Microsoft product and is already has tie-ins with the latest versions of SQL Server, beginning with 2012 Service Pack 1, Cumulative Update 2. Let’s take a closer look at how to save your SQL Server backups to Azure.
Requirements for Azure Storage Backup
Before you can start saving your SQL Server backups to the Azure cloud, you first require a subscription. You can use your Microsoft account to log in, or if you don’t have one can sign up for a new account. Azure offers a free 90-day trial with no obligation to convert to a paid membership – this is a painless way to try out Azure.
Beyond the 90-day free trial period, Azure requires a paid membership. You pay by entering your credit card details. Azure has garnered praise as one of the cloud providers with a simple, straightforward pricing structure, the fact that users only pay for what they use, its per-minute billing, and no upfront or termination fees. See more details about Azure in general and its pricing structure specifically for SQL Server databases.
Next, since you are primarily interested in using Azure as a storage medium, you need to create an Azure storage account. Instructions for doing this can be found on the Microsoft MSDN site. Once this is complete you will also be granted access keys for logging to your Azure storage account and creating containers.
The next step is to create a container. Containers are virtual objects used to store files uploaded to your Azure account. Set your container’s Access Type parameter to ‘private’. This page contains instructions on how to create a container. Note down or copy the URL of your container – you will require this later.
Finally, you need a credential to access your Azure account. Log in to SQL Server Management Studio (SSMS), expand Security, then right-click on Credentials and select ‘New Credential’. This credential should preferably have the same name as your Azure container. In the ‘Identity’ field, enter the name of your storage account, and in the ‘Password’ field enter the access key from your Azure account. Alternatively, you can create a credential using a SQL command with this format:
CREATE CREDENTIAL myCredential WITH IDENTITY = 'your_Azure_storage_account', SECRET = 'your_Azure_access_key';
How to backup to Azure
Now that we are done with the prerequisite requirements, we get to the interesting part – how to actually save your backups in Azure. To do this, use the TO URL option of the BACKUP command. This will generally take the format:
BACKUP DATABASE database_name TO URL = ‘http://your_Azure_container_URL’ WITH CREDENTIAL = ‘your_Azure_credential’, STATS = 5
After this, you can then programmatically create a SQL Server script or Windows job to automate the transfer of your backups to Azure. For notifications that the backups and transfers to Azure are successful, you can also configure the Sendmail functionality on SSMS.
If you have multiple databases to back up you can either do it manually for each database or follow these instructions to configure and use Powershell to automate the process.
An easier way to do all this
Hopefully, it is now clear how to create and store backups to Azure. However, if all this sounds somewhat complex (especially the automation part) then rest easy because there is a much simpler solution that is just as powerful and still achieves the same results. The SQLBackupAndFTP application enables you to easily configure your SQL Server environment to take backups of your SQL Server databases and automatically store them in your Azure storage account. To set up your databases to be automatically backed up to Azure in SQLBackupAndFTP, follow the steps below:
- To select all the databases you want to backup click the gear button at the “Select Databases” section.
- Click on the plus button at the “Store backups in selected destinations” and select Azure storage.
- Enter your Azure storage details – storage account name, access key, and container name. You can also add a specific path to upload to under Advanced settings.
4. Click Save & Close.
That’s it! You’re done. To automate the steps, such as taking daily backups at a specific time, you can also set up a job in SQLBackupAndFTP, and also choose to receive email notifications about your jobs. The complexity of manual setup in SSMS is reduced to a few steps when you use you use SQLBackupAndFTP.
Conclusion
Azure offers a secure, well-integrated storage solution for your SQL Server database backups. Azure setup is straightforward, but automation and notification of your backups to Azure is somewhat less so. SQLBackupAndFTP offers you a simple avenue to take advantage of Azure as your preferred backup destination.