Redgate Change Control 3

Data

When capturing database changes in version control to use in automated deployments, it's important to also capture transactional or static data changes as well as schema object changes.  Transactional data could be specifying a default when adding a new column or updating transactional data already in a Production database, e.g., increasing salaries by x% or splitting a column into two new columns.

In addition, it is important to version control and deploy changes to static data as well.  Static data is also known as configuration or lookup data.  It's usually small sets of data that need to be deployed alongside the application changes for the application to run correctly.  This can include data such as zip/postal codes, country lists, or even application settings.

Capturing changes as part of a versioned migration script

When you Generate migrations using Redgate Change Control, you select which changes in your development database you want to create scripts for, i.e., which changes are ready to be committed to version control.  After selecting which changes, there is a review step.  Any table structure changes are shown in a versioned migration script at the top of this page.  In addition to specifying the exact version and filename you want, you can also edit this script to include any data changes as well.  Since this script was generated from changes already on your development database, it will not be re-run against the development database.  Most users will write and execute the data change script against the development database in the IDE of their choice and then just copy and paste it into the script at this point.  This script can then be committed/pushed to version control and the whole script will be run in CI/CD environments with Redgate Change Automation (learn more about Deploying database changes).

Adding a new versioned migration script to the project folder

If you need to add a versioned migration script that doesn't correspond with any table schema changes, then simply navigate to the project folder using the link in Redgate Change Control and create or save your change script in this directory using the correct filename.  Make sure the filename starts with a capital V, the number you want it executed in, two underscores, and then a description, e.g., V003_20201022__UpdatingJobTitles.sql.

Using Data Compare for Oracle to generate changes 

You can use Data Compare for Oracle to generate a deployment script for data changes in static/lookup tables.  You can compare your development database to Production, or compare your development database to a scripts folder that represents the data in Production.  Once you generate the deployment script, simply save it into your project folder using the naming convention described above.  This can then be versioned controlled and flow through your automated deployment process to be deployed to your downstream environments (e.g., Test, QA, Stage, Production).

Learn more about how to use Data Compare for Oracle, which is part of Redgate Deploy (previously called the Deployment Suite for Oracle) on its documentation site.   



Didn't find what you were looking for?