Recovering from a possibly failed scale-up

Question

For the purposes of familiarizing myself with the new Azure SQL tiers, I cloned a 7GB production database from a Business to a Standard S0 tier. This operation worked without issue, creating a ~3.5GB database (I guess it skips index creation?). I then fired up SSDT, added a new publish script, and published. This was a mistake, as my little S0 DTU utilization skyrocketed to 100%. I cancelled the SSDT publish.

While the SSDT publish was (slowly) cancelling, I scaled the server from S0 -> S2 to see what would happen. 15 minutes after clicking the scale button, my SSDT publish (which was still working on cancelling) was forcibly disconnected. For the last 45 minutes, the database has been reporting a "Scale operation in progress..." while showing 0 successful connections, 99.95% DTU percentage, and a constant database size of 3.56GB.

My questions are:

1. Is there any way to see the status of a scale operation beyond the simple "Scale operation in progress message?"

2. Is there a way to abort the operation, destructively or otherwise?

3. When running expensive operations (like creating 5GB of indexes), is the best practice to scale to an enterprise tier, run the operations, then scale back down to a normal tier?

4. When scaling, does the dashboard show resources used by the scaling process? Meaning that if I see 99% DTU utilization after initiating the scale, am I seeing resources being used by the scaling operation, or normal database activity only?

Answer 1

Re #1: There is a percentage progress column in sys.dm_operation_status however I'm not sure how accurate the value is for scale operations. You can query this view from the master database.

Re #2: There is not. The duration is depending on the size of the database. Some other factors can also play a role. If you have the impression that the operation is stuck, you should file a support ticked.

Re #3: If you chose a higher performance level the operation will finish in less time. Also you DB will be able to handle additional load to better.

Re #4: Resources used by the scale operation are not accounted included in the utilization data that you see as this is a system operation.

Answer 2

scale up / down is an online operation and you shouldn't see disconnections for longer duration. If you see you can open a support ticket with Microsoft. I don't read anywhere about cancelling the scale operation. Depends on resource usage you can scale up / down for doing maintenance operations. Table partitioning may be one approach to avoid rebuilding the whole table. SQL V11 can't support transactions larger than 2GB. You may want to upgrade to V12 for this scenario and many other features. AFAIK it shouldn't show the resources usage of scaling operation. Most likely this is from your SSDT pubslish activity