Database integration is one of the toughest parts in Integration. In this post I will review some keynotes in DI.
Firstly, all of the files related to DI should be source controlled. Ensure that your database scripts have been tested and verified.
Secondly, let’s list all of the reproducible database steps.
1. Delete a database.
Delete a database and its data so later you can create a new database with the same name.
2. Create database.
Use DDL files to create new database.
3. Import data.
Use insert/import/etc. scripts to import data.
4. Migrate database and its data.
Migrate database schema and its data to a new environment.
5. Modify database objects.
Use DDL files to modify database objects.
6. Update testing data.
7. Backup/restore data.
My experience is,
For every build, I need to run the step 1, 2, 3 in an automated flows (basically this step will take 2-3 days), and then in step 4, I will use Oracle VM system to templatize my base environment as a template, so DEV/Testers can just clone their environments from the template and then they can have the same data with the base environment in minutes.
For step 5,6 we use Oracle Database Edition Technology. Every time we need to apply DDL and DML, we will apply them in the PATCH edition and only after that is successful we will cut over it to RUN edition.
For step 7, we just simply use oracle imp/exp tool to implement that.
During database development, DBA should not involve into data migration because this part should be strictly handled by automated flows and DBA should take part in Database performance and other priorities. And by providing DEV a template, DEV can have a clone environment in mnutes and we call those environments as Sandbox in that way DEV can develop independently.