Developing an environment

Previously I've talked about how important I think that development databases are, and that I think that they should be proper sandboxes, protect from the outside world and each other. I admit, I've kind of glossed over how that might be done, and so I wanted to correct that...

In our current project we're building an application that will eventually replace an existing one. Until it does so we need to ensure that our new application can live together with the legacy one, and will share the same data. The two systems must exist symbiotically.

The existing application, let's call it Legacy is a classic Forms or Powerbuilder and Oracle structure. All it's objects exist in the Oracle user LEGACY, and are wrapped in public synonyms.
Each users of the system is given an Oracle user that then accesses the LEGACY objects through the public synonyms. Let's say have the users USER1, USER2 and USER3.
So we have a structure that looks like this:


The new application is to live within the same database. We'll call that application NewApp, and install it into the new Oracle user NEWAPP. As an Intranet delivered system (over HTTP) we decided that all user connections to the Oracle database would occur through the user NEWAPP.

We decided that in order to insulate ourselves from Legacy, we would ensure that all access to the LEGACY objects would be done through a new set of private synonyms. As an aside, this allowed us to develop a kind of 'name space'. I.E. The private synonyms for the LEGACY objects are all prefixed with LE_. That way we can tell which are LEGACY objects and which are NEWAPP and reduce the chances of clashing object names.

Anyway, that then gives us the following structure in our live environment:


We then state that we do not add new functionality into the LEGACY user. I.E. All development for NewApp is done in the NEWAPP user. We are not the owners of LEGACY and therefore do not have the ability to change LEGACY at will. We are the owners of NEWAPP and therefore the contents of NEWAPP are entirely under our control.

So how do we deal with this structure in our workspaces?

One option is to build a full database per developer / pair. Starting with an empty database we could either import a copy of LEGACY or build one from scripts. Then by running our NewApp build script we can build the NEWAPP user as it would exist in the live environment.

This may be appropriate for a small team of highly Oracle skilled developers, but I'm not too sure.
With the large number of databases you then have an overhead in terms of maintaining those databases. You either need a development DBA on hand to troubleshoot problems on each database, or you need each developer to be capable of dealing with those problems such as the listener not starting up, upgrading the DB to 9.2.0.7 to match the live environment, etc. In addition, if each database is on a client machine, you run the risk of each database being configured in a slightly different way... being fiddled with by developers that don't necessarily understand the impact of settings being different in development as they are in live.

In short, I'm not too sure. I know it CAN work, and that it's far superior to not having individual workspaces... but we followed a different route.

We decided we wanted a single development database, in which many workspaces could exist. However, in order to have individual workspaces, we don't just need individual NEWAPP users, we also need individual LEGACY users. OK, so we may not be changing the structure or functionality of the LEGACY user, but much of our data exists in that user. If we're to be insulated from the changes made in other workspaces, then we need to be insulated from data changes as well as structural ones.

So how do we implement the individual workspaces in a single database?

The answer is actually surprisingly simple; we have a dual mode build script, to which we can state that we're building a development environment.

If we're building into live / demo / any other non development environment then we build our private synonyms and issue our grants to LEGACY objects, then build / upgrade the NEWAPP user.

If we're building into a development environment then we start off by creating our development version of the LEGACY user (lets call it DEVLEG1), then create our private synonyms and grants to DEVLEG1 instead of LEGACY.

So if we were to build the development environments DEVNEW1/DEVLEG1, DEVNEW2/DEVLEG2 and DEVNEW3/DEVLEG3 then we'd have the following structures:


If we then found that we needed to be able run the front end for Legacy in our development environments then we could add the creation of relevant users along with private synonyms to the appropriate DEVLEGx user, giving us the following structures:


The result is that we have fully independant development workspaces whilst allowing the database to be centrally managed. We can use our DBA team to keep the databases up and running and control the set-up whilst allowing us to develop the code freely without treading on each other's toes.
The eagle eyed of you may notice the fact that our development databases may be a little deviod of data, not a great scenario. However, we have noticed that ourselves and put a solution in place. More on that later...

Technorati Tags: Oracle, software, development, database, upgrade, blog, agile, Robert+Baillie, patch, db, dbms, workspaces, sandboxes

We need space to develop

I've talked previously about how important I think it is that individual developers (or pairs) get to work in their own sandbox (not least here). This means that developers can experiment; can try avenues that would otherwise break the workspace for other developers; can refactor the database itself.

Obviously, in order to do this, you need to actually have development databases for those developers to own. This tends to lead to the DBA team announcing that you'd need far too many databases, that you simply can't be serious, that we've no idea how overworked they already are and that we can't expect them to take on another x databases, where x equals the number of developers. Oh, and by the way... who's going to build them? I can fit the first 3 in some time the middle of the year after next, god knows when the other 7 will get built.

I can understand the reaction. I'd be exactly the same if I thought that someone was suggesting that I doubled my workload. Luckily for DBAs, that's most definitely not what I'm suggesting.

The misunderstanding comes from the thought that a development database is the same species of beast as the live database. It isn't, not by a long way.

The major difference between a live and development database is how their role defines the use of data. In a live database the single most important thing in that database is the data it holds. It is the sole purpose for the existence of that database. Fine, we reshape that database so that we can access the database in new and interesting ways, but all said and done, if that data wasn't there then the database would be useless. In the development environment this is not the case.

The most important thing in a development database is its structure. Its purpose is to work as a validation tool for the live database, to ensure that the application we're producing works against the live database and to allow us to develop the structure of that database in order to allow to us to provide our application. It's important that we hold data that has a texture similar to that which exists in live, but it doesn't matter which data we hold. The data itself has no intrinsic value. We can generate our own.
In fact, when we run unit tests on our PL/SQL code, this is exactly what we do; we generate our test data so we know exactly what form it is in. That way we can make clear statements about the form we expect it to be in when our PL/SQL has completed.

Also, we don't need to hold the same volume of data. In live, every single piece of data is of importance and so you can't just remove it*. In development we can limit ourselves to create only the data that is of importance to us at this point in time. If our development databases contain a very small amount of data, and the means to generate the data it needs, then you remove a large amount of the DBA's work.

That is, most the work a DBA will do in maintaining a live database is purely down to the fact that it is a system that contains a large volume of critical data. It must always be available, it must serve data within certain performance requirements, and if the machine it's running on suddenly blows up then we must be able to recover the data. Quickly.
Take away everything other than the 'it must always be available', and you've taken away most of the DBA's work.

When we started development, we didn't tell our DBA that we needed 'x' new development databases. We told our DBA that we needed 1. We said we didn't need it backed up, just that we needed it to be up during working hours. We said, make it about the same size as live and it'll always be big enough.
We then ran our build scripts multiple times on that database, creating a schema for each of our pairs of developers. This is the same build script we would use to upgrade live, it just has a development mode that allows us to say which schema name we want it to install into.
The DBA has needed to do next to nothing with the database since it was created.

Now I admit, we have only around 10 development databases at the moment. Maybe the problem gets a lot harder to manage when you have 100 development databases. But then again, if you have 100 developers then I'm damn sure you've got more than 1 DBA. You've got a DBA team. And if you've got a team then surely someone in that team's got the time to build you enough databases to support your development team. If not, then you've probably got more serious problems to deal with...

Of course, none of this takes into account the fact that your customer needs to test the application, that you'll need to performance tune and test along side all the other systems running before you release and you need a test environment for duplicating issues once the system goes live. But NONE of these things should be happening in your development environment... a topic for another post. Later.

* Actually, you often find that most the data in a live database is useless and only a small proportion is ever used. But try telling that to your customer and getting them to let you archive it...

Technorati Tags: Oracle, software, development, database, upgrade, blog, agile, Robert+Baillie, patch,db, dbms, workspaces, sandboxes

Database Upgrade Links

Around August last year I started to put together some notes on writing database upgrade scripts. It was kicked off after a discussion with Wilfred van der Deijl, based on experiences on my current project where I honestly we've got it about as right as it ever needs to be.

For a while now I've been fiddling with FeedDigest to try to get it to categorise my entries so that I can produce a summary page, and I've finally given up. So instead, I've put this entry together. It's just a link page to each of my database upgrade entries... I hope it's of use!

• Introduction


• Rollbacks


• Why all the effort?


• Dealing with code


• Table Centric Views


• Design By Contract

Google Pack

It's been a while since I've been 'in the blogging scene', and I suppose the only way to get back is to start posting again! So, whilst this post may be small, I'm hoping it's beautiful.

Google have decided to address that age old problem... getting your new PC from the point it's at when you first buy it to actually being useful and on the way to being safe to use on the web.

They've put together the 'Google Pack'.

OK, so I like Google Earth, but I'm not sure it's quite essential, but the rest:

Google Desktop
Picasa
Google Toolbar for IE
Norton Antivirus
Ad-Aware
Adobe Reader

And THE most important one:
Mozilla Firefox

It's a stunning idea, though I'd probably add to that:
Open Office
AVG (Ok, so you've already got Norton, but AVG is ACTUALLY free)
Skype
Hitman Pro (Ad-aware is only one part of the anti spyware suite)