如何管理新构建和迁移的 sqlserver 数据库项目？
如何管理 Visual Studio 项目的 SQL Server 数据库构建/部署/迁移？
我们有一个包含合理数据库部分（约 100 个表，约 500 个过程/函数/视图）的产品，因此我们需要能够部署当前版本的新数据库以及将旧数据库升级到当前版本。目前，我们维护单独的脚本来创建新数据库和版本之间的迁移。显然不理想，但其他人是如何处理这个问题的呢？
这对我们来说很复杂，因为我们有许多客户，每个客户都有自己的数据库实例，而不是说在我们自己的 Web 服务器上只有开发/测试/实时实例，但为其他人管理开发/测试/实时的过程必须类似。
更新：我不想使用像 RedGate 这样的任何专有产品（尽管我一直听说它们非常好并且会研究它作为解决方案）。
How do you manage your sql server database build/deploy/migrate for visual studio projects?
We have a product that includes a reasonable database part (~100 tables, ~500 procs/functions/views), so we need to be able to deploy new databases of the current version as well as upgrade older databases up to the current version. Currently we maintain separate scripts for creation of new databases and migration between versions. Clearly not ideal, but how is anyone else dealing with this?
This is complicated for us by having many customers who each have their own db instance, rather than say just having dev/test/live instances on our own web servers, but the processes around managing dev/test/live for others must be similar.
UPDATE: I'd prefer not to use any proprietary products like RedGate's (although I have always heard they're really good and will look into that as a solution).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我们使用 Red-Gate SQLCompare 和 SQLDataCompare 来处理此问题。这个想法很简单。这两种比较产品都允许您将选定表（例如配置表）中的架构或数据作为脚本维护完整的映像。然后，您可以将任何数据库与脚本进行比较并获取更改脚本。我们将脚本保存在 Mercurial 源代码管理中，并为每个版本添加标签（标签）。然后，支持人员可以获取任何版本的脚本，并使用 Redgate 工具从头开始创建或升级。
Redgate 还有一个 API 产品，允许您从代码中执行比较功能。例如，这将允许您在安装程序或产品本身中具有自动升级功能。我们经常将其用于我们的托管网络应用程序，因为它使我们能够更全面地自动化推出过程。在我们的例子中，我们有一个 MSBuild 任务，支持可以执行该任务来执行自动部署和升级。如果您分发给第三方，则必须为包含 API 的每个分发支付少量的额外许可费。
Redgate 还有一个自动打包数据库安装或升级的工具。我们不使用该版本，因为我们发现与版本的脚本进行比较为我们提供了更大的灵活性。
Redgate 工具还可以帮助我们进行开发，因为它们使以非常精细的方式对架构和配置数据进行源代码控制变得很简单（每个数据库对象都可以放置在自己的文件中）
We use Red-Gate SQLCompare and SQLDataCompare to handle this. The idea is simple. Both compare products let you maintain a complete image of the schema or data from selected tables (e.g. configuration tables) as scripts. You can then compare any database to the scripts and get a change script. We keep the scripts in our Mercurial source control and tag (label) each release. Support can then go get the script for any version and use the Redgate tools to either create from scratch or upgrade.
Redgate also has an API product that allows you to do the compare function from your code. For example, this would allow you to have an automatic upgrade function in your installer or in the product itself. We often use this for our hosted web apps as it allows us to more fully automate the rollout process. In our case, we have an MSBuild task that support can execute to do an automatic rollout and upgrade. If you distribute to third-parties, you have to pay a small additional license fee for each distribution that includes the API.
Redgate also has a tool that automatically packages a database install or upgrade. We don't use that one as we have found that the compare against scripts for a version gives us more flexibility.
The Redgate tools also help us in development because they make it trivial to source control the schema and configuration data in a very granular way (each database object can be placed in its own file)
这个问题是在 SSDT 项目出现之前就被问到的，但这绝对是我现在会采用的方式，以及手工制作用于结构数据库更改的迁移脚本，其中数据会受到影响。
The question was asked before SSDT projects appeared, but that's definitely the way I'd go nowadays, along with hand-crafting migration scripts for structural db changes where there is data that would be affected.
还有 MS VSTS 方法（2008 描述 此处），有人在 2010 年写过一篇关于执行此操作以及使用这些工具的优点/缺点的好文章吗？
There's also the MS VSTS method (2008 description here), anyone got a good article on doing this with 2010 and the pros/cons of using these tools?