sql - 基于多系统数据库结构的复制/更新最佳实践
问题描述
所以在搜索并没有找到类似的案例之后，我想提出一个新问题。所以情况是这样的：我们正在使用一个具有非常复杂数据结构的大型数据库。此外，我们正在开发多个系统以确保稳定性（开发、测试、质量和生产），并且它始终是一个斗争，因此在这些系统之间移动数据。正如我所说，数据结构非常大，数据库内部也有很多逻辑。客户可以添加新的数据部分作为配置，并且还有用于统计和监控的静态数据收入。所以让我用一个小例子来解释这个问题：
让我们以这个数据库为例。我们有一些家庭互相竞争。他们将创建一些关于他们提出的观点的统计数据。
- 紫色表是固定配置。它们只创建一次，只能通过 Operator 进行更改。这些更改将首先在开发系统中完成和测试。
- 黄色表格正在改变配置。每个家庭都可以创建或删除多个比赛并分配他们的孩子。
- 红表只是普通数据。每次孩子得分时，都会添加一个新行，其中包含数量和当前时间以及与孩子和比赛的关系。该表将作为以后统计的基础。
该数据库是在两个系统上开发的，一个由家庭使用的生产系统和一个由程序员/操作员使用的开发系统。
在开发过程中，程序员将添加测试数据，如儿童家庭比赛和积分。在使用家庭的同时，他们将创建新的比赛并分配新的孩子，并将填满积分表。
- 有必要将新的/经过测试的/固定的系列从开发系统复制到生产系统。
- 还需要将竞赛、竞赛-孩子-作业和分数从生产系统复制到开发系统，以发现新的错误。
- 还必须可以更改开发系统上的表结构并将此更改传输到生产系统。（这不应该是这里的主要话题，有时它可能会发生如此大的变化，以至于没有简单的方法，所以让我们保持这一点简单但记住这一点。）
我想将部分表复制到另一个系统，但能够忽略某些表（例如：Points），并且我想确保不复制没有父母家庭的孩子，因此数据库中没有“无父母”对象。
问题：有什么好的和节省的方法来做到这一点？
我不需要针对特定数据库类型或某些脚本的解决方案。我正在寻找工具、库或良好实践。（但作为说明，我们使用的是 mssql。）
我们目前正在为这个问题制作一个工具（进展不顺利：不稳定、过于复杂、速度慢并且可能重新发明轮子）。
还有很多我认识的开发人员只是复制整个数据库（制作备份并将其运行到另一台服务器中）但这也产生了问题：正在复制用户并且他们的 guid 发生变化，因此他们失去了权限等。我不这么认为是一个很好的解决方案。此外，数据库关闭了很长时间，而且过程从来都不是一个顺利的过程。
手动制作有时是最简单的方法，但考虑到我们的数据结构的大小，它不仅是一项巨大的工作，而且出错的可能性也很大。
所以我希望有人知道一个工具或类似的东西来帮助我。
解决方案
欢迎来到像数据库这样的有状态实体的开发过程中。:) RedGate 制作了一个名为 SQL Source Control 的工具，该工具非常适合将更改的数据和 Schema 转移到生产环境中，并且它可以与 GIT 等源代码控制解决方案进行交互。它有点贵，但它是我找到的最好的。让开发人员及时了解产品数据和开发人员更改的一种选择是我在我最后一个工作地点炮制的一种选择，它是……不是 100% 完美，但总比没有好，而且是免费的。它是在 Powershell 中开发的，它是这样的：
- 创建 Pre-restore、Pre-dacpac 和 Post-dacpac SQL 脚本来存储 dev 和 prod 之间的数据和权限差异
- 使用SQLPackage.EXE制作Dev的DacPac（Dacpac基本上是db的xml schema，没有数据）
- Execute Pre-restore Proc（经常拷贝出需要持久化的测试数据）
- 通过 Dev 恢复 Prod
- 执行 Pre-dacpac 脚本（任何可能导致数据丢失的 DDL 可能需要转到此处）
- 使用 SQLPackage.EXE 将步骤 2 中制作的 DacPac 应用到新恢复的数据库
- 执行 Post-Dacpac 脚本（权限、恢复步骤 3 中复制的数据）
同样，就像我说的那样，它工作并自动将产品数据恢复到我们的开发环境中，同时保持我们的开发更改完好无损，但它需要大量的维护和维护。另外，请记住，一旦您的数据库达到一定大小，由于恢复需要时间，每晚进行恢复不再是可行的选择。