Let me begin with the moral of the story: Azure isn’t the answer for everything. The cloud is still new, and the closer you get to the bleeding edge, the more time you’ll be spending trying to work all of this stuff out.
We have a client who started with another consultant. Over the past few weeks, we’ve evaluated where they left things, and decided that the path the previous consultant took may not be the best long-range path. So we need to grab their database (which is an ERP database with about 50,000 tables spread across about 125 companies) from SQL Azure.
Thus began the saga.
The “easy and complete” way to do this is using SQL Server Management Studio. Select the database, go to Tasks > Export Data Tier Application… Fill in the blanks (where do you want the bacpac file? What tables to do you want? Then start the process. And Wait. And Wait. And Wait.
Eventually, you get a timeout error. When you look this up the first time, you’ll see an option to edit the registry and increase the timeout. That was two weeks ago, and I’ve forgotten the registry key, but Google the error and you’ll go right to it after a page or so of results.
Try again, and you might be lucky. I wasn’t. I even tried selecting only a few hundred tables. Same result. I increased the timeout. Same result. By the way, there were some 4-8 hour waits before the timeout occurred, so you’ll be spending a week or so with this solution.
After you try that a time or to, you’ll decide to go to SqlPackage, a command line utility that allows you to do the same thing, with more control.
Here’s what my final try of this command looked like:
sqlpackage.exe /a:Export /of:True /sdn:DATABASE_NAME /su:USER_NAME /sp:PASSWORD /ssn:asm-srv-azsql-01.database.windows.net /tf:c:DataMY_FILE_NAME.bacpac /p:CommandTimeout=6000
Yep, that’s a 6000 second timeout (default is 30).
Connecting to database ‘DATABASE_NAME’ on server ‘SUBDOMAIN.database.windows.net’
Unlike some of the other tries, this actually produced a (small 25K) file on my desktop. I let it run 72 hours before I stopped it, and stopped it when I noticed that nothing had been written to the file in 48 hours. Over the weekend, I tried again.
Here’s the message that was waiting on me this morning:
Messages from SQLPackage.exe when trying to export Azure SQL Database
Note that time elapsed. That’s 18 hours.. It seems like that’s where we came in.
And now to figure out another way to get the data…