SQL Server Database Continuous Integration (CI) Best Practices And How To Implement Them – Testing, Processing And Automation

Source:- glassgraden.com

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 SQL Server database continuous integration (CI) Best practices and how to implement them – Source control.

<span< a=””>>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.

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.

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.

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.

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.

<span< a=””>> 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.

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.

<span< a=””>> 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.

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.

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.

<span< a=””>> 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.

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.

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.

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.

Because of this, we want to abort our CI/CD process on failure and immediately send email notifications vs continuing to cycle the pipeline.

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.

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.

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.

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.

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.

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.

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.

Continuous integration process must be available to be executed 3 ways

1. Manually
2. On a schedule
3. On commits

Automation

No plain text passwords should exist in automation scripts

1. Use Windows authentication e.g. Integrated security
2. Or encrypted passwords
3. 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.

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.

But for unattended processes, e.g. jobs scheduled to run overnight, you will have to use some form of password encryption.

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, <span< a=””>> ApexSQL Generate can be launched with a project file pre-configured to connect to a database and generate test data for it.