Anyone who has spent a bit of time managing enterprise Oracle systems would have come up against the dreaded “dblink”. This construct is one of those things that are so great for developers but absolutely pernicious to us Operations folks.

There are a number of good technical discussions on dblink out there, so let me focus on the big picture, architectural view. There are some reasons why dblinks are highly popular:

• It is enterprise integration made simple. Want to connect a billing system to a CRM database? Simple – just establish a dblink between the billing database and the CRM. Voilà – the moment a customer billing event occurs, the data is available in the CRM system. So even if the customer calls a minute after, say paying a bill, the customer service chaps can have the data accessible.
• Apart from the integration benefits, dblinks support that ethereal concept called “Transaction Integrity”. This is implemented through the strangely named two-phase commit (2PC) protocol (strangely named, because there are actually three phases, but more on that in a separate post). Basically, this ensures that a transaction respects the “all or nothing” approach that gives the databases the halo that surrounds them. So, how does this work in real life, what challenges does it solve: Like any database primer will tell you, if the act of a customer paying a bill has to update the billing system, as well as the enterprise’s accounting system, it would not do at all to have only the billing system updated and not the accounting system. No, that would be quite disastrous. So that is where transaction integrity comes in – either both systems will be updated, or neither will be updated. Dblinks respect that, or more correctly a transaction boundary can cross the database over the dblink.
• Dblinks are easy to set up – a simple SQL command of the type CREATE DATABASE LINK (with a few parameters, of course) is sufficient to leverage all the powerful benefits, of instant integration, transactional integrity, etc.
• Security (which is always a concern when connecting systems together) can also be handled in a somewhat satisfactory manner. The connection from one database to the other can be made either as a hadcoded user, setup at creation time, or the connected user (at runtime).
• Moreover, the configuration and all its options can be completely handled at the server end. There is no need for distributing configurations (or credentials) to client terminals. In fact, there is nothing at the client end to suggest that more than one database is being used at all. The only requirement is that the remote database (in Oracle’s case) is visible to the server i.e. has an entry in the server’s tnsnames.ora.

Now, with that plethora of benefits behind it, what on God’s green earth would make one NOT want dblinks around? In one word: dependencies. We folks in IT (particularly the sorry subset of us that has to actually manage these systems in production) hate dependencies. Dependencies are what cause systems to fail, more often than not. They also “tightly couple” systems to each other – believe it or not, that’s BAD.

So here’s why – consider the same example above when a billing event updates the billing database as well as the accounting database. Also consider that the brilliant, well meaning people who develop these systems wanted a fool- proof way of ensuring that these systems were tied together in the “all or nothing” fashion described above. Also, being well-meaning they wanted to ensure that any billing event updates the accounting database immediately, thus providing a “real-time view” of the enterprise, and so on and so forth. Now guess what we come up with – yes, a dblink!

Now picture this – the billing system is what is called a 24×7 system, that is it runs 24 hours a day, 7 days a week. You want customers to pay up all the time, don’t you? So this billing system is probably wired to your web site, your IVR, etc. And needless to say, since this is where your money’s coming from, you want this to be available all the time. The load characteristics of this system are purely transactional, with a large number of small transactions – the equivalent of a large number of users paying their bills, querying their statements, etc. No heavy processing.

Now, stop for a moment and think about your accounting system. It is probably used by a few users in the accounting department and senior management. It also has a number of small transactions, but it has a couple of characteristcs that are very different from the billing system:

• It has large batch runs, particularly when financial statements are generated.
• It may not be highly transaction oriented
• It will not be exposed to the external world
• It certainly will not qualify as a 24×7 system

Now, imagine the drawbacks of the tight coupling of the billing and accounting systems. First, to maintain integrity, the accounting system also has to be 24×7, otherwise your billing system can’t be allowed to run. Second, the online performance of the accounting system will have a large bearing on the performance & stability of the billing system. You can’t take the accounting system down for maintenance, even though you are outside the operational window.

In effect, the tight coupling has forced your accounting system to be as critical and available as your billing system. Now you need to invest in 24×7 operations, no maintenenance windows, and all the other hassles that go with such a system. Besides, if your accounting system is busy processing some batch jobs, please be assured that your billing system is going to slow down drastically. In extreme cases, your billing system will crash (typically with an ORA-7445).

What is a dream for the developers is a nightmare from the operations perspective.

The solution: loose coupling, where each system can run independantly of the other. In other words, even if the accounting system is down or performing poorly, the billing system is not affected. Then what about integration, one may ask. That is another topic altogether and will usually involve some combination of a reliable message queue, a store-and-forward mechanism, and application-to-application integration.

Not database-to-database.