Search code examples
sql-serversql-server-data-toolsdac

Dacpac Upgrade database Times a little silly


Edit: Updated to state it isn't hanging, just takes AGES!

I'm trying to update an existing sql server database using a dacpac.

I can create a new SQL server database with the (stripped down) example below in 30 seconds. The issue I'm having is that using the same dacpac, rerunning the procedure (so it is updating an existing database rather than creating afresh) takes 20 minutes.

Is this kind if time difference what is to be expected? Having used redgate's SqlCompare comprehensively, I'm finding the time unpaletable.

The third param of the deploy method is UpgradeExisting which I'm setting to true - Is this all I need to do or am I missing something??

void Deploy(string TargetConnectionString, string TargetDatabaseName, string pathToSourceDACPAC)
{

    DacServices dacServices = new DacServices(TargetConnectionString);

    //Set up message and progress handlers
    dacServices.Message += new EventHandler<DacMessageEventArgs>(dbServices_Message);
    dacServices.ProgressChanged += new EventHandler<DacProgressEventArgs>(dbServices_ProgressChanged);

    //Load the DACPAC
    DacPackage dacpac = DacPackage.Load(pathToSourceDACPAC);

    //Set Deployment Options
    DacDeployOptions dacOptions = new DacDeployOptions();
    dacOptions.AllowIncompatiblePlatform = true;

    //Deploy the dacpac
    dacServices.Deploy(dacpac, TargetDatabaseName, true, dacOptions);

}

//Event handlers...
void dbServices_Message(object sender, DacMessageEventArgs e)
{
    OutputThis("DAC Message", e.Message.ToString());
}

void dbServices_ProgressChanged(object sender, DacProgressEventArgs e)
{
    OutputThis(e.Status.ToString(), e.Message.ToString());
}

NB the program disappears into the ether on the dacServices.Deploy line..


Solution

  • OK, the silly times were experienced while running through the debugger (VS2012). Once compiled the times were 25 or so seconds when chosing the Memory DacSchemaModelStorageType, or 45 or so seconds when chosing the File DacSchemaModelStorageType .

    I guess this just means that debugging is a pain in the whatsit!