Deployment Automation: How to handle data implications of schema changes
mbruegel
Posts: 43 Bronze 2
We are in the process of working toward an automated deployment process using RedGate's tools for Oracle.
A question that has come up is how can we most effectively manage the data implications of structural schema changes in one seamless scripted deployment?
Assuming an existing database / schema populated w/ data, how to handle issues like:
- moving a column, and its associated data, from table A to table B
- adding a new not null column to a table, where the new column value cannot be determined by a default value or simple function (i.e. assume a complex procedure is required to determine the new column value)
I assume one strategy might be to break the deployment down into separate steps:
- Use RedGate to deploy DDL changes to a point (halt / break in the deployment process)
- perform necessary data migration procedures manually (i.e. outside of RedGate automation)
- Resume w/ the use RedGate to deploy the remainder of DDL changes
I note that this approach would require some significant planning (i.e creating & managing multiple source scripts)
How have others dealt with this challenge?
As far as I can determine RedGate tools do not allow for the interjection of custom (DML) scripts at during the deployment process.
- martin
A question that has come up is how can we most effectively manage the data implications of structural schema changes in one seamless scripted deployment?
Assuming an existing database / schema populated w/ data, how to handle issues like:
- moving a column, and its associated data, from table A to table B
- adding a new not null column to a table, where the new column value cannot be determined by a default value or simple function (i.e. assume a complex procedure is required to determine the new column value)
I assume one strategy might be to break the deployment down into separate steps:
- Use RedGate to deploy DDL changes to a point (halt / break in the deployment process)
- perform necessary data migration procedures manually (i.e. outside of RedGate automation)
- Resume w/ the use RedGate to deploy the remainder of DDL changes
I note that this approach would require some significant planning (i.e creating & managing multiple source scripts)
How have others dealt with this challenge?
As far as I can determine RedGate tools do not allow for the interjection of custom (DML) scripts at during the deployment process.
- martin
Tagged:
Answers
We're currently investigating options for automated deployment in Oracle environments similar to those found in the SQL Server tools which should include a way to accommodate maintaining data.
I don't currently have a time frame for this unfortunately, but do keep an eye out for things coming up!
I hope that helps!
Kind regards,
Alex
Have you visited our Help Center?
I'm the Product Manager for Database DevOps. As Alex mentioned, we're currently looking into this to cover those cases you described and give teams more control over the deployment script, so that you do not have to go outside of the RG process. If you'd like to be part of our Early Access Program, please let me know (stephanie.herr @ red-gate.com).
The only other way I've seen this done with the current capabilities in the Deployment Suite for Oracle is to use an idempotent Pre/Post script process that is orchestrated through your release management system.
Stephanie M. Herr :-)
Product Manager Database DevOps