测试中 (Testing)
Test databases should be processed with unit tests
In many shops code is unit tested at the point of commit. For databases, I prefer running all unit tests at once and in sequence against a QA database, vs development, as part of a Test step, in my continuous integration workflow pipeline. Yes, issues would be caught later than at check-in, but continuous integration largely solves this with frequent iterations, including at a commit itself. So the difference between on-check in, unit testing and unit testing a build created on-commit, is simply that the unit tests will be run against a fully re-constituted QA database, vs Development. The previous article in this series is测试数据库应与单元测试一起处理
在许多商店中，代码是在提交时进行单元测试的。 对于数据库，在连续集成工作流管道中，相对于开发，作为测试步骤的一部分，我更喜欢一次针对QA数据库依次运行所有单元测试。 是的，问题会比签到延迟发现，但是持续集成很大程度上可以通过频繁迭代（包括在提交本身）来解决。 因此，签入，单元测试和在提交时创建的构建单元测试之间的区别仅仅是，单元测试将针对完全重构的QA数据库与开发进行对比。 本系列的上一篇文章是 SQL Server database continuous integration (CI) Best practices and how to implement them – Source control. SQL Server数据库连续集成（CI）最佳实践以及如何实现它们-源代码管理 。ApexSQL Unit Test is a tool that integrates with the tSQLt framework to create and execute SQL Server unit tests. It can also be automated as part of the Test step for any SQL Server database continuous integration workflow pipeline.
ApexSQL单元测试是与tSQLt框架集成以创建和执行SQL Server单元测试的工具。 对于任何SQL Server数据库连续集成工作流管道，它也可以作为“测试”步骤的一部分进行自动化。
Unit tests and test scripts should be in source control
单元测试和测试脚本应在源代码控制中
The test step is a critical part of any CI/CD process. Test scripts, including unit tests themselves, should also be versioned in source control, extracted at the point of the Build step and executed. As changes are made to these test scripts they can be versioned, with the confidence that all changes will be implemented as part of the next build.
测试步骤是任何CI / CD过程的关键部分。 测试脚本（包括单元测试本身）也应在源代码控制中进行版本控制，在“构建”步骤中提取并执行。 在对这些测试脚本进行更改时，可以对它们进行版本控制，并确信所有更改都将在下一个版本中实现。
The test step should include a “Pull” out of source control, as well as the execution of the tests themselves. Unit tests can be kept in a separate branch of the source control project e.g. /tests to avoid inadvertently applying them to production.
测试步骤应包括脱离源代码控制的“拉”，以及测试本身的执行。 单元测试可以保存在源代码管理项目的单独分支中，例如/ tests，以避免将它们无意中应用于生产。
Production data shouldn’t be used to test
生产数据不应用于测试
Pulling data from production is appealing as a quick expedient, but is never a good idea. Touching a production database to feed a QA database violates the principle of one-direction, in that our workflow pipeline should start at a baseline of 0 and work right, towards production, only stopping on failure or for manual reviews. Even creating a one-time reservoir of test data from production, creates privacy issues as such data could be exposed to developers, contractors etc.
从生产中提取数据是一种快速简便的方法，但是从来都不是一个好主意。 接触生产数据库以供QA数据库使用违反了单向原则，因为我们的工作流管道应从0的基线开始，直到生产成功，直到失败或进行人工检查才停止。 即使从生产中创建一次性的测试数据存储库，也会产生隐私问题，因为此类数据可能会暴露给开发人员，承包商等。
The best approach is using a tool or script to quickly, repeatedly and reliably create synthetic test data for your transactional tables (we want to pull static data from version control itself). With such a system, we can configure exactly the amount of data for each table, ensure it is sufficiently randomized but also, when needed as realistic as possible.
最好的方法是使用工具或脚本为事务表快速，重复和可靠地创建综合测试数据（我们希望从版本控制本身中提取静态数据）。 使用这样的系统，我们可以为每个表准确配置数据量，确保将数据充分随机化，而且在需要时也尽可能地切合实际。
ApexSQL Generate is a SQL Server database synthetic test data generation tool that can automatically create data for transactional type tables. It fits perfectly into the Populate step of a SQL CI workflow pipeline, and can populate all remaining tables that haven’t already been filled with Static data from source control as part of the Build step.
ApexSQL Generate是SQL Server数据库综合测试数据生成工具，可以自动为事务类型表创建数据。 它非常适合SQL CI工作流管道的“填充”步骤，并且可以作为“构建”步骤的一部分填充所有尚未用源代码控制中的静态数据填充的剩余表。
Unit tests should produce a success or failure run
单元测试应产生成功或失败的结果
Running unit tests to produce manual summary results for human consumption defeats the purpose of automation. We need machine readable results, that can allow an automated process to abort, branch and/or continue.
运行单元测试以产生供人食用的手动汇总结果无法达到自动化的目的。 我们需要机器可读的结果，这可以允许自动化过程中止，分支和/或继续。
ApexSQL Unit Test can be run via the command line, so it fits perfectly into the CI/CD Test step. It generates return codes based on execution failure or job success/failure. So assuming the application processes without error, it will return a code based on the collective results of the unit tests of either Success or Failure. This code can be consumed by the calling application or script, to abort on failure (see next) or proceed to the next step on success.
ApexSQL单元测试可以通过命令行运行，因此非常适合CI / CD测试步骤。 它根据执行失败或作业成功/失败生成返回码。 因此，假设应用程序处理没有错误，它将基于成功或失败的单元测试的总体结果返回一个代码。 调用应用程序或脚本可以使用此代码，以在失败时中止（请参阅下一步）或在成功时继续进行下一步。
Unit tests should fail on a threshold
单元测试应该失败
Running a CI process which requires 100% of all tests to pass, is akin to not having CI at all, if the workflow pipeline is set up atomically to stop on failure, which it should (see next). Otherwise, regardless of the test results, your build will require manual review of testing results to determine if it as a Green (aka clean) build.
如果将工作流管道自动设置为在发生故障时停止，则运行需要100％所有测试通过的CI流程类似于根本没有CI（请参见下一个）。 否则，无论测试结果如何，您的构建都将需要手动检查测试结果，以确定其是否为绿色（也称为纯净）构建。
To thread the needle, tests should have built in thresholds, that will raise an error based on either the % of tests failing or in some cases, if certain high priority tests fail.
要穿线，测试应该内置阈值，这将基于测试失败的百分比或某些情况下（如果某些高优先级测试失败）而产生错误。
ApexSQL Unit Test can be configured to set a threshold for success e.g. 80%, so that it will only raise a status of failure if less than the threshold % of tests fail.
ApexSQL单元测试可以配置为设置成功阈值，例如80％，以便仅当测试失败阈值％以下时，它才会引发失败状态。
处理中 (Processing)
Each step should produce a clear success or failure results
每个步骤都应产生明确的成功或失败结果
It should go without saying that all processes should ultimately produce a Boolean result of pass or fail, but some non-automated processes can easily find their way into your CI workflow pipeline. Unit testing is a good example, as it is often configured to produce results and summaries for human, not machine consumption. And even when results like “99% of tests are passed”, are produced, it isn’t rendered into something the calling process can consume and branch, other than parsing for numbers and applying logic to it. We shouldn’t have to do that. Software should be plug-n-play into any workflow pipeline, taking known inputs and producing expected outputs – like pass, fail.
毋庸置疑，所有进程最终都应该产生布尔结果，即通过或失败的布尔结果，但是某些非自动化的进程可以轻松地找到其进入CI工作流程管道的方式。 单元测试是一个很好的例子，因为它通常被配置为产生用于人为的结果和摘要，而不是机器消耗。 而且即使产生了诸如“ 99％的测试通过”之类的结果，除了解析数字并对其应用逻辑之外，它也不会呈现为调用进程可以使用和分支的东西。 我们不必这样做。 软件应在任何工作流管道中即插即用，获取已知输入并产生预期输出，例如通过，失败。
All ApexSQL console applications produce detailed return codes that can be evaluated to branch or abort the CI/CD process. At each step in a CI/CD workflow a return code will be raised, to allow the process to abort on error.
所有ApexSQL控制台应用程序都会生成详细的返回码，可以对返回码进行评估，以分支或中止CI / CD进程。 在CI / CD工作流程的每个步骤中，将引发返回码，以使过程在出错时中止。
No process should run, past the point of failure
超过故障点的任何过程都不应运行
SQL Server database CI workflow pipelines can involve many steps. And more if CD steps are automatically appended and/or the CI workflow spawns multiple, parallel pipeline, for example, one for performance/stress testing, one for unit tests, one for static testing vs rules and policies, these processes can consume considerable amounts of time and resources.
SQL Server数据库CI工作流管道可能涉及许多步骤。 而且，如果自动附加CD步骤和/或CI工作流产生多个并行管道，例如，一个用于性能/压力测试，一个用于单元测试，一个用于静态测试与规则和策略的管道，则这些过程可能会消耗大量的资源时间和资源。
Because of this, we want to abort our CI/CD process on failure and immediately send email notifications vs continuing to cycle the pipeline.
因此，我们希望在失败时中止CI / CD流程，并立即发送电子邮件通知，而不是继续循环管道。
The CI process should not cycle again until any errors in the last build are fixed
在修复上一个版本中的任何错误之前，配置过程不会再次循环
The only thing worse than a failed build, is a problem with the database or build process itself, that unless fixed, will cause all subsequent processes to fail. Any failure should trip a circuit breaker, that unless manually overridden, should prevent any sub-sequent CI workflows from initiating. This circuit breaker can simply be to flip a flag from 0 to 1 in a database table. Running the workflow manually will bypass the circuit, but if it completes it will automatically reset it.
比构建失败更糟糕的是，数据库或构建过程本身存在问题，除非修复，否则它将导致所有后续过程失败。 任何故障都应使断路器跳闸，除非手动覆盖，否则应阻止任何后续的CI工作流启动。 该断路器可以简单地将数据库表中的标志从0翻转到1。 手动运行工作流程将绕过电路，但是如果完成，它将自动将其重置。
Provide team notifications, before and after builds and on error
在构建之前和之后以及出现错误时提供团队通知
Many CI processes have alerts on pipeline initiation to the entire development team, so that they won’t check in files, until they get a notification that the build window is closed, based on their pipeline environment, build requirements etc. My preference is to put in a delay in the on-commit build process to never cycle more frequently, than it takes for the entire build process to complete. This governor, eliminates no-check-in blackouts which can be disruptive an annoying.
许多CI流程都会向整个开发团队发出有关管道启动的警报，因此他们不会检入文件，直到他们根据管道环境，构建要求等收到有关关闭构建窗口的通知。延迟提交时构建过程的周期永远不会比完成整个构建过程所花费的时间更频繁。 该调速器消除了可能导致烦人的无登记入住停电。
Annoying or not, such notifications on initiation of a pipeline, may be a requirement for QA, unless a failover system is in place where the new QA environment doesn’t over-write the old one, but is created as a separate build. In such a case where the QA environment is off-line, during a build the Notify step at Pipeline initiation should include QA.
不管是否烦人，此类有关管道启动的通知都可能是QA的要求，除非有一个故障转移系统，其中新的QA环境不会覆盖旧的QA环境，而是将其创建为单独的内部版本。 在QA环境处于脱机状态的情况下，在构建期间，管道启动时的“通知”步骤应包括QA。
Less is more, when it comes to success notifications. If error alert was not received, it can be assumed that the new build was created, and QA should be able to seamlessly segue to testing the new build. In such cases, again, only QA should get these notifications.
关于成功通知，少即是多。 如果未收到错误警报，则可以假定已创建新的构建，并且QA应该能够无缝地进行测试以测试新的构建。 同样，在这种情况下，只有质量检查人员才能获得这些通知。
On failure, the entire team should get the failure notification, including as many details as to what failed as possible.
发生故障时，整个团队都应获得故障通知，包括尽可能多的故障详细信息。
The “One-click CI” script that runs a complete Continuous integration pipeline with PowerShell, includes email notification lists by group (QA, Devs, Manager) and sends out alerts on pipeline initiation, completion and error to the appropriate groups. This feature is also built into the ApexSQL Developer CI/CD dashboard as well as our ApexSQL Build server plug-ins, e.g. Jenkins.
“一键式CI”脚本可使用PowerShell运行完整的持续集成管道，包括按组（QA，Devs，Manager）分组的电子邮件通知列表，并将有关管道启动，完成和错误的警报发送到适当的组。 ApexSQL Developer CI / CD仪表板以及我们的ApexSQL Build服务器插件（例如Jenkins）也内置了此功能。
Build frequency > build process time
构建频率>构建过程时间
Builds should not be created more frequently than the time it takes for a build to actually complete. In doing so, you will create pipeline collisions, if you are working asynchronously. Even if you have parallel build pipelines, the value of processing a complete build for each check-in, is dubious, in an environment with frequent check-ins.
构建的创建时间不应超过构建实际完成所花费的时间。 这样做的话，如果您是异步工作的话，将会产生管道冲突。 即使您具有并行构建管道，在频繁签入的环境中，为每个签入处理完整构建的价值仍然是可疑的。
If a pipeline takes 1 hour, from start to finish, to complete, including all testing, then all the build interval should be set to no less than one hour and all new commits should be queued, and applied the next build.
如果一个管道从开始到完成，包括所有测试，需要1个小时，那么所有构建间隔都应设置为不少于一个小时，并且所有新提交都应排队，并应用下一个构建。
The “One-click CI” script that runs a complete Continuous integration pipeline with PowerShell, can be scheduled to run at any interval. Similarly, the ApexSQL Developer CI/CD dashboard can also be scheduled by frequency, whether the build is scheduled, on some interval, or initiated by commit. Even if initiated by commit, the job will still run on an interval, not less than what is specified in the configuration settings.
可以计划在任何时间间隔运行“一键式CI”脚本，该脚本使用PowerShell运行完整的持续集成管道。 同样，ApexSQL Developer CI / CD仪表板也可以按频率进行调度，无论构建是按计划进行调度，按一定间隔进行还是通过提交进行启动。 即使由提交启动，作业仍将以一定间隔运行，间隔不小于配置设置中指定的间隔。
Continuous integration process must be available to be executed 3 ways
持续集成过程必须可以执行3种方式
- Manually 手动地
- On a schedule 按时间表
- On commits 提交时
自动化 (Automation)
No plain text passwords should exist in automation scripts
自动化脚本中不应存在纯文本密码
- Use Windows authentication e.g. Integrated security 使用Windows身份验证，例如集成安全性
- Or encrypted passwords 或加密的密码
- Or – in case of manual execution, prompt for credentials 或者–如果是手动执行，则提示输入凭据
It’s all fun and games until someone puts a plain text password in an automation script. Fortunately, Windows authentication aka Integrated security, solves many of our automation problems vis a via SQL Server and/or repository credentials. In cases where you must use SQL Server authentication, you have to do some extra work.
除非有人在自动化脚本中输入纯文本密码，否则所有这些都是有趣的游戏。 幸运的是，Windows身份验证（也称为集成安全性）通过SQL Server和/或存储库凭据解决了许多自动化问题。 如果必须使用SQL Server身份验证，则必须做一些额外的工作。
For attended processes, this is relatively easy. Simply add a process to prompt for input to your existing PowerShell script or batch code, to accept credential information. The script to prompt for a User ID and Password in PowerShell check out articles How to create a simple SQL Server database login dialog using PowerShell and How to create a login form dialog using PowerShell accept user input and pass it to an ApexSQL console application.
对于有人参与的流程，这相对容易。 只需添加一个过程以提示您输入现有的PowerShell脚本或批处理代码，即可接受凭据信息。 在PowerShell中提示用户ID和密码的脚本签出文章如何使用PowerShell创建简单SQL Server数据库登录对话框以及如何 使用PowerShell 创建登录表单对话框接受用户输入并将其传递给ApexSQL控制台应用程序 。
But for unattended processes, e.g. jobs scheduled to run overnight, you will have to use some form of password encryption. You can also check out article Four ways of scheduling ApexSQL tools.
但是对于无人值守的流程（例如计划在夜间运行的作业），您将必须使用某种形式的密码加密。 您还可以查看文章调度ApexSQL工具的四种方法 。
All ApexSQL Project files encrypt passwords to allow you to store this information, and successfully connect to a database or source control repository, for any step in a CI process. For example, during the Populate step, ApexSQL Generate can be launched with a project file pre-configured to connect to a database and generate test data for it.
对于CI流程中的任何步骤，所有ApexSQL Project文件都将对密码进行加密，以使您可以存储此信息，并成功连接到数据库或源代码控制存储库。 例如，在“填充”步骤中，可以使用预先配置为连接到数据库并为其生成测试数据的项目文件启动ApexSQL Generate 。
1690
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
