在 Visual Studio 2013 中发布 DacPac [英] Publishing DacPacs in Visual Studio 2013

问题描述

我在 Visual Studio 2013 中有一个 SSDT 数据库项目.当将数据库更新发布到其他环境中的数据库时，它用作答卷".我最近看到了 Jamie Thompson 关于 DacPacs 的博客文章，他在这里写了一篇关于 DacPac 是什么以及如何使用它们的精彩总结.

I have an SSDT database project in Visual Studio 2013. This is used as the "answer sheet" when publishing database updates to a database in the other environments. I recently came across Jamie Thompson's blog article on DacPacs, where he writes a great summary on what DacPacs are, and how to use them.

现在，假设我有以下场景:

Now, say I have the following scenario:

1. VS2013 中的 SSDT 项目，版本 1.0.33
2. 我的开发环境中的数据库，版本 1.0.32
3. 我的 S-test 环境中的数据库，版本为 1.0.31

根据 Jamie 的说法，使用 DacPacs 发布数据库更改是幂等的，即我可以将第 1 项中的 SSDT 项目中的 DacPac 发布到第 3 项中的数据库，并且它将在两个版本 1.0 中完成对数据库的所有更改.32 和 1.033，因为 DacPac 包含有关整个 DB 架构的信息(其中还应包括在 1.0.32 版中所做的更改).

According to Jamie, publishing databases changes using DacPacs is idempotent, i.e. I can publish the DacPac from the SSDT project in bullet 1 to the database in bullet 3, and it will get all the changes done to the database in both version 1.0.32 and 1.033 since the DacPac contains information about the entire DB schema (which then also should include changes done in version 1.0.32).

这是对发布 DacPac 的工作原理的正确理解吗?

Is this a correct understanding of how publishing a DacPac works?

推荐答案

是的，一旦您以声明方式在 DACPAC 中定义了模型，您就可以将模型部署到具有任何版本数据库的任何目标环境.引擎会根据目标自动生成合适的变更脚本.

Yes, once you defined your model in a DACPAC in a declarative way, you can then deploy your model to any target environment with whatever version of you database. The engine will automatically generate the proper change scripts according to the target.

您可以使用 SqlPackage.exe 实用程序从 Visual Studio 或命令行部署(发布)您的模型.这是使用 SqlPackage.exe 和发布配置文件的 PowerShell 脚本示例.您可以选择直接发布或生成更改脚本(设置 $action 变量).DACPAC 文件和发布配置文件必须在 ps 文件的同一文件夹中.将生成一个日志文件:

You can deploy (publish) your model from Visual Studio or from command line using the SqlPackage.exe utility. Here an example of a PowerShell script that use SqlPackage.exe and a Publish Profile file. You can choose to publish directly or generate the change script (set the $action variable). The DACPAC file and the Publish Profile file have to be in the same folder of the ps file. A log file will be generated:

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

####################################
$action                 = 'Publish' #Only generate script: 'Script'; Publish directly: 'Publish'

$databaseName       = 'Test'
$serverName         = 'localhost'
$dacpacPath         = Join-Path $scriptPath '.\Test\bin\Debug\Test.dacpac'
$publishProfilePath = Join-Path $scriptPath '.\Test\Scripts\Publish\Test.publish.xml'


$outputChangeScriptPath = Join-Path $scriptPath 'TestDeploymentScript.sql'

$logPath = Join-Path $scriptPath 'TestDeployment.log'
####################################



$sqlPackageExe = 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe'

if ($action.ToUpper() -eq 'SCRIPT')
{

    Write-Host '********************************' | Tee-Object -File "$logPath"
    Write-Host '*  Database Objects Scripting  *' | Tee-Object -File "$logPath"
    Write-Host '********************************' | Tee-Object -File "$logPath"

    $args = "/Action:Script /TargetDatabaseName:$databaseName /TargetServerName:$serverName " +
            "/SourceFile:""$dacpacPath"" /Profile:""$publishProfilePath"" /OutputPath:""$outputChangeScriptPath"" "

    $command = "& ""{0}"" {1}" -F $sqlPackageExe, $args

    Invoke-Expression $command | Tee-Object -File "$logPath"

    if($LASTEXITCODE -ne 0)
    {
        $commandExitCode = $LASTEXITCODE 
        $Error[0] | Tee-Object -File $outputChangeScriptPath
        return $commandExitCode
    }

}

if ($action.ToUpper() -eq 'PUBLISH')
{
    # DWH
    Write-Host '*********************************' | Tee-Object -File "$logPath"
    Write-Host '*  Database Objects Deployment  *' | Tee-Object -File "$logPath"
    Write-Host '*********************************' | Tee-Object -File "$logPath"

    $args = "/Action:Publish /TargetDatabaseName:$databaseName /TargetServerName:$serverName " +
            "/SourceFile:""$dacpacPath"" /Profile:""$publishProfilePath"" "

    $command = "& ""{0}"" {1}" -F $sqlPackageExe, $args

    Invoke-Expression $command | Tee-Object -File "$logPath"

    if($LASTEXITCODE -ne 0)
    {
        $commandExitCode = $LASTEXITCODE 
        $Error[0] | Tee-Object -File $outputChangeScriptPath
        return $commandExitCode
    }
}

这篇关于在 Visual Studio 2013 中发布 DacPac的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！