Using clones to "re-baseline" a SQL Change Automation Project
Published 15 November 2020
Easier management of migrations-based database projects, over time.
Integrating clones into the database build and deployment process makes it much simpler to consolidate migration scripts.
This automation requires use of SQL Change Automation as well as SQL Clone.
When you start a DevOps project to improve one of your legacy database applications, it can require a lot of database refactoring to support new functions and improve the performance of existing ones. Assuming you've overcome any existing 'build blockers', your starting point, in SQL Change automation, would be to capture a baseline of your deployment target, in the form of a script.
You start development, running series of sprints, and making small, controlled deployments most days. This is the correct approach but some months later, you find that you have thousands of migration scripts, and that verifying a deployment for the 1500th "migration" starts to take too long, because SCA has to build the shadow database from scratch, from what may be a complex baseline script, and then run all 1499 of the previous ones in the right order, on this 'baseline'.
Ideally, you want to 'rebaseline' your SCA migrations project, meaning to update the 'baseline' so that it accounts for the previous 1499 migrations that have already been deployed to the target. What this does,, is reimport the schema from the deployment target, use it to build the shadow database, compare this to your project and recalculate your migrations, essentially 'compressing' your previous 1499 migrations scripts into a single migration script (starting again from 0001.sql). However, this approach is problematic since migration scripts are intended to be immutable, once deployed. Also, you will lose any custom logic added to previously-deployed scripts, such as to preserve existing data. The suggested approach ,instead, is "reorganization", simply concatenating a series of migration scripts into a single file (see Reducing the number of migration scripts).
However, when using a clone as a baseline, rebaselining is much simpler and can all be automated with PowerShell. The process might be as follows:
- Ensure that all of your target databases are updated - all development, test, production and external databases etc. must be upgraded to the version of the database in your
mainbranch, and not contain new changes from any feature branches.
- Create a new image from the latest version of your production database -with a different name than the existing image (e.g. MyImage and MyImage_New) and then switch it in for the existing image (e.g. rename MyImage to MyImage_old and then MyImage_New to MyImage).
- Move any existing clones over to the new image - this will drop the clones and recreate them from the new image, so you'll either need to save off any uncommitted changes to existing clones first, or you'll lose them.
SQL Change Automation will recreate the shadow database from the new image. Since this is taken directly from the production target, it will know, from the migration log, which migrations have already been applied, and will not 'recalculate' any of this existing migration scripts. It only needs to verify any migrations that have yet to be deployed. For an example, see Automated Rebaselining with SQL Change Automation Clones.