After using the beta for couple of weeks I am really impressed. Everything is easy, UI is intuitive and you don’t need lots of configuration to make this work. You can setup basic daily backup work in just matter of minutes. If you got Azure storage or Amazon S3 account ready. If not then it still does not take long. You spend most of the time digging up usernames and passwords not figuring out how things work! Splendid.
So let’s go through how to do daily Azure SQL backup into Amazon S3 storage.
Red-Gate Cloud Service Dashboard is really clean and simple. Just select the action you want to perform.
First after registering to Red-Gate Cloud Services and login select “Backup SQL Azure to Amazon S3” from Dashboard.
Just fill in the Azure SQL credentials and Amazon S3 keys.
Secondly just fill in Azure SQL server name xxxxxxx..database.windows.net, login credentials to that DB and press refresh button next to Database drop down. Select database that you want to backup. Next click on the “AWS Security Credentials” link and login to your Amazon AWS account. You will be taken directly to place where you can find the keys.
"Access key id" goes to "AWS Access Key" and "Secret Access Key" is shown after pressing "show" link in Amazon AWS.
Note that you need S3 Bucket. If you don’t have one you can create it from here https://console.aws.amazon.com/s3/home I will not explain how that is done, but it is really easy. After you have inserted Amazon keys you can press the refresh button next to Bucket dropdown in Red-Gate UI. Just fill in the name that you want your backup file to have. Don’t worry about the day stamp because this tool will add date at the end of the filename. Press “continue” button to select scheduling options or backup now.
Just select when you want your daily backup to run or just press "Backup now" button.
Next just fill in exact time you want your backup in 24h format, select time zone, select week days when to run this and press schedule button. You can also just press backup now button for one time backup. Note that there is also a monthly backup option.
You should see your scheduled backup in next screen. When your first backup is done it appears here under history header.
Next you are taken into schedules and history view. Here you can view details of the upcoming backup operation, cancel it or invocate backup now. Just move your mouse over the upcoming event.
Just move mouse on top of scheduled backup job and you can cancel job or invocate it to happen right now.
When backup job has been successfully completed you will receive an email and a “log” line will appear in “schedules and backup history”.
If job is successful or not it will appear here under history title
You can view details of executed jobs by moving mouse on top of history line. If you backup job has run into errors you will see an error triangle as well as receive error message via email. Email also contains a direct link to that specific History log line. Neat!
- If you backup job has exceptions it has warning icon in history. Details link will contain error message of what went wrong.
Clicking on the link on the email will direct you to same view as clicking “details” next to completed job.
Here you can read throught what happened while executing backup. If you ran into errors they are also present. Just scroll down from the bar.
If you got your backup run cleanly you should see the created pacbac file in Amazon S3 bucket.
Backup file is safe and sound in Amazon S3
Note that you can just as easily use Azure Storage services or use FTP to upload the backup to you own backup architecture.
There are couple of missing features that I would like to see. For example similar choice than in Red-Gate backup tool to first create copy of database (for transactional safety) and ability to encrypt the backup file before it is uploaded into Amazon or Azure storages. But event without these small features this is still awesome!
So you have your application running on Azure. Some next steps would be beneficial to have. Well maybe, but you need to first figure out answers to a couple of important things.
As of now Azure does not provide any solutions of the shelf. You need to build those by yourself. I will describe one possible setup that will provide you some functionality for the points listed above. Being a Cloud Man that I am, this setup is also running entirely on the Cloud 🙂
Firstly, the setup. In order to get things up and running you will need the following tools and accounts:
- Amazon EC2 account with Windows 2008 and Amazon monitoring service.
- Windows 2008 server (with SQL Server 2008 R2 client utilities) for Azure backup.
- SQL 2008 RC2 server for Azure SQL backup.
- Azure Management Cmdlets tools for Azure Storage backup.
- Red-Gate SQL Comparison Bundle version 9 or above for Azure SQL backup.
- AzureWatch account for Autoscaling and monitoring Azure web and worker roles.
- (Optionally) Dropbox with Packrat service so you will get unlimited undo history on your backups.
The overall picture of this setup will look something like in the picture below. You could replace Amazon EC2 Windows 2008 server + SQL 2008 RC2 with Azure VM role, or your own hosted server and Dropbox with Azure storage account. By replacing Amazon with Azure WM role you will gain savings on the data transfer fees and the steps should be fairly similar. If you decide to do that I would recommend having those under different Azure subscription so that one Administrator cannot delete your backups and your service!
High level picture of monitored Azure app with backup and autoscale.
In addition, make sure that Windows 2008 Server has at least SQL Server 2008 R2 installed especially bcp.exe version 10.50.1600.1. That is because bcp.exe utility is used to perform database backups and older versions had nasty bug that prevented backup from working.
If you have several services running on one Azure subscription it is useful to direct their logs into one shared storage account. This is because AzureWatch can monitor only one log account per subscription.
I don’t go to all the details because steps those are quite obvious. If you get confused with my quick instructions, please send me an email and I will add more details into this blog post.
- Get Amazon account and launch Windows 2008 server with SQL 2008 RC2 server or Make VM role for Azure.
- Install SQL Server 2008 R2 client utilities
- Install Dropbox
- Install Azure Management Cmdlets
- Install Red-Gate SQL Comparison Bundle
- Get AzureWatch account and install the control panel to 2008 server.
You should also automate running of these PowerShell scripts with Windows 2008 Server Scheduler:
- Remember that you need to give full path to PowerShell script in Task arguments like this:
-noninteractive -nologo -command “&”c:\my backup scripts\backup.ps1″”
- Modify SQL backup script from Mike Mooneys blog to suite your needs. Make sure that zip files are stored in folder that is Dropbox synchronized.
- Modify backup sample scripts of Azure Management Cmdlets to suite your needs. Samples can be found from installation folder. You can direct backup to Azure storage account or to Dropbox folder.
- Happy Clouding!