Using DevOps Toolkit to compare Source Control objects as a source to a target Oracle DB and produce synchronization script
To apply synchronization script to a target DB.
I hope something like that would be possible with Schema Comparizon feature of DevOps Toolkit, but I cannot find it anywhere.
If that is not possible with this Toolkit, then what would you recommend to automatically deploy from source control to Oracle DB?
TDT can compare live schemas and produce a sync script, which you could run via TDT, but we don’t have anything to compare a live schema to source control. I don’t know if there is other software that can do that or not.
You have started an internal discussion though, so possibly a future version of TDT will do this. We’ll update this post if it’s going to happen.
for few years, I have been using your competitor’s SchemaCompare command line to do that for MSSQL. They introduced the same for Oracle recently, but the product lacks post and pre-deployment automation.
I hoped TOAD would do the trick.
For real CD/CI, the ability to compare source code to a target DB is critical. It creates current, not old sync script every time, so everything can be automatically performed. Any other solution I came by relies on sync scripts prepared earlier, therefor very often not current by the time of deployment. Additionally, such sync scripts very often were created against different version of the target schema.
CD/CI needs to deploy from source control, not from another DB environment.
DevOps Toolkit is a great idea, but it cannot meet this challenge.
For objects that you can “create or replace”, this wouldn’t be difficult to implement.
For tables, things that you have to either drop and create again, or alter…that’s where it gets tough.
Do you need the product to be able to look at a “create table” script in source control, comparing it to a table in a live schema…and come up with “alter table” statements to sync, or is it acceptable to drop and create the table in the live schema?
the way it works in the other product for us with MSQSQL, which I would suggest here as well:
for non-invasive table changes, you can use simple ALTER
for “invasive” changes like adding a not-nullable column to already populated table, there is an option to attach a “migration script” instead of an automatic change. So when SchemaCompare starts preparing sync script, such table is excluded from comparison and migration script runs instead.
Thing is that with real life CI/CD, majority of synchronizations are simple and automatic.
Even, if developer forgot to create migration script, it will come up with first automatic deployment in Integration environment. Then, source code can be corrected and next deployment will be fine.
There are a number of reasons we haven’t historically implemented a VCS to DB sync script. One is the John mentioned above about tables. Another reason is the fact that we have no control over the cleanliness of the contents of the VCS structure. A number of people set up Team Coding against existing VCS structures, which generally have other objects in it besides those controlled by Team Coding. Some people use the VCS as an archive: deleting objects in the database they no longer need because their history exists in the VCS as a backup. Attempting to sync those changes back to the database can easily include objects you don’t want included; and restricting the sync to only objects that already exist in the database would eliminate new objects from Dev, or objects you wish to restore.
That’s not to say his idea is a bad one – on the contrary, it’s something we’ve been thinking about how to accomplish! It’s just very potentially dangerous depending on how customers implement TC in their environment. It can work if the VCS folder structure is completely pristine and clean, and doesn’t include anything but active database objects; but otherwise, it can easily introduce problems. We generally don’t like to include features that can easily corrupt database environments. Since we have no control over the quality or cleanliness of the VCS, this is a concept hasn’t been implemented yet.
Hey John, please read my entry about the table changes above.
While I understand your concern about your client hurting themselves, one can do a lot of damage with any other TOAD tool, right?
Additionally, keeping source code in good order is a basic requirement for any code. Once you start treating DB differently from other code, your agile ability to deploy is gone.
In any way, thank you for taking your time to discuss that topic
I just wanted to give you an update. We’ve been continuing to discuss this idea internally and have some initial ideas on how something like this might be able to be accomplished. I don’t know when something like this might be available; but we wanted to let you know that we are discussing and researching it. We’ll keep you updated as we know more. In the meantime, thank you for your request! You guys are the reason we do what we do, and we’d like to see you get the tools you’d find beneficial. Please do feel free to keep the ideas and requests coming!