Partilhar via

Azure SQL DW: Moving to a different region with restore from backup option

  • Artigo

Reviewers: Dimitri Furman, John Hoang, Mike Weiner, Denzil Ribeiro, Joe Yong

Background:

Azure SQL Data Warehouse service (SQL DW) uses a snapshot backup to back up your data at a regular (8 hour) interval. DBAs can use this backup to restore a SQL DW database into a new database in the same region, or to a paired region at a different geographical location. For details refer to this documentation. As an example, if you have an Azure SQL DW in West US, your paired region is East US. You can learn about the Region pairs here.

While you can use the backup to restore to a paired region, what about the scenario of moving your SQL DW database to a remote non-paired region, as an example, let’s say East Asia? You can export the data out to the blob storage in a region of your choice from SQL DW, and then reload it into the new database in East Asia. This is very cumbersome if you have hundreds of tables, and could also be error prone. You will also have to re-create all the database objects in the new database.

Is there an easier way? I am going to talk about a little known, but easy solution that can help you do just that in the next section.

Solution:

The “Create new SQL Data Warehouse” workflow allows you to create a new database from a backup. Let’s explore if it allows me to create a SQL DW database from backup to a region of your choice!

1. In Azure Portal, click on the + sign and choose Databases, then SQL Data Warehouse.

clip_image001

2. Enter a database name.
3. Create a new resource group or use an existing one.
4. From Select Source choose “Backup”. This will take you to a blade where you can see all your other SQL DW databases. Choose the one that you want to migrate to another region.

clip_image003

5. On the Server blade, create a new one in a location of your choice.

clip_image004

6. Back on the SQL Data Warehouse blade, click on Create.
7. Wait for it to get done.

How fast the backup is restored to a different region will depend on data size. The bigger the database, the longer it will take. As an example, a 1.7TB database was restored using the above method in 30 minutes.

Conclusion

For this process to work, your source SQL DW database must exist. It won’t work for a deleted SQL DW database. Once the new SQL DW database comes online, you can login and run some deterministic queries to make sure you have the exact data. While this restore is going on, if you are loading data to the source SQL DW, you may want to run your differential loads to the newly restored database and reconcile the differences since the restore. Once satisfied, you have the choice to keep both the SQL DW up and running or pause the source SQL DW database to save on cost, ultimately dropping the database at a future date.

Comments

  • Anonymous
    December 21, 2016
    The comment has been removed
    • Anonymous
      November 13, 2017
      Sorry for the late reply. I didn't see this question before today. I am sure you have figured out already that you can move SQL/DW/VMs from one subscription to another.