Re: Scalability of Replication?

Jeff McWilliams wrote:
> How scalable are the replication features on Oracle 9.2? Is anyone aware of
> any good studies on this?

Any ideas about how you want to replicate?
>
> I've been asked by a small mortgage/loan company to help them design a
> solution that will support their growth plans for their customer/loan
> database system.
>
> They currently have 8 offices. Their IT person thinks they want to
> run a database server at each office with replication back to a

Ah - you have a problem. They already have a solution, they just did not tell you. Move with caution; hidden agenda ahead.

> centralized server. This would be updateable materialized views or
> multi-master replication.

See - it's even been determined it should be master/master. Why not master/snapshot? E.g. should every office have the full customer table, or just a localized set? Goes for a lot of tables, I would say.

>
> Here's where it gets really difficult:
>
> Their growth plan calls for 100 offices in 5 years, and 850 offices in
> 10 years. Ignore for the moment all the other issues maintaining 850 servers.
>

Right - dream on. As if economy is that great.

> I have my doubts that replication can even scale to this size, but I've
> never dealt with anything on this scale before, and I'm tempted to walk
> away from project.

Hm, it probably would (but think about the central server, and the network infrastructure.)
>
> If it were me, I'd be tempted to push for a web based application that's
> centrally located and sits on a load-balancing cluster of web/app servers.
Of course, that's the way to go. You can even get in between, and replicate between two (East coast/West coast springs to mind :) ) or more central servers.

> However, I don't have all the details about their app yet to determine
> if that's realistic - plus that design would have the downside of
> cutting off the branch offices if they had a network outage.

Networks can be rerouted - what if their server would go down? What is another server would have delayed replication and your data is screwed? Imagine how you are going to check between 850 (or even 10!) servers which one holds correct data (with the possibility, server 1 is correct about the first 2 records, #2 about the ones over coffeebreak, etc, etc).
Just trying to sketch the nightmare when/if replication breaks.
>
> Can anyone point to any case studies for "distributed" databases on
> such a scale?

If they are serious about 850 outlets in 10 years, I would not hesitate to contact Oracle for examples and best practices (at the risk Oracle will take over the project).
>
> Thanks,
>
> Jeff
>

Comments inline

-- 
Regards,
Frank van Bortel