Practicality of using MW for backups

Mar 14, 2010 at 6:51 PM
Hello, I have been experimenting with this tool and have found that I can successfully move small databases back and forth from SQL Azure. My question is: how practical would this approach be regularly pulling down from SQL Azure a database that is several GB in size? We need to retrieve our data regularly in order to import it into a reporting database and perform operations that we cannot do in the cloud. Is the migration wizard robust enough to tolerate an interruption in internet connectivity? Will it pick up where it left off? Can I run the wizard in a scripted fashion, such that I can pre-select both the source and destination servers as well as choosing the objects I want to migrate? Or do I have to use the GUI? Do I need to take my database off line in order to run the wizard? Will there be problems if I run it against a database that is currently being hit by my web application? Here is a general question about SQL Azure itself: Do I need to be concerned about doing regular backups of my database? Or does SQL Azure provide a way to restore or roll back to a previous known state? Thanks for any help or suggestions. Kevin
Coordinator
Mar 17, 2010 at 10:39 PM

Hi, 1. My question is: how practical would this approach be regularly pulling down from SQL Azure a database that is several GB in size? SQLAzureMW has retry logic built into it for uploading of schema and data. On the download, it has the retry logic built into it for schema, but not BCP. That said, I have downloaded a 10GB database (it just takes a long time). So, you can use SQLAzureMW to download your databases.

2. We need to retrieve our data regularly in order to import it into a reporting database and perform operations that we cannot do in the cloud. Is the migration wizard robust enough to tolerate an interruption in internet connectivity? The only place that SQLAzureMW cannot handle interruption is during BCP of data out of SQL Azure (upload is no problem).

3. Will it pick up where it left off? Upload to SQL Azure, Yes. Download from SQL Azure no.

4. Can I run the wizard in a scripted fashion, such that I can pre-select both the source and destination servers as well as choosing the objects I want to migrate? No

5. Or do I have to use the GUI? Yes

6. Do I need to take my database off line in order to run the wizard? No

7. Will there be problems if I run it against a database that is currently being hit by my web application? You might see a little performance hit since it is doing a BCP download of your data, but other than that it should not interfere with your web application.

8. Here is a general question about SQL Azure itself: Do I need to be concerned about doing regular backups of my database? SQL Azure has one primary and two secondary instances of your database running at all times. If the primary fails, then SQL Azure will switch to a secondary instance and start up a new instance. So, from a high availability standpoint, you don’t need to worry. BUT, that said, if you accidentally delete or modify your data, you will not have any way to recover once it is committed. So you would need implement a backup process if this is a potential problem for you.

9. Or does SQL Azure provide a way to restore or roll back to a previous known state? Not today. One of the things that you might want to check out is SQL Azure Data Sync. It will allow you to sync between SQL Azure and your local database so that you can do backups from your local database.

I hope this helps,
George