The Old Joel on Software Forum: Part 3 (of 5)

Remote database replication

Does anyone have any experience with setting up either SQL Server or Oracle where there were going to be two duplicate servers thousands of miles away that were supposed to have the same data on them?

The application that I have in mind does not require instant duplication - it's not like a stock market or airline reservation system. Users will be, mostly, adding new records and remotly added records will need to be visible in hours, not seconds.

pdq
Friday, September 26, 2003

If
1. you have the programming resources
2. and the datasets are tiny, like the IPaq on thousands of your sales force
3. and the replication is master to many secondary for lookup and many secondary to master for updates...
You might just simply try to find a file transfer mechanism you can live with.

For example your sales force needs to update customer info at thousands of sites, they are mobile.

Have then plug in the ipaq into their hotel laptop and run synchronization over night.

At 1am:
Upload the changes..

Give 4 hours:
To allow for batch updates

At 5am:
download an rsync (talked about earlier yesterday on JOS threads) suitable for your database file (say Access MDB).

You could just write a vb program that do uploads and downloads using HTTPS.

But if you give us more details (and your details indicate fewer and larger secondary dbs) that could change the story completely..

Li-fan Chen
Friday, September 26, 2003

It's one SQL database that needs to be replicated between offices. Let's assume the offices have T1 lines for the moment and the database is on the order of 500 megs. The amount of data that is changing is on the order of 10s of megs per day and is mostly additional records.

I am interested in having the database engine do the work. I've heard this is possible? Has anyone done it? We could always write our one database, but I would prefer to buy not build.

pdq
Friday, September 26, 2003

> I've heard this is possible?

You are probably talking about replaying log files... not for recovery, but for replication. Most databases that preserves every inserts and updates can create such a file. You just transfer it to the destination db, replay the changes. This assumes the secondary db is read only. This update can be relatively life, but you'll still have to do some programming I think, depends on cost.

Li-fan Chen
Friday, September 26, 2003

I can't speak for Oracle, but SQL Server supports 2 methods of accomplishing your task:

Log Shipping
Transactional Replication

Do a quick google for SQL Server with the above terms and you'll find plenty of discussion on the pros and cons of each. I've used both, but in support of multiple servers on the same LAN, not across a 1000 mile WAN. Both work well in a LAN environment.

Sgt. Sausage
Friday, September 26, 2003

I can not speak for Oracle but implementing merge replication with MSSQL is realy easy. I've implemented merge replication between one server in Mexico and two in Europe (Germany and Slovenia). It works great.

Branko
Monday, September 29, 2003

Thanks, Transactional Replication looks like what I need.

pdq
Monday, September 29, 2003