• Support
• Documentation
• BuildMaster
• Change Management
• Database Change Scripts

Database Change Scripts

Database change scripts are a special type of file that is run against a database to make changes to the data structures (tables) and the data. As a result of the fundamental nature of data and databases, a change script is irrevocable.

For example, a change script might remove a column to a table. In addition to permanently altering the table's structure, the data contained within the column are lost and cannot be retrieved. Obviously, a database may be restored from backup, but there may be additional changes to the data or structure that is not automatically reconcilable.

As a result of this unique nature, two cardinal rules of database change scripts arise:

1. Scripts can be run once and only once.
2. Script executions cannot be undone.

While not a cardinal rule, best practices dictate that each changes script should be run through each environment (just as source code changes would be) to ensure proper testing and verification of changes.

Note that database object code (stored procedure, views, etc) are not considered change scripts.

Database Change Script Development Process

The unique nature of database change scripts require that a slightly different process be used for development. Unlike code files, versioning database change scripts is pointless. Because each modification of a change script creates an entirely different change script, it's more important to control the execution of each change script in each environment.

This responsibility often falls towards the Database Administrator, who ensures that the cardinal rules are followed and rejects failed database change scripts, advising the developer to create a new one.

Database Change Scripts in BuildMaster

Through the database change script module, BuildMaster implements both cardinal rules and also tracks the executions of database change scripts.

Database Change Scripts in BuildMaster are associated with a deployable and a release, and track whenever they have been executed against a database. As a result of the immutable nature of data change scripts, modification of a script after it has been executed in an environment is not permitted.

If a database change script is deemed unacceptable (e.g. it failed in the first environment, or it was an incorrect change, etc), it may be set inactive (soft deleted) or permanently purged.

Database Integration

BuildMaster can support virtually any relational database engine through the use of database providers. Once a database provider has been configured, database change scripts may execute against it.

Change script executions are tracked through a light-weight table which is created after a database provider has been initialized within BuildMaster.

Adding a new Change Script

Change scripts can be uploaded into BuildMaster from the listing page.

Change Script Listing

Uploaded change scripts can accessed from the SQL Change Scripts link under the Configuration and Databases menu.

Permitted users may also download all the change scripts associated with a specified application.

Change Script Execution

Change script executions can be performed in one of two ways: either by manually executing the script in a specified environment, or utilizing the built-in Execute Change Scripts action during a deployment plan execution.

• Action-based executions are used when promoting builds into new environments. The Action will check for any change scripts that were not run in the Environment associated with the Deployment Plan, and will run any scripts that have not yet been run automatically.
• Manual execution of a database change script is possible, though should limited to exceptional circumstances such as emergency data fixes; like action-based execution, manual execution will prevent the same script from running against the same database twice.

Action-Based Change Script Executions

A script may also be executed as part of a deployment plan.

Manual Executions

A script can be executed from the overview page.

Execution History

BuildMaster logs all change script executions and stores data pertinent to the particular script: the Release Number associated with the script, the Environment in which the script was executed, the date and time of execution, and whether or not the execution was successful.

Database Change History

In addition to BuildMaster's record, databases store which change scripts have been executed against them. This is important, as a production database may be restored in a development environment, and therefore would not have the latest change scripts applied.