Re: Distributed Database Issues

In article <40fi55$ed3_at_news.dax.net>, hts_at_sasdata.no (Haakon T. Soenderland) writes:
|> Hi all,
|>
|> I'm sitting here looking at the white-paper on Distributed Database
|> Technology and Symmetric Replication from the Oracle WWW-site
|> (http://www.oracle.com/info/products/entserver72/ddtsr.html).
|>
|> I'm wondering about some of the statements Oracle makes in this paper
|> and would be interested in your comments on them.
|>
|> In the section on "Synchronous Distributed Technology" they say:
|>
|> "Synchronous technology ensures application integrity and minimizes
|> complexity, but can have poorer response time performance and less
|> availability if the systems and networks involved are unreliable
|> and slow."
|>
|> and in the section on "Asynchronous Distributed Technology" they say:
|>
|> "Asynchronous technology maximizes availability and response time
|> performance, but can be more complex and requires careful planning
|> and design to enure application integrity."
|>
|> I'm reading (or interpreting) this (and the rest of the document)
|> to mean that Oracle thinks that using PL/SQL, triggers and procedures
|> is easier to set up and manage than snapshots.
|>
|> I would think that setting up a synchronous distribution environment
|> would be just as complex as setting up an asynchronous one.
|>
|> More important, I would think that maintenance and expansion of such
|> an environment would be easier using asynchronous replication (snapshots)
|> than using synchronous distribution (i.e PL/SQL, triggers and procedures).
|> One should think that adding one more site with synchronous
|> distribution would involve writing or re-writing lots of
|> PL/SQL, while adding another snapshot site would be easier (since its
|> deklarative).
|>
 

        Yes, lots of PL/SQL changes because to call a procedure at a remote database you have to hard_code the database name as well as the procedure name. So if you have identical requirements at several different sites, you need to add code every time a new such site is added. But there is a way around this problem with dynamic sql.

        I had to take over a system which used calls to identical procedures installed on remote databases. The first time I had to add a new location, I had to replicate loads of if-its-this-database-then-call-this-procedure lines of code, even though the procedures themselves were clones of a single procedure. Besides the coding problem, moreover, the dependencies are horrendous. A change at a remote site can cause a timestamp mismatch which renders your calling procedure invalid until you recompile it, which you can't do while it's in use.

        So here's what I did. I set up a dummy table at each site for each procedure that needed to be called from the central site, with the columns in each table corresponding to arguments to the procedure. Then I wrote insert triggers for each of these tables, which call the procedures and pass them the arguments.

        Next I went back to the if-then-else statements and replaced each such set to one call to a procedure (adding target database as an argument) which uses dynamic sql to insert into and delete from each remote dummy table. Now when a new database is added, we run an install script at the remote database and add an entry to ourtable of databases. No code changes at all.

|> Having little experience with this technology, I might be dead wrong.
|> However, it seems to me that if one is using replication, it would
|> be easier to set up and manage snapshots then writing the replication
|> code yourself. I'm most concerned about management and maintenance
|> of such an environment. Setting it up migth be difficult, but it is
|> an onetime job, whilst one could imagine such a system expanding and
|> beeing in operation for years. Thats when it needs to be managable and
|> maintainable.
|>
|> What are your thoughts?

        Snapshots just didn't suit our purposes because we are doing more than replication, so I can't comment on how manageable they are. Never tried 'em.

|>
|> I'm also wondering if anyone can tell me about the underlying technology
|> for the "Multiple Master" replication they are talking about. They are
|> saying that it uses "Deferred RPC" as the undelying technology, but does
|> that mean that you'll have to write these RPC's yourself? Or is there
|> a layer "over" this that you use (as with snapshots)?
|>

        Interesting. Deferred Remote Procedure Calls sound like what you get if you use the technique I described above, but use delete triggers instead of insert triggers to call the procedures, and remove the delete from the original transaction. I've done that, too, but just for testing purposes so that I could isolate the remote procedure calls from the rest of the transaction.

        Actually, a lot of this article seems to say that they are providing support for things we've already had to figure out how to do for ourselves. Darn! Now everybody else will be able to do it too :-).

        But then again the article looks much more like a sales pitch than a technical description, so I'm not sure how these features really work.

|>
|> Thanks,
|> Haakon

And thanks for pointing out this article, Don

-- 
-- 
============================================================
Donald A. Smith         No, that was JOHN Smith
eddas_at_huber.com         I have never met Pocahontas