Post-Deployment-Script running very slow migrating from SQL Server 2008 R2 towards SQL Azure

Question

Hi everybody.

We are trying to make the appropriate changes to our web application for having it running both on Windows Azure and in an On-Premises context.

In particular, we want to deploy a DACPAC package and its post-deployment-script at runtime.

Following the sample code found on the SQL DAC Example frome Codeplex, we have written this code for the Deploy action:

            try
            {
                SqlConnection.ClearAllPools();
                using (var Conn = new SqlConnection(_ConnectionString))
                {
                    //Set up the connection
                    _Connection = new Microsoft.SqlServer.Management.Common.ServerConnection(Conn);
                    _Store = new DacStore(_Connection);
 
                    //Load the dacpac
                    DacType dType;
                    using (var stream = System.IO.File.OpenRead(_FileName))
                    {
                        dType = DacType.Load(stream, false);
                    }
 
                    //Configure deployment properties
                    var properties = GetDatabaseDeploymentProperties(_Connection, dbName);
 
                    //Subscribe the event handles
                    this.EventSubscribe(_Store);
 
                    //Install the dacpac
                    _Store.Install(dType, properties, true);
 
                    //Load and run post-deployment scripts
                    var Scripts = dType.MiscellaneousFiles;
                    foreach (IExtraSource script in Scripts)
                    {
                        if (script.Tags.Contains("PostDeploy"))
                        {
                            var content = UTF8Encoding.UTF8.GetString(script.GetContents());
                            _Connection.SqlConnectionObject.ChangeDatabase(dbName);
                            _Connection.AutoDisconnectMode = Microsoft.SqlServer.Management.Common.AutoDisconnectMode.DisconnectIfPooled;
                            _Connection.ExecuteNonQuery(content);
                            _Connection.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteSql;
                        }
                    }
                }
            }
            catch (Microsoft.SqlServer.Management.Common.SqlServerManagementException ex)
            {
                throw new DbProvisionException(String.Format("Errore in Deploy: {0}", ex.Message), ex);
            }
            catch (Microsoft.SqlServer.Management.Common.ExecutionFailureException ex)
            {
                throw new DbProvisionException(String.Format("Errore in Deploy: {0}", ex.Message), ex);
            }
            catch (Microsoft.SqlServer.Management.Common.ConnectionException ex)
            {
                throw new DbProvisionException(String.Format("Errore in Deploy: {0}", ex.Message), ex);
            }
            finally
            {
                //unsubscribe event handlers
                this.EventUnsubscribe(_Store);
                if (_Connection != null) _Connection.Disconnect();
            }

This method runs well inside the On-Premises scenario referencing these libraries:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Dac.dll
Microsoft.SqlServer.Management.DacEnum.dll
Microsoft.SqlServer.Management.DacSerialization.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll

with version 10.0.0.0__89845dcd8080cc91

Things change dramatically using the new version 11.0.0.0:

we are experiencing an extremely long time needed for the execution of the post-deployment-script
about 5 to 10 minutes to run a script that actually needs a few seconds on previous version.

Am i doing something wrong?

Any help would be really appreciated.
Thanks.

Answer 1

Hello

I'm experiencing another strange behaviour:

It randomly happens that the execution is fast (10 secs against 10 minutes).

Whe I say "randomly" I mean that it sometimes runs fast having tryed to vary theese factors:

• StatementTimeout set at 600, 10, 0, 1
• Restart of the SQL Server Instance

But most of the time it lags.

Thanks again.

Answer 2

Hi everybody.

It seems that we finally managed to solve this issue.

After several hours in searching for a solution I've discovered a critical misundestanding:

In the download page the libraries are reported with two different target cpu version. x86 and x64. 

My development workstation has Windows 7 x64 with an Intel I3 CPU so at the beginning I adopted the x64 version of the libraries, but checking into the GAC I realized that those assemblies (v.11.0.0.0) figured as A64 architecture, instead of the previous version (v.10.0.0.0) that figured as MSIL architecture. This caused my application to crash with the classic "the assembly xxx is missing" message.

Then I downloaded and installed the x86 version and this time the application did not crashed anymore but I experienced the slow script execution.

At the end I removed all the x64 version, that were probably conficting with the others, and the issue is solved.