How to do blue-green deployment with PL/SQL code?

Question

Is it possible to do blue-green deployment when your software is mostly a set of PL/SQL packages?

I don't really want to duplicate the database, the financial risks in case of any desyncs are too big. I understand that step 1 is to decouple schema changes from package changes, but how to proceed further?

Is splitting packages into a separate DB that I can spawn multiple copies of the only option?

Answer 1

Yes, it is possible, but it takes a bit of work. I assume that the concern with duplicating the database is 1) time and 2) size/storage, otherwise it seems like it would be trivial to spawn databases for testing against. Based on the fact that you are targeting PL/SQL i'm guessing you are working with Oracle.

You will either need: 1) a smaller-scale dataset that can act as a representative sample of you data which is sufficient for testing all of your corner cases or 2) to clone the whole production database. Honestly, I recommend option #2. There are numerous cases where things operate correctly at a smaller scale, but run into issues once scaled up - especially with database data. Ideally, you could store and replay a sample of queries for load testing using a database proxy like ScaleArc (if Oracle won't let you store your queries).

Saying you need to clone your whole dataset may make me sound like a kook and like it will take forever, but you may find this answer in which I detail a methodology for cloning multi-terabyte databases in seconds without significantly increasing your storage costs due to duplication and delta-snapshotting useful. This may be harder if you are using Oracle's proprietary storage system. Be forewarned, the tech isn't cheap, but if you wanted cheap, you wouldn't be using Oracle. The best way to sell that to your upper management is to prove that the tech is cheaper than the outage/data mangling it would prevent. It's your insurance policy against outages - so how much insurance are you willing to buy?

Answer 2

This is very easily possible, with no change to any code (neither the PL/SQL code nor your application code, if there is some other application like Java or whatever):

1. Put all PL/SQL in one (or more) separate Oracle schema. If your application today uses only one "myapp" schema for tables and PL/SQL, then in the future you will have "myapp" (tables, indexes etc.) and "mycode" (or whatever) which only contains PL/SQL procedures.
2. In "myapp", create synonyms for all the objects that now live in "mycode".
3. In "mycode", you can create stub tables - i.e., the same tables as in "myapp", but without any data. You do not need any indexes or whatnot, just the tables. These are only used at compile time; they are especially not used by the optimizer, later.

That's it for separating your code and data. The next step obviously is to create "mycode2" as a clone of "mycode", and from then on you simply need to switch the synonyms in "myapp" between those two. You can have as many code schemas as you want, and switch between them freely with some rather trivial tooling.

(Setting the proper options, grants, making sure that the PL/SQL uses the tables of the caller instead of the compile unit left as an exercise to the reader... - feel free to ask if anything is really unclear.)

Practical hints: It is true that you have to change 5000 synonyms (well, hopefully you do not have 5000 PL/SQL packages :) ), but that is an uncritical operation insofar as there is nothing that can go wrong (both drop synonym and create synonym can not go wrong for any reason I am aware of; unlike e.g. drop table which can lock if the table is in use). You will obviously script those drops; and you will also probably query the synonyms you have to set by select object_name from all_objects where user_name='MYCODE2' and object_type in ('PACKAGE', 'PROCEDURE') or something like that to automatically catch all of them.

Answer 3

For non-data components such as DDLs or PL/SQL I would recommend using Liquibase to manage them. However, to do Blue Green deployment with sub-second downtime is likely an impossibility because you need to stop the requests to the old database and route them to an exact copy of the new database at the time with your delta being applied.

The closest I can think about doing it is as follows. For the purposes of the sequence I have the liquibase migration scripts as part of the application startup to avoid having multiple steps for application startup.

Verification

1. Copy Green DB and other data artifacts such as file systems etc. to Blue. This is a time and size factor.
2. Deploy and start Green application blue.
3. Deploy and start the new application on Blue which applies liquibase scripts.
4. Verify

Switch over

1. Pause the Green application (this prevents any future access to the data). This begins the downtime.
2. Copy Green DB and other data artifacts such as file systems etc. to Blue (again). This is a time and size factor.
3. Deploy and start Green application blue.
4. Deploy and start the new application on Blue which applies liquibase scripts.
5. Switch route to Blue

Switch over and Fallback (note new data loss)

(from above)

6. Restart green application
7. Switch route back to Green

Reducing cost

• Instead of a new database or ElasticSearch or Kafka instance, just use a different schema and credentials between blue and green.
• Instead of a new storage, use a different root folder between blue and green.