Trying to enumerate major classes of issues caused by lack of coordination in migrating SQL changes between the environments:
- “Incompatible Changes”: Adam makes a schema change for release X. Bob makes a schema change for release Y. When release X is deployed to Testing, Adam’s change is applied fine but when release Y goes to Testing, Bob’s change fails since it was not designed to be applied on top of Adam’s. If two releases are tested in different environments, this issue may not be found until the integration testing of release Y.
- “Broken Script Sequence”: As schema change scripts are developed, they may go through multiple rounds of modifications. If the sequence of scripts is never ran in its entirety, any incompatibilities between the individual scripts may not be discovered until entire sequence is deployed to PROD. A variation of this issue occurs when all or some scripts are not numbered and therefore may run out of order.
- “Reversed Change”: Adam changes SP1 for project X but only stores it to project X-specific script folder. Bob changes the same SP1 for project Y. Adam’s change is deployed first. When Bob’s change is deployed, it reverses Adam’s change since Bob’s change used older version of SP1 as its basis.
- “Premature Change”: Adam changes SP1 for project X and stores it in the common location called “BASE”. Bob takes SP1 from BASE and adds changes for project Y. This time around Bob’s change is deployed first. However, since it also includes Adam’s changes, it breaks project X, since related binary changes are not yet deployed.
- “Incompatible Changes (Direct PROD Modification)”: Adam develops a schema change using DEVDB as the base. Meanwhile DBA makes some changes directly in PROD. When Adam’s change is deployed, the script fails since it’s not compatible with changes made directly in PROD.
- “Change Fails When Applied Second Time”: As changes are deployed and redeployed to Test, some of them may be applied more than once. Usually this is solved by adding IF conditions to the script that verifies whether the column or table already exist. However if you think about it, this scenario is really a specific case of Broken Script Sequence.
- “Duplicate Metadata”: As issues are found and fixed in Test, some scripts may end-up applied more than once. While schema-changes tend to fail fast, and SP changes can usually be applied on top, meta data changes end-up getting applied twice, and thereby duplicated. Usually this is solved by adding IF conditions to the script that verifies whether the meta-data already exist. However if you think about it, this scenario is really a specific case of Broken Script Sequence.
- “Script Partially Completes”: Each individual script file usually has multiple schema or data modifications in it. If one of those modifications fails due to Incompatible Changes or Broken Script Sequence, the script keeps running on with some of subsequent scripts failing. This results in the database being in an unknown state, and requires manually intensive labor to research and repair.
- “SQL Server Dies of SP Recompilation”: If all SPs in the database, whether modified or not, are simply (re-)applied during deployment, SQL Server may become slow or irresponsive due to SP recompilation.
- “Lost Change (manual version)”: As issues are found during Testing and fixed, new scripts may be created by developers and added to the script sequence. The person deploying the scripts may mistakenly think that a script has been deployed before, and not deploy it, resulting in a lost change.
- “Lost Сhange (automated version)”: The team is using an automated tool that tracks which scripts have been deployed. As issues are found during Testing and fixed, some scripts may require modifications. If developer modifies a script that has been applied before (instead of adding a new one), the change will not get deployed, since this script will be skipped by the tool as previously deployed.
- “QUOTED_IDENTIFIERS”: If some scripts do not specify values of certain SQL options (e.g. QUOTED_IDENTIFIERS etc.), or specify different values, the tables and queries may be created with conflicting settings, which may result in hard to debug errors.
Will add more here as I discover them.
Tags: