SQL Azure Management - Backup and Monitoring

SQL Azure Backup and Restore
SQL Azure does not support the normal SQL Server BACKUP commands.

It does provide inherent data redundancy and reliability. However, this will not help if:

• There is an app bug which corrupts your data.
• There's a hacker who destroys your data
• The Microsoft's datacenter loses all of your data (unlikely but possible)

To cope with these scenarios, there are two mechanisms currently available to backup your data:

• You can use the SQL Azure copy commands to copy your database contents to a second, still stored within SQL Azure - this will protect again only the first problem above (assuming the hacker has a way into your Azure store)
• You can use the open source SQL Azure Migration Wizard to download data to a local copy - this will provide a recovery mechanism for all 3 items above

The use of this second tool is discussed further below:

SQL Azure Data Migration

Warning - using this for a backup process will involve a large amount of data transfer (which will cost money), but it's better than not having a backup at all.

Backup

To use this tool:

1. download it from http://sqlazuremw.codeplex.com/
2. choose "Analyze and migrate T-SQL database"
3. choose the Azure database - yourserver.database.windows.net - username - username@yourserver - and password.
4. Note that you can only connect to the SQL azure database from IP addresses whitelisted on the firewall settings on the SQL Azure web control panel
5. choose "[databaseName]"
6. choose "script all database objects"
7. hit "next" and it will slowly happen
8. choose your local SQL server to connect to
9. create a new SQL server database
10. hit "next" to insert the data into your local database

For restore, you need to go in the opposite direction

Restore

To do this:

1. typically suspend the Azure web application
2. use the Azure web user interface to drop the existing SQL Azure database
3. run the steps above in reverse - from local to SQL Azure
4. restart the Azure web application.

Notes:

• - dependent on the technical level of the user - or if there's an automation server available, this backup process can be scripted using command line arguments - see the documentation for batch mode - http://sqlazuremw.codeplex.com/releases/view/32334#DownloadId=125697
• - this backup process should perhaps be combined with copy to ensure that the data downloaded is transactionally consistent (http://www.azuresupport.com/2010/08/sql-azure-backup-database-copy/). To do this:

1. connect to SQL Azure using SQL Server Management Studio
2. open a "New Query"
3. type "CREATE DATABASE backupName AS COPY OF mainName"
4. Hit Execute
   
   The state of the copy can then be monitored using "select name, state, state_desc from sys.databases where name = 'backupName"
   

Note that costs of running multiple databases on Azure suggest that you should not keep taking more and more backups and storing them all within SQL Azure.

SQL Azure Monitoring
There really isn't much that can currently be done in the way of monitoring the health of an Azure SQL Server.

This http://msdn.microsoft.com/en-us/library/ff394114.aspx document describes the ability to query:

• Database size
• Number of connections - and their activity
• Some simple query performance

However, this doesn't really provide any clue as to monitoring the database "health" - e.g. uptime/status, loading, etc.

There is some access to overall status at http://www.microsoft.com/windowsazure/support/status/servicedashboard.aspx - but this is at a very coarse level.

The only current suggestions for checking a SQL server instance is alive are:

• just rely on Microsoft uptime (under their SLA) - not ideal
• use tools like Nagios to add "HTTP" monitoring to Azure applications connected to the SQL Server database - use this web monitoring to check the health of the server (at least to check it is alive).
• try to build something (e.g. a web app) based around sys.dm_exec_connections (http://msdn.microsoft.com/en-us/library/ff394114.aspx) which would provide some analysis of loading.
• try to extend the enzo SQL library - http://enzosqlbaseline.codeplex.com/ - it's benchmarking tools might give you some clue on current loading (but this monitoring might also impact your current performance).