SQL Server数据库连续集成（CI）最佳实践以及如何实现它们–源代码控制

This article provides for a roadmap to continuous integration and delivery best practices, and along the way demonstrates how to apply these with ApexSQL tools and technologies. In some sections this article is aspirational, as no solution yet exists, but demonstrates our plan, direction and roadmap. As the tools that apply these best practices are released this article will be updated accordingly.

本文提供了持续集成和交付最佳实践的路线图，并演示了如何将它们与ApexSQL工具和技术一起应用。 由于某些解决方案尚不存在，因此在某些部分中，本文志向高远，但它展示了我们的计划，方向和路线图。 随着应用这些最佳实践的工具的发布，本文将进行相应的更新。

The article will be color coded to separate the policies from the implementation. This will allow you to read through the policies quickly, to get a good understanding of some baseline expectations, then, if you have ApexSQL tools, work through the list again to implement them.

本文将采用彩色编码，以将策略与实现分开。 这将使您能够快速通读策略，以更好地理解一些基线期望，然后，如果您有ApexSQL工具，请再次遍历列表以实施它们。

一切都应该在源代码控制中 (Everything should be in source control)

Everyone will assume that all database objects are in source control but we want to make sure static data and the database itself is too. The goal is to ensure that whatever will be built in the QA environment should be in source control.

每个人都将假定所有数据库对象都在源代码控制中，但是我们要确保静态数据和数据库本身也是如此。 目的是确保将在质量检查环境中构建的所有内容都置于源代码控制中。

A true build starts with just a SQL Server with nothing else outside of our automation environment, including and especially, the database itself. Therefore, we want the database creation script versioned in source control and dropped and re-created with each new build.

真正的构建仅从SQL Server开始，而自动化环境之外的其他功能（尤其是数据库本身）除外。 因此，我们希望在源代码管理中对数据库创建脚本进行版本控制，并在每次新构建时都将其删除并重新创建。

Static data is a critical part of our database build process as well, as in many cases database and business logic is tightly coupled with specific data in static tables. To read more about what static data is and why it needs to be versioned in source control, read Understanding SQL Server database static data and how it fits into Database lifecycle management.

静态数据也是我们数据库构建过程的关键部分，因为在许多情况下，数据库和业务逻辑与静态表中的特定数据紧密结合。 要了解有关什么是静态数据以及为什么需要在源代码管理中对其进行版本控制的更多信息，请阅读《 了解SQL Server数据库静态数据及其如何适合数据库生命周期管理》 。

从数据模型开始 (Start with the data model)

When designing and developing a database, most developers don’t create tables by writing script, but rather they edit them in a data modeling tool. Ideally, the ERM tool will allow the user to check out a table, make changes, and check in all from within the IDE.

在设计和开发数据库时，大多数开发人员并不通过编写脚本来创建表，而是在数据建模工具中对其进行编辑。 理想情况下，ERM工具将允许用户从IDE内部签出表，进行更改并全部签入。

If the changes would break dependencies, the developer could isolate those objects that would change, manually check them all out, and then run the change script to update them, and check them all back in.

如果更改会破坏依赖关系，则开发人员可以隔离那些将要更改的对象，手动将其全部签出，然后运行更改脚本以对其进行更新，然后将其全部签回。

ApexSQL Refactor, offers a feature, Safe rename, that will create a script to update all referencing objects, based on renaming of any object.

ApexSQL Refactor提供了安全重命名功能，该功能将基于任何对象的重命名创建一个脚本来更新所有引用对象。

Even better, if the ERM tool would automatically execute the process of checking out all referencing objects, updating them, and checking them back in.

更好的是，如果ERM工具将自动执行签出所有引用对象，对其进行更新并重新签入的过程。

IDE与SQL源代码控制集成在一起 (IDE integrated with SQL source control)

If your ERM isn’t integrated directly with source control, then your IDE should be. This will allow you to check out objects, edit them and check them back in all in the same IDE you are developing in. This has been a given in client development for years, but less so in the database development world.

如果您的ERM没有直接与源代码控制集成，则应该与您的IDE集成。 这将使您可以签出对象，对其进行编辑并在正在开发的同一IDE中全部签入它们。这在客户端开发中已经存在多年了，但是在数据库开发领域却很少。

With tools like ApexSQL Source Control, you can integrate SQL Source control directly into your development process in either SQL Server Management Studio or Visual Studio, without. ApexSQL Source Control allows integrated check-in, check-out processes so you can update your source control repository as you are developing your database.

借助ApexSQL Source Control之类的工具，您可以将SQL Source控制直接集成到SQL Server Management Studio或Visual Studio中的开发过程中，而无需集成。 ApexSQL Source Control允许集成的签入，签出流程，因此您在开发数据库时可以更新源代码控制存储库。

强制入住评论 (Mandatory check-in comments)

We expect the code itself to have comments but too often, check ins are committed silently, with no means to quickly see what was changed other than compare to the last version. If a change was significant to require a commit, it should require a comment.

我们希望代码本身具有注释，但通常情况下，签入是无声提交的，除了与上一个版本进行比较之外，没有其他任何方法可以快速查看所做的更改。 如果更改很重要，需要提交，则应添加注释。

Even better, configure your IDE to require comments on commits via custom check-in policies. A good example in Visual Studio is How To: Create Custom Check-in Policies in Visual Studio Team Foundation Server. This script will require the user to add a comment, prior to allowing the check-in to proceed. Setting minimum character requirements may be a helpful mod.

更好的是，将IDE配置为要求通过自定义签入策略对提交进行注释。 Visual Studio中的一个很好的例子是如何：在Visual Studio Team Foundation Server中创建自定义签入策略 。 该脚本将要求用户添加注释，然后才能继续签入。 设置最低字符要求可能会很有帮助。

If forcing manual, custom comments for each commit is overkill (which I don’t think it is), then consider automating them with tags. By creating a template with simple tags like Type “Edit” or “New”, Datetime, User, Version number, last Version number etc and having that template be applied to each commit would provide a baseline for use full information, as shown in the following image.

如果强制使用手动方式，则每次提交的自定义注释都太过严格了（我认为这是没有道理的），然后考虑使用标签将其自动化。 通过使用简单的标签（例如类型“ Edit”或“ New”），日期时间，用户，版本号，最后一个版本号等创建模板，并将该模板应用于每次提交，将提供使用全部信息的基准，如下图。

The below example, is a custom Visual Studio add-in demonstrating how to implemented, automated, tag based comments. See $log$ / Keyword Substitution / Expansion for more information.

下面的示例是一个自定义Visual Studio加载项，演示了如何实现基于标签的自动注释。 有关更多信息，请参见$ log $ /关键字替换/扩展 。

对象内评论 (In-object comments)

To ensure a full version change documentation trail, it is important to include changes in the header of the scripts, objects as well. Most developers are accustomed to quickly checking the version history of an object by Date, in ascending order, and change history.

为了确保完整的版本更改文档记录，在脚本，对象的标头中也包含更改非常重要。 大多数开发人员习惯于按日期（升序）快速检查对象的版本历史记录，并更改历史记录。

A neat trick to both automate comments (from the previous best practice) and to append the check-in comment to the source code header stub itself. $log$ / Keyword Substitution / Expansion Check-In Policy Using a tag based system to generate a comment block, it also appends it to the code block header itself as in the following image.

一个巧妙的技巧，既可以自动执行注释（来自先前的最佳实践），又可以将签入注释附加到源代码标头存根本身。 $ log $ /关键字替换/扩展签入策略使用基于标签的系统生成注释块，还将其附加到代码块标题本身，如下图所示。

强制锁 (Mandatory locks)

A “conflict” in the course of source control occurs when the user is working in the dedicated model (on a local copy of a database) with a version of an object that is not the latest one from the repository.

当用户在专用模型中（在数据库的本地副本上）使用的对象版本不是存储库中的最新版本时，就会发生源代码控制过程中的“冲突”。

Since most CI processes will start in source control, we can assume there are no conflicts, as all objects will have been successfully checked in, but even so, on scheduled builds that run nightly or event based builds e.g. “Build day”, conflicts can prove time consuming to resolve and may result in the build window being missed. Even though such scheduled or event-based builds don’t meet the criteria of true continuous integration, they do at least classify as build automation and are pre-cursors to CI, so I’ll still address them in this best practices article.

由于大多数CI流程都将在源代码控制中开始，因此我们可以假设没有冲突，因为所有对象都已成功签入，但是即使如此，在每晚运行的计划构建或基于事件的构建（例如“构建日”）下，冲突也可能会发生。证明解决该问题很耗时，并可能导致缺少构建窗口。 即使此类计划的或基于事件的构建不符合真正的持续集成的标准，但它们至少可以归类为构建自动化，并且是CI的先驱，因此，我仍将在此最佳实践文章中针对它们进行讨论。

We may have a case where builds are done nightly at midnight. The onus is for all developers to commit their changes to the repository prior to leaving for the night. One developer, staying late, is checking in his changes and notes a conflict with a developer who made changes on the same object earlier in the day, and has subsequently left. With no means to discuss the conflict resolution with the other developer and not willing to overwrite his changes, the developer flags the build system to skip the nightly run and leaves. A conflict was avoided, but at the cost of a missed build. Now QA will be working on a day old build and will miss the opportunity to work with fresh changes.

我们可能会在每晚的午夜完成构建。 所有开发人员都有责任在过夜之前将其更改提交到存储库。 一位熬夜的开发人员正在检查其更改，并注意到与当天早些时候在同一对象上进行更改并随后离开的开发人员发生冲突。 由于没有办法与其他开发人员讨论解决冲突的方法，并且不愿意覆盖他的更改，因此开发人员将构建系统标记为跳过夜间运行并离开。 避免了冲突，但是以错过构建为代价。 现在，质量检查人员将要进行一天的建设，而错过了进行新更改的机会。

Policies should exist that provide not only a visual indication that another developer has checked out an object but that it has already been edited. If those signals are ignored, then an explicit message can be shown when the user attempts to check out an object that has already been checked out.

应该存在一些策略，这些策略不仅可以直观地表明另一个开发人员已经签出了一个对象，而且已经对其进行了编辑。 如果忽略了这些信号，则当用户尝试检出已经检出的对象时，可以显示一条显式消息。

This is helpful but not stringent enough to avoid conflicts. The next layer of protection would be a policy that didn’t simply nag at the user to follow correct procedure but enforced the requirement to check out an object before editing it. The back door in such a “Permissive” policy is that even with the warning and the requirement to check out the object, the other developer could do just that, and override the original check out.

这是有用的，但不够严格以避免冲突。 下一层保护将是一项策略，该策略不仅会迫使用户遵循正确的程序，而且会强制要求在编辑对象之前先签出对象。 这样的“宽松”政策的后门是，即使有警告和要求检出对象的要求，其他开发人员也可以这样做，并覆盖原始的检出。

The best policy IMHO, is one that leaves nothing to chance and not only requires checkouts but allows them to be locked, preventing any chance of a conflict. Should the developer in our example who has successfully navigated through all of the other obstacles to prevent conflicts, attempt to edit an object, they would hit a roadblock.

最好的策略恕我直言，这是一个没有任何机会的策略，不仅要求结帐，而且允许将它们锁定，以防止发生冲突的可能性。 在我们的示例中，如果开发人员成功克服了所有其他障碍以防止冲突，并尝试编辑对象，那么他们将遇到障碍。

ApexSQL Source Control allows for the granular setting of Policies to enforce the level of adherence to source control best practices suitable for your team.

ApexSQL源代码管理允许对策略进行细化设置，以强制遵守适用于您团队的源代码管理最佳实践。

具有检测是否仍签出任何对象的机制 (Have a mechanism to detect if any objects are still checked out)

In a true CI/CD process, checked out objects are a reality for almost every build, since a build is triggered on every commit, it would be impossible for all other objects to be checked-in prior to each developer checking in their object.

在真正的CI / CD流程中，签出的对象几乎对于每个构建都是现实的，因为在每次提交时都会触发构建，所以在其他开发人员签入对象之前，不可能签入所有其他对象。

But I always get nervous when I start a scheduled build and see objects still checked out. In a team where daily check ins are the norm, if not the requirement (see next), pending check ins, near the end of the day, are often an indication of some sort of problem. Being able to visually scan the project via a source control client to identify checked out objects is critical. Developers can be contacted to remind them that they may have forgotten to check code in.

但是，当我开始计划的构建并看到仍然签出对象时，我总是会感到紧张。 在以日常签到为标准的团队中，如果没有要求（请参阅下一页），则在一天快要结束时挂起的签到通常是某种问题的征兆。 能够通过源代码控制客户端以可视方式扫描项目以识别签出的对象至关重要。 可以联系开发人员，以提醒他们他们可能忘记了签入代码。

ApexSQL Source Control provides a project view to show all objects by status.

ApexSQL源代码管理提供一个项目视图，以按状态显示所有对象。

For ad hoc synchronizations where the source control repository is the “Source” and a database is the “Target” and the goal is to move changes from the repository to the database, having database change control software that is source control aware, is an important feature. This would show visual indicators on each object for checked out, checked out and edited as well as locked.

对于临时同步，其中源控制存储库是“源”，数据库是“目标”，目标是将更改从存储库移到数据库，并且具有知道源控制的数据库更改控制软件是重要的特征。 这将在每个对象上显示可视指示器，以进行检出，检出和编辑以及锁定。

A policy driven approach to address this problem is to simply mandate nightly check-ins of all code (see next).

解决此问题的策略驱动方法是简单地强制所有代码每晚进行检入（请参阅下文）。

每晚入住 (Nightly check ins)

A key tenet of continuous integration isn’t checking in faster, but testing faster, which is the end, that drives the means. Closing the time from creating bugs to finding them, is critical for improving quality. The longer bugs exist, then the higher the chance of destabilizing more code, other modules, as more code is layered upon it. Also, the more time between development and testing, the harder it is for the developer to double back, remember what he/she was coding and fixing it.

持续集成的关键原则不是更快地签入，而是更快地测试，这最终是驱动手段的途径。 关闭从创建错误到发现错误的时间，对于提高质量至关重要。 错误存在的时间越长，则随着更多代码分层，破坏更多代码和其他模块的机会就越大。 同样，在开发和测试之间花费的时间越长，开发人员进行回退的难度就越大，请记住他/她正在编写和修复的内容。

To this end, forcing more iterative check-ins reduces the latency in software development that can degrade agility and quality.

为此，强制执行更多的迭代检入操作可减少软件开发中的延迟，这可能会降低敏捷性和质量。

Open check-outs can be problematic for nightly builds, as there can be code in development that relies on the checked-out module to perform properly but won’t be included in the new build of QA. If the build fails, the open check-outs become the unknown variable that have to be researched.

公开检出对于夜间构建可能会出现问题，因为开发中可能存在依赖于检出模块才能正常执行但不会包含在新版QA中的代码。 如果构建失败，则开放的签出将成为必须研究的未知变量。

Furthermore, open check-outs can be symptomatic of developers working on units of work that are too large and weren’t broken down more atomically in a way that ensures good communication and feedback.

此外，开放式签出可能表示开发人员从事的工作单元太大，并且无法以确保良好的沟通和反馈的方式进行更原子化的分解。

For all of these reasons and more, dev teams should encourage a policy of check-in before you check-out, meaning check-in all of your code before you leave for work that day.

出于所有这些原因以及更多原因，开发团队应鼓励在签出之前执行签入策略，这意味着在您当天去工作之前签入所有代码。

The next article in this series:

本系列的下一篇文章：

• SQL Server database continuous integration (CI) Best practices and how to implement them – Testing, processing and automation SQL Server数据库连续集成（CI）最佳实践以及如何实现它们–测试，处理和自动化

翻译自: https://www.sqlshack.com/sql-server-database-continuous-integration-ci-best-practices-and-how-to-implement-them-source-control/