Why does a SQL Azure DACPAC upgrade (via a PowerShell script) consistently take 30min to complete

Question

I created a PowerShell script to upgrade a SQL Azure instance with my latest DACPAC (taken from http://msdn.microsoft.com/en-us/library/ee634742.aspx).

What I have experienced when running my PowerShell script is that it consistently takes approximately 30min to execute. The script is idle for almost half an hour waiting on $dacstore.IncrementalUpgrade($dacName, $dacType, $upgradeProperties) to return from execution and nothing is printed out on the PowerShell console window. Only right at the end of the half hour does the incremental update start spitting out console messages which inform me that the upgrade is taking place (essentially it appears that the script has hung for 30min until it finally comes back alive and the script does this consistently every time).

Does it usually take this long for the IncrementalUpgrade to complete and is there supposed to be a 30min period of inactivity/waiting?

Note that I am running the PowerShell script from my local machine which is external to the Azure network.

Thanks for any insight you can give for this, I am hoping that I can reduce this incremental upgrade process to substantially less than 30min so that my continuous integration build doesn't take so long.

Answer 1

According to Microsoft Support this is a known issue and will be fixed in SQL Server 2012 (code named Denali). Here are the details from Microsoft Support:

It’s a known issue that using SSMS 2008 or PowerShell to update DAC on SqlAzure is very slow. SQLServer 2008 utilize old extraction engine which run query for every column and small object. This way works well at on-premise server, and meets SQLServer 2008 original design target. However, when managing the SqlAzure database, the query need be transferred over internet, network latency makes the old extraction becomes inefficient, especially, when network is not good.

Our SQL product team aware this issue and designed new extraction engine to fix it. The new engine is integrated in SQL Server 2012 (code name Denali). Unfortunately, some of the engine behavior may bring break changes to SQL Server 2008. We try different approach but we can’t relief regression barrier when apply the new engine in the SQL server 2008. Therefore, we don’t have plan to deliver the new extraction engine as hotfix on SQLServer 2008 so far. That will impact the current on-premise users and operation.

Further details about how I architected the PowerShell script with a continuous integration (CI) process can be found here.