Goran Žuri

Welcome to my part of the internet. My name is Goran Žuri and I'm a software enineer and a technology enthusiast. I also love snowboarding, biking, wakeboarding and other sports so feel free to join in.

Saturday, 3 October 2015

Migration from Azure SQL

After almost for 2 years we have decided to move our main SQL database from Azure SQL to our data center. The decision was made for security purposes mostly, we started to have sensitive customer data in our SQL and it was unacceptable that all that data was in the cloud, also German laws prohibit storing any customer information outside Germany. To be fair we had a pretty good experience with Azure SQL service. The biggest pro would be that you have all of the technologies at your disposal without any maintenance or admin work, but the big con was when some Azure services go down (and they do!) you are powerless. We had all kinds of failures as Traffic manager went down, Websites in multiple regions at once, etc. Also one thing to keep in mind if you are using Cloud services for important project have offline independent backup service, a lot can happen.

Migrating Azure SQL

Exporting Azure SQL is fairly simple and I prefer to do it using SQL Management studio. Also I don't need the Transaction Logs and History so using .bacpac is much more convenient for me. If you need Transaction Logs and History you need to find some other method for migration.
  1. Connect to your Azure SQL server
  2. Right click on the database you want to export 
  3. Go to Tasks -> Export Data-tier Application
  4. Wizard will guide through the rest of the process, and create a .bacpac file that is the full database backup with structure and all the data
  5. On your second server just reverse the process by using Task -> Import Data-tier Application

When it doesn't work

As I stated before sometimes The Cloud just doesn't work, and it always happens in the worst possible moment. My scenario began when EF Migrations failed to update the database schema but somehow put the new record in Migrations table. I needed to restore rollback or restore the old copy of the database. 

For the failed attempts I tried:
  • Azure point in time restore, it failed with a generic message that the action cannot be completed?!
  • Export function of the current database (I needed it to sync the data later) failed in the same matter
  • Exporting using the previously described method got stuck on trying to export database schema
Since it was a middle of the work day with high traffic I was pretty desperate so the only thing that did work was to copy the database using the old Azure portal.

Posted by at
Labels: ,

1 comment: