2

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?

2

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 reply 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. 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?

  • 1
    We have a smaller-scale dataset, but how does it relate to blue-green deployments? I am talking about switching users from app version N to app version N+1 without apparent downtime by maintaining two production applications. – Alexey Sep 19 '17 at 13:27
  • @Alexey You might want to clarify your question then. – James Shewey Sep 19 '17 at 15:16
1

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.

  • So, what's the downvote for? – AnoE Sep 18 '17 at 20:06
  • So when the "frontend" connects to the myapp schema, it calls "mypackage", but calls it with invoker rights so it uses the tables in "myapp"? Interesting. What I don't like is that there's now 5000 synonyms to be changed per deployment instead of a single switch. – Alexey Sep 19 '17 at 13:23
  • Yes, @Alexey. 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... to automatically catch all of them. It may not be beautiful, but it is blue-green. – AnoE Sep 19 '17 at 20:41
  • Actually, there are almost 12000 packages in the system. – Alexey Sep 20 '17 at 9:11
  • @Alexey: cool. Well, you know your system best. You'll have to test if such an approach works for you, performance wise, anyways. – AnoE Sep 20 '17 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.