Category: UpgradingOracleDatabases
Build your database from a single command
When you're building a new application or module with Oracle in the back end, you'll be building a lot of elements that will reside in the database. You'll probably need to create a new database user, tables, views, packages, roles, grants, etc, etc. There is no reason why you shouldn't be able to do this from a single central script.
You may need a few parameters on that script, or a config file to go with it, but you should be able to point that script at an empty database, and end up with a completely working back-end for your system. The only requirements of that script should be that there is a working Oracle server instance to install into, and that you have Oracle client tools installed that will connect to that server. There, theoretically, isn't any reason why you can't make our script setup the datafiles, tablespaces and suchlike for our database, but we don't want to take all the setup work from our DBA's in one go!
By writing this script you allow yourself to be able to roll out a new database workspace with the minimum of fuss. You'll need a database workspace to go with each development workspace you use. When you need to roll back your development workspace to a previous version in order to fix a bug that exists in live, you'll need a database at the same version for that system to connect to. In fact, the database workspace and the development workspace are the same thing. Unless you have a requirement that your database must not change in any way through your development, then you are developing in the database, and so it will change with the rest of the system. Therefore, a particular version of the front end of the system will require a particular version of the back end of the system. The two things are intrinsically linked.
This fact is missed by so many people it scares me.
Rebuild your databases often
Very often in software roll-out we find that the database build / upgrade is the section of the build that causes the most headaches and unexpected failures. There is a simple reason for this. Database upgrade code is very rarely ran. Many places I've worked would release upgrades that have never once been ran from start to finish without a failure. Obviously this is a bad idea, but that didn't stop it from happening: scripts would be written that would be ran once in a developers workspace and then not ran again until a 'system test' phase where they're ran once, the scripts being massaged by the developers on had. They'd then be deemed as fit for release even though they'd not actually ran through start to finish.
I've never seen a situation where other code in the system suffers quite like this (except maybe interfaces). Even when testing has been very much an after thought, if something was so broken that a developer was needed to 'just put that semi-colon in', then the system didn't get shipped.
So, we address that by getting into the habit of running our build and upgrade scripts often. Whenever a workspace is updated with the latest version from cvs, the database build script is ran. Whenever integration starts, the database build script is ran. Before a full set of tests is ran, the database build script is ran. By running the build often we ensure it runs first time, every time, with no failures.
We may 'just add that line' and not test it if once person we runs the upgrade once or twice. We always fix the problem if 8 people need to run it many times a day!
If you're supporting a live system, make an incremental build
If you're following XP you've probably 'gone live' by now. If you haven't, why not... isn't your system useful yet? ;-)
If you're supporting a live system then you've probably got a database that you can't trash and rebuild when you want to move it to the latest release. You've got data that you need to keep. You're going to have to upgrade.
That is, as soon as you go live you're probably going to need to change the way you build the database. Up until that point it's normally easiest to make a greenfield build: a script that destroys what ever exists and builds a new database schema from scratch. This is almost certainly no longer possible.
So leave your greenfield build scripts alone, at the version at which you went live. Then add patches into the build that will upgrade that greenfield. Essentially it's like having a code freeze on a small section of the build. I know, I know, I said code freeze... it is a good type of code freeze, I promise!
We do this so that when we build our databases in the development environment we run the same code as the live upgrade will use. This is, afterall, our main way of testing the upgrade scripts. Since we can't destroy the database in live, then we need to make sure that our build doesn't rely on this being possible in the development environment. Another way of looking at it is: If it doesn't run against live then we don't want to run it in our development environment; since a changed version of the greenfield installer will never get ran against live, then there's no need to change it.
Admitted, at some later 'go live' point it may be worthwhile consolidating those patches into a new greenfield installer. This will make the upgrade code a little easier to follow. However, there is always the chance that the resulting database created by the greenfield installer will differ from that created by the original patch based installer. We can, of course, test for this.
There's a lot more that can be said about the design of an incremental build script: unit testing upgrades and associated migrations, logging patch activity. That may come in a later blog entry...
No comments:
Post a Comment