Failure to create database using DACPAC file and Powershell

Question

I'm deploying a dacpac file onto a SQLAzure database using the following Powershell script

 

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Sdk.Sfc")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Dac")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection("dbinstance.db.net", "uid", "pwd")
$sqlStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($serverConnection.SqlConnectionObject)

$sqlStoreConnection.Connect()

$newDacStore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($sqlStoreConnection)
$dacpacPath = "C:\Database\Database.dacpac"
$fileStream = [System.IO.File]::Open($dacpacPath, [System.IO.FileMode]::OpenOrCreate)
$dacType = [Microsoft.SqlServer.Management.Dac.DacType]::Load($fileStream)

$newDacStore.add_DacActionStarted({Write-Host `n`nStarting at $(get-date) :: $_.Description})
$newDacStore.add_DacActionFinished({Write-Host Completed at $(get-date) :: $_.Description})

$dacName = "TestDB"
$evaluateTSPolicy = $true
$deployProperties = New-Object Microsoft.SqlServer.Management.Dac.DatabaseDeploymentProperties($serverconnection,$dacName)

$newDacStore.Install($dacType, $deployProperties, $evaluateTSPolicy)

$fileStream.Close()

 

It runs fine until the Install when it throws the following:

 

Starting at 24/08/2011 16:50:37 :: Preparing DAC metadata in the SQL Server instance 'dbinstance'

Completed at 24/08/2011 16:50:37 :: Preparing DAC metadata in the SQL Server instance 'dbinstance'

Starting at 24/08/2011 16:50:38 :: Preparing deployment script

Completed at 24/08/2011 16:50:39 :: Preparing deployment script

Starting at 24/08/2011 16:50:39 :: Creating database 'TestDB'

Completed at 24/08/2011 16:50:41 :: Creating database 'TestDB'

Starting at 24/08/2011 16:50:46 :: Creating schema objects in database 'TestDB'

Completed at 24/08/2011 16:52:41 :: Creating schema objects in database 'TestDB'

Starting at 24/08/2011 17:02:05 :: Registering the DAC in DAC metadata.

Completed at 24/08/2011 17:02:09 :: Registering the DAC in DAC metadata.

Starting at 24/08/2011 17:02:09 :: Creating database 'TestDB'

Completed at 24/08/2011 17:02:09 :: Creating database 'TestDB'

Exception calling "Install" with "3" argument(s): "Unable to install DacInstance. Please verify the

 components of the application."

At C:\Database\DeployDatabase.ps1:53 char:21

+ $newDacStore.Install <<<< ($dacType, $deployProperties, $evaluateTSPolicy)

    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : DotNetMethodException

 

 

Looking at SSMS, I can see that the database has been registered (Management > Data-tier Applications), but no database is created.   

I'm at a loss as to what is happening and the error message is a bit vague.

Anyone with any ideas?

---

Sunil

Answer 1

Hi Sunil,

If you deploy the DAC to SQL Azure using package using SQL Server 2008 R2 Management Studio, do you get the same error?

We cannot register a DAC if the database contains objects that are not supported in a DAC. Please also check if there is any SQL Azure unsupported feature used in the DAC.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 2

Hello Jian,

I should have clarified my initial message:  I can deploy the dacpac to SQL Azure correctly from SSMS on my local box - I can even run the script and deploy.  But from my deployment server, I am getting the problem.

I suspect that it is due to configuration differences between the 2 machines; it's a Win Server 2008 box but only has SQL Server 2008.  

 

 

---

Sunil

Answer 3

Hello Sunil,

1. Can we please get the full exception message for better insight into the issue here.

If you are running your script in a function, you can write a method like PrintException (below) and use it to trap inside your own method upfront as

Trap
{ 
  PrintException($_.Exception);   
  return; 
}

#
# Prints Complete Stack of a given exception
#
function PrintException([Exception]$exception)
{
    if($exception -eq $null) { return; }
   
    write-output $exception.Message`n
    while ( $exception.InnerException )   
    {   
        $exception = $exception.InnerException   
        write-output $exception.Message`n
    };          
}

If you are executing the script as-is, please retrieve the exception from $error[0].Exception.Message. You can recurse this as '$error[0].Exception.InnerException' etc. to print all the inner details.

2. Most possibly, I think there is a mismatch between your DAC framework bits and other dependent binaries. To be certain of using all the correct binaries, consider upgrading to the following from Denali CTP3 release (http://www.microsoft.com/download/en/details.aspx?id=26726) :
      a.   SQLSysClrTypes.msi
      b.   SharedManagementObjects.msi
      c.   DACFramework.msi
      d.   SqlDom.msi
      e.   TSqlLanguageService.msi

 

 

 

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 4

Hi Sunil,

Try upgrading the Management Studio to SQL Server 2008 R2 SP1 in your deployment server.

For July 2011 Service Release, you need this to connect properly without any issues.

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"

Answer 5

The firewall rules on SQL Azure may be blocking your PS script from the deployment server.

Answer 6

The link for SQL 2008 R2 SP1 is: http://www.microsoft.com/en-us/download/details.aspx?id=26728