Databases: Five deployment errors with potential for drama

Database administrators (DBAs for short) have their hands full. No wonder: database environments are growing exponentially and the underlying infrastructure is becoming increasingly complex. Added to this are recurring errors in operation, for which Redgate has evaluated practical solutions.

Error #1: Manual reviews

Manual reviews (of SQL code, for example) are subjective, often tedious and dependent on individual people. Database administrators check scripts ad hoc and often without clear standards or context for previous changes. This leads to inconsistencies, increases the error rate and, in the worst case, blocks the rollout of databases. In this way, the review process becomes a bottleneck, increases the change lead time and reduces the deployment frequency, while at the same time increasing risks due to incomplete checks.

Solution: Instead of manual reviews, companies should rely on automated coding standards, static code analysis and dry-run deployments. They should generate migrations automatically using dedicated tools and have them tested on production environments prior to rollout. In addition, pull requests with pipeline checks ensure reproducible checks, consistent quality and audit trails. In this scenario, database administrators only check exceptions manually and no longer check every script.

Error #2: No central deployment tracking

Without centralized tracking, database teams do not know which changes have been made in which environment. As a workaround, so-called idempotent «do-everything scripts» are usually created, i.e. scripts that deliver the same result when executed multiple times, with many IF-checks. Over time, these become increasingly complex, slower and more difficult to predict. Different states between database environments lead to unclear executions and make debugging and recovery considerably more difficult.

Solution: Companies should replace idempotent scripts with versioned migrations with a clear audit trail per environment. Furthermore, it is necessary to log every deployment, but only execute pending changes. You should also implement dry-run scripts that show exactly what is being executed. This makes deployments deterministic, traceable and easier to track, while reducing complexity.

Error #3: Schema drift between environments

Direct changes to individual environments lead to so-called schema drift. For example, if database administrators and developers manually change tables or columns, test, staging and production systems deviate from each other. Deployments then fail unexpectedly or behave differently than tested. As a result, tests become less meaningful and rollbacks become risky as the actual status is unknown.

Solution: Automated drift detection compares target schemas with the expected status and reports deviations at an early stage. Consistent monitoring simultaneously detects direct DDL (Data Definition Language) changes. In addition, test environments are regularly rebuilt or updated from a reference. This keeps environments consistent and deployments predictable.

Error #4: Missing rollback strategy

A lack of rollbacks means that teams have to improvise in the event of errors. However, database changes cannot always be easily reversed, which is particularly the case with data migrations or strong dependencies. Manual rollbacks are also slow, risky and error-prone. A lack of a rollback strategy therefore increases downtime and causes teams to avoid deployments for fear of making mistakes.

Solution: Automatically generated and tested in the CI/CD Undo-scripts create a clearly defined rollback option. Rollbacks are thus validated in advance and backups are integrated as a step before the final deployment. At the same time, a rollforward strategy that consists of small releases, quick fixes and feature flags, i.e. the option to (de)activate functions at runtime without code changes, makes sense. In this way, companies reduce the risk and, in case of doubt, speed up recovery.

Error #5: Blind spots in post-deployment monitoring

With frequent releases, it becomes difficult to assign performance problems to a deployment. Without a connection between monitoring and deployments, regressions remain undetected until users report them. Missing baselines and many simultaneous changes also make diagnosis more difficult and significantly extend the recovery time.

Solution: Deployment annotations in monitoring link releases with performance metrics. Teams can then see immediately whether CPU, memory or query changes occur after a deployment. In addition, health checks before the final deployment prevent releases on unstable systems. Another positive effect of this approach is that database developers receive early feedback and can therefore identify and rectify problems more quickly.

«Nobody wants drama when deploying databases - least of all the administrators who have to run them,» emphasizes Oliver Stein, Managing Director DACH at Redgate. «That's why it's important to use the right strategies and tools to nip typical problems in the bud and prevent them from arising in the first place.»

Source: www.red-gate.com/de

This article originally appeared on m-q.ch - https://www.m-q.ch/de/datenbanken-fuenf-deployment-fehler-mit-drama-potenzial/