SQL 如何使用 SSDT 和 Visual Studio 2012 数据库项目正确管理数据库部署？

How to properly manage database deployment with SSDT and Visual Studio 2012 Database Projects?

提问by Charles Josephs

I'm in the research phase trying to adopt 2012 Database Projects on an existing small project. I'm a C# developer, not a DBA, so I'm not particularly fluent with best practices. I've been searching google and stackoverflow for a few hours now but I still don't know how to handle some key deployment scenarios properly.

我正处于研究阶段，试图在现有的小项目上采用 2012 数据库项目。我是 C# 开发人员，而不是 DBA，所以我对最佳实践不是特别熟悉。我已经在 google 和 stackoverflow 上搜索了几个小时，但我仍然不知道如何正确处理一些关键的部署场景。

1) Over the course of several development cycles, how do I manage multiple versions of my database? If I have a client on v3 of my database and I want to upgrade them to v8, how do I manage this? We currently manage hand-crafted schema and data migration scripts for every version of our product. Do we still need to do this separately or is there something in the new paradigm that supports or replaces this?

1) 在几个开发周期的过程中，我如何管理我的数据库的多个版本？如果我的数据库 v3 上有一个客户端，我想将它们升级到 v8，我该如何管理？我们目前为我们产品的每个版本管理手工制作的架构和数据迁移脚本。我们是否仍然需要单独执行此操作，或者新范式中是否有支持或替换此功能的内容？

2) If the schema changes in such a way that requires data to be moved around, what is the best way to handle this? I assume some work goes in the Pre-Deployment script to preserve the data and then the Post-Deploy script puts it back in the right place. Is that the way of it or is there something better?

2）如果架构以需要移动数据的方式发生变化，那么处理这种情况的最佳方法是什么？我假设在 Pre-Deployment 脚本中进行了一些工作以保留数据，然后 Post-Deploy 脚本将其放回正确的位置。这是它的方式还是有更好的方法？

3) Any other advice or guidance on how best to work with these new technologies is also greately appreciated!

3) 也非常感谢有关如何最好地使用这些新技术的任何其他建议或指导！

UPDATE:My understanding of the problem has grown a little since I originally asked this question and while I came up with a workable solution, it wasn't quite the solution I was hoping for. Here's a rewording of my problem:

更新：自从我最初提出这个问题以来，我对这个问题的理解有所增加，虽然我想出了一个可行的解决方案，但这并不是我希望的解决方案。这是我的问题的改写：

The problem I'm having is purely data related. If I have a client on version 1 of my application and I want to upgrade them to version 5 of my application, I would have no problems doing so if their database had no data. I'd simply let SSDT intelligently compare schemas and migrate the database in one shot. Unfortunately clients have data so it's not that simple. Schema changes from version 1 of my application to version 2 to version 3 (etc) all impact data. My current strategy for managing data requires I maintain a script for each version upgrade (1 to 2, 2 to 3, etc). This prevents me from going straight from version 1 of my application to version 5 because I have no data migration script to go straight there. The prospect creating custom upgrade scripts for every client or managing upgrade scripts to go from every version to every greater version is exponentially unmanageable. What I was hoping was that there was some sort of strategy SSDT enables that makes managing the data side of things easier, maybe even as easy as the schema side of things. My recent experience with SSDT has not given me any hope of such a strategy existing but I would love to find out differently.

我遇到的问题纯粹是数据相关的。如果我的应用程序版本 1 上有一个客户端，并且我想将它们升级到我的应用程序版本 5，那么如果他们的数据库没有数据，我不会有任何问题。我只是让 SSDT 智能地比较模式并一次性迁移数据库。不幸的是，客户有数据，所以事情没那么简单。从我的应用程序的版本 1 到版本 2 到版本 3（等等）的架构更改都会影响数据。我当前管理数据的策略要求我为每个版本升级（1 到 2、2 到 3 等）维护一个脚本。这会阻止我从应用程序的第 1 版直接转到第 5 版，因为我没有数据迁移脚本可以直接转到第 5 版。为每个客户端创建自定义升级脚本或管理从每个版本到每个更高版本的升级脚本的潜在客户都难以管理。我所希望的是 SSDT 支持某种策略，使管理数据方面的事情变得更容易，甚至可能和模式方面一样简单。我最近使用 SSDT 的经验并没有让我对现有的这种策略抱有任何希望，但我很想找到不同的答案。

采纳答案by DevPrime

I've been working on this myself, and I can tell you it's not easy.

我自己一直在研究这个，我可以告诉你这并不容易。

First, to address the reply by JT - you cannot dismiss "versions", even with declarative updating mechanics that SSDT has. SSDT does a "pretty decent" job (provided you know all the switches and gotchas) of moving any source schema to any target schema, and it's true that this doesn't require verioning per se, but it has no idea how to manage "data motion" (at least not that i can see!). So, just like DBProj, you left to your own devices in Pre/Post scripts. Because the data motion scripts depend on a known start and end schema state, you cannot avoid versioning the DB. The "data motion" scripts, therefore, must be applied to a versioned snapshot of the schema, which means you cannot arbitrarily update a DB from v1 to v8 and expect the data motion scripts v2 to v8 to work (presumably, you wouldn't need a v1 data motion script).

首先，为了解决 JT 的回复 - 即使使用 SSDT 具有的声明性更新机制，您也不能忽略“版本”。SSDT 在将任何源模式移动到任何目标模式方面做得“相当不错”（前提是你知道所有的开关和陷阱），确实这本身不需要版本化，但它不知道如何管理“数据运动”（至少不是我能看到的！）。因此，就像 DBProj 一样，您可以在 Pre/Post 脚本中使用自己的设备。由于数据移动脚本依赖于已知的开始和结束模式状态，因此您无法避免对数据库进行版本控制。因此，“数据移动”脚本必须应用于模式的版本化快照，这意味着您不能随意将数据库从 v1 更新到 v8 并期望数据移动脚本 v2 到 v8 工作（大概，您不会

Sadly, I can't see any mechanism in SSDT publishing that allows me to handle this scenario in an integrated way. That means you'll have to add your own scafolding.

遗憾的是，我在 SSDT 发布中看不到任何允许我以集成方式处理这种情况的机制。这意味着您必须添加自己的脚手架。

The first trick is to track versions within the database (and SSDT project). I started using a trick in DBProj, and brought it over to SSDT, and after doing some research, it turns out that others are using this too. You can apply a DB Extended Property to the database itself (call it "BuildVersion" or "AppVersion" or something like that), and apply the version value to it. You can then capture this extended property in the SSDT project itself, and SSDT will add it as a script (you can then check the publish option that includes extended properties). I then use SQLCMD variables to identify the source and target versions being applied in the current pass. Once you identify the delta of versions between the source (project snapshot) and target (target db about to be updated), you can find all the snapshots that need to be applied. Sadly, this is tricky to do from insidethe SSDT deployment, and you'll probably have to move it to the build or deployment pipeline (we use TFS automated deployments and have custom actions to do this).

第一个技巧是跟踪数据库（和 SSDT 项目）中的版本。我开始在 DBProj 中使用一个技巧，并将其带到 SSDT，经过一些研究，结果其他人也在使用它。您可以将 DB 扩展属性应用于数据库本身（称为“BuildVersion”或“AppVersion”或类似名称），并将版本值应用于它。然后，您可以在 SSDT 项目本身中捕获此扩展属性，SSDT 会将其添加为脚本（然后您可以选中包含扩展属性的发布选项）。然后我使用 SQLCMD 变量来识别当前传递中应用的源版本和目标版本。一旦确定了源（项目快照）和目标（即将更新的目标数据库）之间的版本差异，您就可以找到所有需要应用的快照。在SSDT 部署内部，您可能必须将其移动到构建或部署管道（我们使用 TFS 自动部署并有自定义操作来执行此操作）。

The next hurdle is to keep snapshots of the schema with their associated data motion scripts. In this case, it helps to make the scripts as idempotent as possible (meaning, you can rerun the scripts without any ill side-effects). It helps to split scripts that can safely be rerun from scripts that must be executed one time only. We're doing the same thing with static reference data (dictionary or lookup tables) - in other words, we have a library of MERGE scripts (one per table) that keep the reference data in sync, and these scripts are included in the post-deployment scripts (via the SQLCMD :r command). The important thing to note here is that you mustexecute them in the correct order in case any of these reference tables have FK references to each other. We include them in the main post-deploy script in order, and it helps that we created a tool that generates these scripts for us - it also resolves dependency order. We run this generation tool at the close of a "version" to capture the current state of the static reference data. All your other data motion scripts are basically going to be special-case and most likely will be single-use only. In that case, you can do one of two things: you can use an IF statement against the db build/app version, or you can wipe out the 1 time scripts after creating each snapshot package.

下一个障碍是保留架构快照及其关联的数据移动脚本。在这种情况下，它有助于使脚本尽可能具有幂等性（这意味着您可以重新运行脚本而不会产生任何不良副作用）。它有助于将可以安全地重新运行的脚本与必须只执行一次的脚本分开。我们对静态参考数据（字典或查找表）做同样的事情——换句话说，我们有一个 MERGE 脚本库（每个表一个），可以保持参考数据同步，这些脚本包含在帖子中- 部署脚本（通过 SQLCMD :r 命令）。这里要注意的重要一点是，您必须以正确的顺序执行它们，以防这些参考表中的任何一个相互有 FK 引用。我们按顺序将它们包含在主部署后脚本中，这有助于我们创建一个为我们生成这些脚本的工具——它还解决了依赖顺序。我们在“版本”结束时运行此生成工具以捕获静态参考数据的当前状态。您所有的其他数据移动脚本基本上都是特殊情况，并且很可能是一次性的。在这种情况下，您可以执行以下两种操作之一：您可以针对 db build/app 版本使用 IF 语句，或者您可以在创建每个快照包后清除 1 次脚本。

It helps to remember that SSDT will disable FK check constraints and only re-enable them after the post-deployment scripts run. This gives you a chance to populate new non-null fields, for example (by the way, you have to enable the option to generate temporary "smart" defaults for non-null columns to make this work). However, FK check constraints are only disabled for tables that SSDT is recreating because of a schema change. For other cases, you are responsible for ensuring that data motion scripts run in the proper order to avoid check constraints complaints (or you manually have disable/re-enable them in your scripts).

记住 SSDT 将禁用 FK 检查约束并仅在部署后脚本运行后重新启用它们会有所帮助。例如，这让您有机会填充新的非空字段（顺便说一下，您必须启用为非空列生成临时“智能”默认值的选项才能使其工作）。但是，仅对 SSDT 由于架构更改而重新创建的表禁用 FK 检查约束。对于其他情况，您有责任确保数据移动脚本以正确的顺序运行，以避免检查约束投诉（或者您在脚本中手动禁用/重新启用它们）。

DACPAC can help you because DACPAC is essentially a snapshot. It will contain several XML files describing the schema (similar to the build output of the project), but frozen in time at the moment you create it. You can then use SQLPACKAGE.EXE or the deploy provider to publish that package snapshot. I haven't quite figured out how to use the DACPAC versioning, because it's more tied to "registered" data apps, so we're stuck with our own versioning scheme, but we do put our own version info into the DACPAC filename.

DACPAC 可以帮助您，因为 DACPAC 本质上是一个快照。它将包含几个描述模式的 XML 文件（类似于项目的构建输出），但在您创建它的那一刻被冻结。然后，您可以使用 SQLPACKAGE.EXE 或部署提供程序来发布该包快照。我还没有完全弄清楚如何使用 DACPAC 版本控制，因为它更依赖于“注册”数据应用程序，所以我们坚持使用我们自己的版本控制方案，但我们确实将我们自己的版本信息放入了 DACPAC 文件名中。

I wish I had a more conclusive and exhasutive example to provide, but we're still working out the issues here too.

我希望我能提供一个更有说服力和详尽的例子，但我们仍在解决这里的问题。

One thing that really sucks about SSDT is that unlike DBProj, it's currently not extensible. Although it does a much better job than DBProj at a lot of different things, you can't override its default behavior unless you can find some method inside of pre/post scripts of getting around a problem. One of the issues we're trying to resolve right now is that the default method of recreating a table for updates (CCDR) really stinks when you have tens of millions of records.

SSDT 真正糟糕的一件事是与 DBProj 不同，它目前不可扩展。尽管它在许多不同的方面比 DBProj 做得好得多，但您不能覆盖其默认行为，除非您可以在前/后脚本中找到解决问题的方法。我们现在正在努力解决的问题之一是，当您拥有数千万条记录时，重新创建更新表 (CCDR) 的默认方法真的很糟糕。

-UPDATE: I haven't seen this post in some time, but apparently it's been active lately, so I thought I'd add a couple of important notes: if you are using VS2012, the June 2013 release of SSDT now has a Data Comparison tool built-in, and also provides extensibility points - that is to say, you can now include Build Contributors and Deployment Plan Modifiers for the project.

-更新：我有一段时间没有看到这篇文章了，但显然它最近很活跃，所以我想我会添加一些重要的注释：如果您使用的是 VS2012，那么 2013 年 6 月发布的 SSDT 现在有一个数据内置比较工具，还提供了扩展点——也就是说，您现在可以为项目包含构建贡献者和部署计划修改器。

回答by Charles Josephs

I haven't really found any more useful information on the subject but I've spent some time getting to know the tools, tinkering and playing, and I think I've come up with some acceptable answers to my question. These aren't necessarily the best answers. I still don't know if there are other mechanisms or best practices to better support these scenarios, but here's what I've come up with:

我还没有真正找到关于这个主题的更多有用信息，但我花了一些时间来了解工具、修补和玩耍，我想我已经为我的问题提出了一些可以接受的答案。这些不一定是最好的答案。我仍然不知道是否有其他机制或最佳实践来更好地支持这些场景，但这是我想出的：

The Pre- and Post-Deploy scripts for a given version of the database are only used migrate data from the previous version. At the start of every development cycle, the scripts are cleaned out and as development proceeds they get fleshed out with whatever sql is needed to safely migrate data from the previous version to the new one. The one exception here is static data in the database. This data is known at design time and maintains a permanent presence in the Post-Deploy scripts in the form of T-SQL MERGE statements. This helps make it possible to deploy any version of the database to a new environment with just the latest publish script. At the end of every development cycle, a publish script is generated from the previous version to the new one. This script will include generated sql to migrate the schema and the hand crafted deploy scripts. Yes, I know the Publish tool can be used directly against a database but that's not a good option for our clients. I am also aware of dacpac files but I'm not really sure how to use them. The generated publish script seems to be the best option I know for production upgrades.

给定数据库版本的部署前和部署后脚本仅用于从先前版本迁移数据。在每个开发周期的开始，脚本都会被清除，随着开发的进行，它们会用任何需要的 sql 充实，以安全地将数据从以前的版本迁移到新版本。这里的一个例外是数据库中的静态数据。此数据在设计时已知，并以 T-SQL MERGE 语句的形式永久存在于部署后脚本中。这有助于仅使用最新的发布脚本将任何版本的数据库部署到新环境中。在每个开发周期结束时，都会生成一个从先前版本到新版本的发布脚本。此脚本将包括生成的 sql 以迁移架构和手工制作的部署脚本。是的，我知道发布工具可以直接用于数据库，但这对我们的客户来说不是一个好的选择。我也知道 dacpac 文件，但我不确定如何使用它们。生成的发布脚本似乎是我所知道的生产升级的最佳选择。

So to answer my scenarios:

所以要回答我的情况：

1) To upgrade a database from v3 to v8, I would have to execute the generated publish script for v4, then for v5, then for v6, etc. This is very similar to how we do it now. It's well understood and Database Projects seem to make creating/maintaining these scripts much easier.

1) 要将数据库从 v3 升级到 v8，我必须为 v4、v5、v6 等执行生成的发布脚本。这与我们现在的做法非常相似。很好理解，数据库项目似乎使创建/维护这些脚本变得更加容易。

2) When the schema changes from underneath data, the Pre- and Post-Deploy scripts are used to migrate the data to where it needs to go for the new version. Affected data is essentially backed-up in the Pre-Deploy script and put back into place in the Post-Deploy script.

2) 当架构从底层数据更改时，部署前和部署后脚本用于将数据迁移到新版本所需的位置。受影响的数据基本上在部署前脚本中备份，并在部署后脚本中放回原位。

3) I'm still looking for advice on how best to work with these tools in these scenarios and others. If I got anything wrong here, or if there are any other gotchas I should be aware of, please let me know! Thanks!

3) 我仍在寻求有关如何在这些场景和其他场景中最好地使用这些工具的建议。如果我在这里做错了什么，或者还有其他我应该注意的问题，请告诉我！谢谢！

回答by jamiet

In my experience of using SSDT the notion of version numbers (i.e. v1, v2...vX etc...) for databases kinda goes away. This is because SSDT offers a development paradigm known as declarative database development which loosely means that you tell SSDT what state you want your schema to be in and then let SSDT take responsibility for getting it into that state by comparing against what you already have. In this paradigm the notion of deploying v4 then v5 etc.... goes away.

根据我使用 SSDT 的经验，数据库的版本号（即 v1、v2...vX 等...）的概念有点消失了。这是因为 SSDT 提供了一种称为声明式数据库开发的开发范式，这松散地意味着您告诉 SSDT 您希望您的架构处于什么状态，然后让 SSDT 负责通过与您已有的状态进行比较来使其进入该状态。在这种范式中，部署 v4 然后 v5 等的概念消失了。

Your pre and post deployment scripts, as you correctly state, exist for the purposes of managing data.

正如您正确声明的那样，您的部署前和部署后脚本是为了管理数据而存在的。

Hope that helps.

希望有帮助。

JT

JT

回答by Ryan White

I just wanted to say that this thread so far has been excellent.

我只想说，到目前为止，这个线程非常好。

I have been wrestling with the exact same concerns and am attempting to tackle this problem in our organization, on a fairly large legacy application. We've begun the process of moving toward SSDT (on a TFS branch) but are at the point where we really need to understand the deployment process, and managing custom migrations, and reference/lookup data, along the way.

我一直在努力解决完全相同的问题，并试图在我们组织中的一个相当大的遗留应用程序上解决这个问题。我们已经开始转向 SSDT（在 TFS 分支上），但现在我们真的需要了解部署过程，并在此过程中管理自定义迁移和参考/查找数据。

To complicate things further, our application is one code-base but can be customized per 'customer', so we have about 190 databases we are dealing with, for this one project, not just 3 or so as is probably normal. We do deployments all the time and even setup new customers fairly often. We rely heavily on PowerShell now with old-school incremental release scripts (and associated scripts to create a new customer at that version). I plan to contribute once we figure this all out but please share whatever else you've learned. I do believe we will end up maintaining custom release scripts per version, but we'll see. The idea about maintaining each script within the project, and including a From and To SqlCmd variable is very interesting. If we did that, we would probably prune along the way, physically deleting the really old upgrade scripts once everybody was past that version.

更复杂的是，我们的应用程序是一个代码库，但可以根据“客户”进行定制，因此对于这个项目，我们有大约 190 个数据库正在处理，而不仅仅是正常情况下的 3 个左右。我们一直在进行部署，甚至经常建立新客户。我们现在严重依赖 PowerShell 和老式增量发布脚本（以及在该版本上创建新客户的相关脚本）。我计划在我们弄清楚这一切后做出贡献，但请分享您学到的其他知识。我相信我们最终会维护每个版本的自定义发布脚本，但我们会看到。在项目中维护每个脚本并包含 From 和 To SqlCmd 变量的想法非常有趣。如果我们这样做，我们可能会沿途修剪，

BTW - Side note - On the topic of minimizing waste, we also just spent a bunch of time figuring out how to automate the enforcement of proper naming/data type conventions for columns, as well as automatic generation for all primary and foreign keys, based on naming conventions, as well as index and check constraints etc. The hardest part was dealing with the 'deviants' that didn't follow the rules. Maybe I'll share that too one day if anyone is interested, but for now, I need to pursue this deployment, migration, and reference data story heavily. Thanks again. It's like you guys were speaking exactly what was in my head and looking for this morning.

顺便说一句 - 旁注 - 关于最小化浪费的话题，我们还花了很多时间来弄清楚如何自动执行列的正确命名/数据类型约定，以及自动生成所有主键和外键，基于关于命名约定，以及索引和检查约束等。最难的部分是处理不遵守规则的“异常”。如果有人感兴趣的话，也许有一天我也会分享它，但现在，我需要大量地研究这个部署、迁移和参考数据的故事。再次感谢。就像你们今天早上所说的正是我脑海中的想法。