Once the list of objects (which have changes that should be included in the script) are defined, the second step is to build the modification script which will transform the object from state A to state Z. This step involves logic that analyzes the database dependencies and generates the DDL, DCL, DML command in the correct order. The same logic exists in building native code (compiling) as developers manually maintain the dependencies between binaries and the compiler knows the order to compile and generate the binaries.

How to know which DB change relates to which task?

By correlating the check-in to the task.

Once the information regarding the reason for this change is saved in the repository, this information can be used to retrieve only the relevant changes & objects when building the list of objects.

How to know if the script should perform the change?

By running a baseline-aware analysis.

The baseline is a label created beforehand in the version control repository, which reflects the expected structure of the object, schema, and database; and highlights the nature of the change. If there is difference between source & baseline, it means the change of the object should be in the script. If there is a difference between target & baseline, it would mean the change of the object should not be in the script as the object was not changed in the source environment.

The same principle exists when several developers work on the same Java, C# or C++ code. When a developer checks-in his changes, the version control compares the latest revision of the object, the developer copy and the object baseline. The object’s baseline is the object’s revision which the developer checked-out (or started modifying). If there is a change between the latest revision and the object’s baseline, that would mean someone else already modified the object and the developer cannot check-in his changes. The developer first needs to resolve the conflict in his local copy and then do a check-in. A baseline is defined for every object and every check-in.

Automated Database Changes Execution

Executing the SQL script is quite simple, it can be done by command line utilities, by scripts, and by ARA (Application Release Automation) tools that know how to execute SQL scripts. However, the important thing in this step is to validate that the script can be executed safely. By not controlling who can make changes in which environment and not documenting the changes, scripts created few minutes ago may not be valid at execution time.

The database code can be modified in all environments. This requires the database in all environments to be under version control, which enforces the documentation of changes. Otherwise, someone can make a change (not using the normal process and without documenting it) in the QA or any test environment. The application will pass the tests but when the scripts will be executed in the production (without the out-of-process change) the application will break.

Automated Database Changes Test

The last (but not least) step, is tests. Testing the application logic, testing that the scripts are correct and testing the script execution. The tests can be done by checking the log files and searching for database errors, by running unit tests developed especially by developers/DBA, or by running the impact analysis again to get an empty script.