Migration scripts capture incremental changes to your development database.  These are SQL scripts that can capture schema and data changes (eg., CREATE, ALTER, INSERT, UPDATE, etc.).  They should be tracked in version control.  These scripts are used to deploy to your other environments in a consistent way.  The same scripts will be run in the same order on each environment, which gives you a repeatable deployment process.

Redgate Change Control allows you to manage and create database migration scripts.  We support the following kinds of migration scripts:

Versioned migration scripts 

Versioned migrations have a version number, a description, and a checksum.  They start with a V followed by a version number, two underscores, and a description (eg., V001.002__NewTwitterColumn.sql).  The version number must be unique. The description is purely informative for you to be able to remember what each migration script does. The checksum is there to detect if any changes have been made to the script since it was applied to the database.

Versioned migrations are applied to a target database in order exactly once.  The redgate_schema_history table tracks which versioned migrations have already been applied and stores the checksum to make sure they haven't changed.  It is best practice not to make changes to existing versioned migrations once they have been applied to a permanent downstream environment.  It's best to create a new versioned migration script and roll forward any necessary changes. 

Repeatable migration scripts

Repeatable migrations have a description and a checksum, but no version number. Instead of being run just once, they are (re-)applied every time their checksum changes.   They start with an R followed by two underscores and a description (eg., R__SetDefaults.sql).  The filename and checksum are also stored in the redgate_schema_history table so we know when the repeatable migrations need to be reapplied.

Programmable Objects

Programmable Objects are schema objects that can be treated like code.  This includes package specifications, package bodies, procedures, functions, triggers, views, and materialized views.  (Almost everything except for tables and sequences, which require more care because of the data they hold.)  By default, Redgate Change Control automatically scripts Programmable Objects as repeatable migrations.  This is helpful because you can see how these objects change over time using your version control system and also helps to keep the number of migration scripts down. This also helps identifies conflicts if different team members are making changes to the same object.

Programmable Objects start with an R followed by two underscores, the schema name, and then the object name (eg., R__schema.objectname.sql). 

Post-deployment script

When a new project is created, Redgate Change Control adds an `afterMigrate__fix_invalid_objects.sql` script, which contains the SQL code required to recompile invalid database objects after each successful migration. It is necessary because Repeatable Migrations are run in alphabetical order without taking into consideration dependencies. For existing projects, this `afterMigrate__fix_invalid_objects.sql` script is added to the project if it does not exist. However, if a script of this name already exists, then it will not be replaced.

Redgate Change Control is built on top of Flyway, if you want to add more scripts that hook into the deployment lifecycle like a Pre-deployment Script (beforeMigrate) or a script to run if something fails (afterMigrateError), then see the Callbacks information on the Flyway website.  Since Redgate Change Control is built on top of Flyway, it is aware of these scripts and will use them correctly, but it does not currently manage them in the Redgate Change Control GUI so they must be managed externally if required for your purposes.  If you'd like to use any of these additional features, please contact our team so we can consider adding support within Redgate Change Control.  

Undo Migration Scripts

We are considering adding built-in support for undo migration scripts.  Undo migration scripts allows you to write a down script for each versioned migration script to help with rolling back changes.  Our built-in support would automatically generate the undo migration script when you're generating the versioned migration scripts.  If you are interested in this feature, please contact our team.

Since Redgate Change Control is built on top of Flyway, you can manage Undo migration scripts outside of Redgate Change Control for now.  You can learn more about Flyway Undo Migrations on the Flyway website.  Undo migration scripts start with a U, followed by the same number as the versioned migration script that it corresponds to, two underscores, and then a description (eg., U001.002__UndoNewTwitterColumn.sql).

Order of execution for Migration Scripts

Within a single migration run, all pending versioned migrations are applied in order first followed by any repeatable migrations that have changed.  Repeatable migrations are applied in the order of their description.  To keep track of which migrations (versioned and repeatable) have already been applied, when and by whom, a redgate_schema_history table is added to the first schema specified in your project. At the end of a successful migration run, the post-deployment script described above is run.

In Oracle, if a migration script fails, then the redgate_schema_history table will indicate which script has failed and will stop executing any remaining scripts.  In order to fix the failure, please see the Troubleshooting section to learn about how to repair the problem.

Migration script states

Because Redgate Change Control is built on top of Flyway, you may come across a number of different migration script states when working in Redgate Change Control. The most common ones include pending (i.e. the script has not yet been applied), success (i.e. the script was applied successfully) and failed (i.e. the script failed during migration). You may also occasionally see some less common states such as future (i.e. a migration that has been applied but cannot be found on disk and is newer than all existing migrations) and missing (i.e. a migration that has been applied but cannot be found on disk and is not newer than all existing migrations). More details on these states can be found here: https://flywaydb.org/documentation/migrations#migration-states.