SQL Server数据库持续集成简介

什么是SQL数据库CI？ ( What is SQL database CI? )

Continuous integration (CI), in the context of databases, refers to the practice of isolated database changes to be integrated, as soon as they are made and pushed to a source control repository. In the early days of CI, daily integration was a rule of thumb to follow. However, today, it is more common, than not, that database changes are integrated several times a day. Each change needs to pass an established testing plan that executes automatically on detected changes committed to the repository and if everything is ok, changes will be automatically merged using the build script. with the remaining code, into a new build that will be pushed to other environments (i.e. production, QA).

在数据库的上下文中，连续集成（CI）指的是隔离的数据库更改一旦进行并推送到源代码控制存储库后就进行集成的实践。 在CI的早期，日常集成是遵循的经验法则。 但是，今天，比以往更普遍的是，每天要多次集成数据库更改。 每个更改都需要通过一个已建立的测试计划，该计划将在检测到的提交到存储库的更改后自动执行，如果一切正常，则将使用构建脚本自动合并更改。 将剩余的代码合并到一个新版本中，该新版本将被推送到其他环境（即生产环境，QA）。

“连续”是什么意思 ( What does “continuous” mean )

By “continuous” it may be assumed that the process, once started, never ends. However, this is not generally the case. The only thing that is “continuous” in the entire process is monitoring the source control repository for changes. The rest of the process is repeatable and occurs only when a change is detected. With this being said, the rest of the process (build, load test data, test, document, deploy) is more “continual” than “continuous”.

通过“连续”，可以假定该过程一旦开始就永远不会结束。 但是，通常情况并非如此。 在整个过程中唯一“连续的”是监视源代码控制存储库中的更改。 其余过程是可重复的，并且仅在检测到更改时才发生。 话虽这么说，其余的过程（构建，加载测试数据，测试，文档，部署）更多是“连续的”而不是“连续的”。

“整合”是什么意思？ ( What does “Integration” mean? )

In CI, “Integration” means that once a database change is committed to the repository, the CI server is triggered to pull the latest state of the repository and to integrate committed database changes with the production (or any other environment). This would create a process to build a database, load it with some test data and perform various types of testing. As soon as all tests are passed, the process of “integration” moves to the next step, which is building the database that will be deployed.

在CI中，“集成”表示一旦将数据库更改提交到存储库，就会触发CI服务器以拉出存储库的最新状态，并将提交的数据库更改与生产（或任何其他环境）集成。 这将创建一个过程来构建数据库，将一些测试数据加载到数据库中并执行各种类型的测试。 一旦所有测试通过，“集成”过程将进入下一步，即构建将要部署的数据库。

CI的好处是什么？ ( What are the benefits of CI? )

   Agility. Instead of waiting the QA team to finish testing right before the release, CI provides feedback early and often. This enables developers to be agile in their response to problems and avoids situations where teams become mired in days, weeks, or even months’ worth of accumulated defects that can slow development to a stop

敏捷性 。 CI无需等待质量检查团队在发布之前完成测试，而是尽早且经常提供反馈。 这使开发人员能够灵活地对问题做出响应，并避免了团队陷入数天，数周甚至数月的累积缺陷中而使开发工作陷入停顿的情况

   Efficiency. By automating the repeatable part of the process (building, loading test data, running unit test, deployment), manual effort and human error are largely eliminated from the build process

效率 。 通过自动化过程的可重复部分（构建，加载测试数据，运行单元测试，部署），可以从构建过程中消除大量的人工和人为错误

   Quality. Having CI implemented allows you to have a database that passed all tests, otherwise it may not even be deployed to QA. This gives a certain confidence to developers as they know the database that is deployed to QA will, in theory, be free of critical and obvious errors.

品质 。 实施CI后，您将拥有一个通过所有测试的数据库，否则它甚至可能不会部署到QA。 这给开发人员一定的信心，因为他们知道部署到QA的数据库在理论上不会出现严重的错误。

CI要求 ( Requirements for CI )

   Source control management system (SCM system). There are many available including Team Foundation Server (TFS), Git, Subversion (SVN), Mercurial and Perforce. By placing a database under source control, you will be able to maintain SQL code changes inside the team and to establish a systematic development methodology. Having a full history of changes made against the database, enables developers to revert any changeset in case there is a need for change rollback. Having a SQL database under source control is a pre-requisite to implementing CI.

源代码管理管理系统（SCM系统） 。 有很多可用的功能，包括Team Foundation Server（TFS），Git，Subversion（SVN），Mercurial和Perforce。 通过将数据库置于源代码控制下，您将能够维护团队内部SQL代码更改并建立系统的开发方法。 具有对数据库所做的更改的完整历史记录，使开发人员可以在需要进行更改回滚的情况下还原所有更改集。 在源代码控制下拥有SQL数据库是实现CI的先决条件。

   Continuous integration server (CI server). This is where the entire processes of automatic testing, building and deployment is managed. A CI server is connected with the source control repository watching and waiting for any committed changes. When a change is detected, the CI server builds a database from the latest version on the repository, put some test data in the database and runs automated tests if needed informing the user (developer) about the test results at the end or during the test process.

持续集成服务器（CI服务器）。 在这里管理自动测试，构建和部署的整个过程。 CI服务器与源代码控制存储库连接，以监视并等待任何已提交的更改。 当检测到更改时，CI服务器将从存储库中的最新版本构建数据库，将一些测试数据放入数据库中，并在需要时运行自动化测试，以在测试结束时或测试期间向用户（开发人员）告知测试结果处理。

   Reliable build and deploy process. When all automated tests are finished and passed, a build system is triggered to use the tested code for building a database and deployment to QA, for example

可靠的构建 和部署 过程 。 完成并通过所有自动化测试后，将触发构建系统以使用经过测试的代码来构建数据库并将其部署到QA。

   Automation. From the first step of monitoring changes on the source control repository, to testing, generating test reports and sending feedback back to the team, up to the final automated build and deployment. CI, should not require any manual intervention and should occur on each change made against the database and committed to the repository.

自动化 。 从监视源代码控制存储库中的更改的第一步到测试，生成测试报告并将反馈发送回团队，再到最终的自动化构建和部署。 CI，不需要任何手动干预，并且应该在对数据库进行的每次更改并提交到存储库时进行。

Building can be scheduled in other ways, like daily, or several times a day, at set periods of time, but the best practice, in general, is to set it to run on each committed change.

可以按其他方式（例如每天或每天几次）在设定的时间段内计划建设，但是通常，最佳实践是将其设置为在每次已落实的更改上运行。

“穷人”的CI工作流程？ ( A “poor man’s” CI workflow? )

To create scheduled or “On demand” CI, the first thing is to identify processes that requires automation. These can be in various areas such as building, testing or deployment. For example, the first requirement is to create a delta script of recent database schema changes, to apply to QA. Next, is to run unit tests on all new and changed objects e.g. stored procedures. Another requirement is to replicate all static production data, like State names, Zip and country codes, etc. Finally, synthetic production data aka test, is needed to test the database fully in QA

要创建计划的或“按需” CI，首先要确定需要自动化的流程。 这些可以在各个领域，例如构建，测试或部署。 例如，第一个要求是创建一个最近数据库模式更改的增量脚本，以应用于QA。 接下来，是对所有新的和更改的对象（例如存储过程）运行单元测试。 另一个要求是复制所有静态生产数据，例如州名称，邮政编码和国家/地区代码等。最后，需要进行综合生产数据aka测试，以在QA中完全测试数据库

The next step is to create a process that makes these processes repeatable and allows running them based on a schedule or particular event. Various tools like ApexSQL Diff (schema change scripting), ApexSQL Script (schema and/or data scripting), and ApexSQL Generate (synthetic database scripting) can be used to create these scripts.

下一步是创建一个流程，使这些流程可重复，并允许根据计划或特定事件运行它们。 可以使用各种工具（例如ApexSQL Diff（架构更改脚本），ApexSQL脚本（架构和/或数据脚本）和ApexSQL Generate（合成数据库脚本））来创建这些脚本。

The last step is to create a job to combine and schedule all the processes to run in the correct order, ideally with one click and/or via a scheduled/automated process and ideally return codes for success or failure

最后一步是创建一个工作，以按正确的顺序组合和安排所有流程，以按正确的顺序运行，理想情况是单击一下和/或通过计划/自动流程，理想情况下返回成功或失败的代码

Once complete, this “poor man’s” CI process will be able to run on demand, with the click of a batch file, or be scheduled to run nightly

完成后，只需单击批处理文件，即可按需运行此“穷人”的CI流程，或计划每晚运行

Changing this process, to one that is automatically triggered based on checked in code requires deeper integration of tools involved in the development process, like ApexSQL Source Control

将此过程更改为基于签入代码自动触发的过程，需要对开发过程中涉及的工具进行更深入的集成，例如ApexSQL Source Control

“有钱人”的CI工作流程 ( A “rich man’s” CI work flow )

The following are steps that show the usual workflow involved database continuous integration at the next level. Note that these steps may vary depending on the project/environment:

以下步骤显示了通常涉及下一级别数据库连续集成的工作流程。 请注意，这些步骤可能会因项目/环境而异：

1. A developer makes a change in the database and creates a build to perform local testing. If everything goes well, changes are committed to the source control repository. Anything that happens before the commit, does not invoke any action on the CI server side. This means that developers can work locally, even to have a local repository where changes will be committed prior to synchronizing with the remote one.
   开发人员对数据库进行更改，并创建一个构建以执行本地测试。 如果一切顺利，则更改将提交到源代码控制存储库。 提交之前发生的任何事情都不会在CI服务器端调用任何操作。 这意味着开发人员可以在本地工作，甚至可以拥有一个本地存储库，在该存储库中将在与远程同步之前进行更改。
2. The CI server is constantly monitoring the remote repository for possible changes (e.g. every few minutes, hours etc.). As soon as committed changes are detected, the CI server triggers a build script that pulls the latest state from the repository (including the newly committed change) and builds a database.
   CI服务器会不断监视远程存储库中可能的更改（例如，每隔几分钟，几小时等）。 一旦检测到提交的更改，CI服务器就会触发一个构建脚本，该脚本从存储库中提取最新状态（包括新提交的更改）并构建数据库。
3. The new database is populated with some static data
   新数据库中填充了一些静态数据
4. Unit tests are executed automatically, as soon as the database is built and populated with the static and data. Additionally, any rule that must be followed inside the team can be set and checked in order to keep the consistency of SQL code and internal coding standards.
   一旦建立数据库并使用静态和数据填充后，单元测试就会自动执行。 此外，可以设置和检查团队内部必须遵循的任何规则，以保持SQL代码和内部编码标准的一致性。
5. CI server sends the feedback to the development team in form of test results. Those can be sent to developers once the test is finished (if all tests passed), or on the first failed test.
   CI服务器以测试结果的形式将反馈发送给开发团队。 可以在测试完成（如果所有测试均通过）后，或在第一个失败的测试上将这些发送给开发人员。
6. Additionally, each version of a built SQL database can be documented in a user friendly and readable format so the team can have an information about the database development process at any point and to be able to inspect some specifics, like object dependencies.
   此外，可以以用户友好且可读的格式记录构建SQL数据库的每个版本，以便团队可以随时获取有关数据库开发过程的信息，并可以检查某些细节，例如对象依赖性。
7. Developers are informed about possible errors not long after they committed changes that triggered the entire process. This helps to review the changes, investigate and fix the problems quickly before moving forward to the next task. This ensures that changes that can break the database cannot pass to test, because the database will not be even built until all tests are passed.
   在提交触发整个过程的更改后不久，开发人员就会被告知可能的错误。 这有助于在继续进行下一个任务之前，查看更改，快速调查并解决问题。 这样可以确保不会破坏数据库的更改无法通过测试，因为直到通过所有测试，数据库才会被构建。
8. As soon as all tests are passed, the CI server invokes the deployment mechanism that uses the tested database and deploy it directly to QA
   一旦通过所有测试，CI服务器就会调用使用已测试数据库的部署机制，并将其直接部署到QA

一些CI最佳做法： ( Some CI best practices: )

Frequent committing of changes. Committing is recommended after each change, or after a task is done (assuming that a single task is atomic enough and it can be finished in a few hours). Worst case, code should be committed nightly with all logical work units reduced to tasks under one day’s worth of work.

经常进行变更 。 建议在每次更改后或完成任务后提交（假设单个任务足够原子，可以在几个小时内完成）。 最坏的情况是，应每晚执行代码，将所有逻辑工作单元的工作量减少为一天的工作量。

Less/no committing of broken code. The best way to prevent bad code from getting into a build is to stop it at the commit. Developers should run tests on their local database to ensure changes haven’t broken the database. They should also carefully review the new code, objects to ensure standards have been followed, format is acceptable, and quality, in general, is acceptable. This can be done manually, or in some cases with automated processes that review an object when a developer checks it in, and prevents the check-in if problems are detected

以下/无 犯 的 断码。 防止不良代码进入构建的最佳方法是在提交时停止它。 开发人员应在其本地数据库上运行测试，以确保所做的更改不会破坏数据库。 他们还应仔细检查新代码，对象以确保遵循标准，格式可接受并且质量一般。 可以手动完成此操作，或者在某些情况下，可以使用自动化流程来进行操作，这些流程会在开发人员签入对象时检查对象，并在检测到问题时阻止签入

Quick bug fixing turnaround. If an issue is detected on QA, it should be resolved or reverted quickly, ideally not longer than 24 hours

快速修复错误。 如果在质量检查中检测到问题，则应尽快解决或恢复，最好不要超过24小时

Automated test coverage. Each new database object, whether it is newly introduced or improved from the previous version, should be covered with unit tests and the acceptance criteria, in terms of pass rate of percentage of passed tests, should be sufficiently high to ensure good downstream quality. Also, automated tests that validate code consistency, redundancy and other rules should be included in the automated process.

自动 测试 覆盖率。 每个新的数据库对象，无论是新引入的还是从先前版本改进的，都应进行单元测试，并且接受标准（通过测试的合格率百分比）应足够高，以确保良好的下游质量。 此外，自动化过程中应包括验证代码一致性，冗余性和其他规则的自动化测试。

翻译自: https://www.sqlshack.com/introduction-to-sql-server-database-continuous-integration/