Random Ramblings About Making Games and Stuff from Cloud

Posts tagged ‘AzureWatch’

Azure SQL Backup and restore scenarios using bacpac export/import

What Azure SQL was missing was a proper supported way to make backups for disaster scenarios. Those scenarios would include loss of control over Azure SQL server or human error causing SQL admin to delete whole Azure SQL server. Great news everyone Azure SQL now has tools to mitigate the impact of these scenarios. SQL Azure Import/Export Service CTP is now available. More details on how this works can be found here.

What do you need?

You need means of scheduling backup, Azure Storage account where to put bacpac files, means to get the exact url of bacpac file and a Azure SQL account to backup.

Azure Account

You need one Azure account with Azure SQL and Storage server of course. šŸ™‚

You might want to have separate backup storage account because, if you cannot access your production account you still have access to your backups. I personally download bacpac backups from Azure to local server.

Bacpac file export tool

In this example I will use Red-gate backup tool. This is because this tool allows you to easily make a database copy of your database before the backup. This will allow you to make transactionally safe backups.

But you can also use DAC SQL Azure Import Export Service Client V 1.2. In this tool you need to make a database copy using for example Cerebrata cmdlets.

Azure Storage account browser

You can use any tool you like. In this example I will use Azure Storage ExplorerĀ because it is free šŸ™‚

Windows server

Ideally this would be dedicated windows 2008 server so that you can be sure that it runs smoothly. You can also download exported bacpac files to this server. Just to be safe šŸ™‚

Backup using Red-Gate command line backup tool

Here is a nice how to video how to setup scripts using Red-Gate tool.Ā NOTE that in this video script will make a backup into different database. What we want to do is schedule a bacpac file generation. If you want to test how bacpac file generation works without command line watch this video.

But back to business: your scheduled script should look something like this:

RedGate.SQLAzureBackupCommandLine.exe Ā /AzureServer:[url_to_azure_server] Ā AzureDatabase:[databasename] /AzureUserName:[db_owner_username] /AzurePassword:[password] /CreateCopy /StorageAccount:[Azure_account_name] /AccessKey:[primary_or_secondary_azure_storage_key] Ā /Container:[container_name_in_storage] /Filename:[filename_of_bacpac]

Notice that I did not use any real values in above script. Just fill in the parameter between [ ] and schedule this script to as often you like. Note that you need to change the file name on every run because over writing with a same file name is does not work. I use date+time combinations.

So now we are ready for disasters šŸ˜‰ Next I will explain how to perform a restore to totally new Azure SQL server.

Restore using Windows Azure management portal

When you need to restore a database to new server you need to have access to bacpac backup file in Azure storage account.

  • Firstly create new Azure SQL server. How to video of that is here.
  • Secondly and this is important add same database logins that the backedup database had. Azure SQL user management is explained in detail here.
  • Thirdly get url of the pacbac file using Azure Storage Explorer. Open Azure Storage Explorer and login to storage account containing the bacpac file. Select the bacpac file and press view button. That is explained in the video of next step.
  • Fourthly you are ready to restore! It can be done like explained inĀ this video.

If you have any comments or questions please don’t hesitate to ask.

Thanks for reading and happy backupping!

Advertisements

I have my app in the Azure. Now what?

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.

  • Backup
  • Monitoring
  • Autoscaling

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 šŸ™‚

Setup

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!

Azure setup

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.

InstallationĀ instructions

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.

  1. Get Amazon account and launch Windows 2008 server with SQL 2008 RC2 server or Make VM role for Azure.
  2. Install SQL Server 2008 R2 client utilities
  3. Install Dropbox
  4. Install Azure Management Cmdlets
  5. Install Red-Gate SQL Comparison Bundle
  6. Get AzureWatch account and install the control panel to 2008 server.

Scripts

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.

That’s it!

Happy Clouding!
%d bloggers like this: