aspose file tools*
The moose likes JDBC and the fly likes Database Synchronization Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database Synchronization" Watch "Database Synchronization" New topic
Author

Database Synchronization

manisha Gupta Garg
Ranch Hand

Joined: Jul 03, 2009
Posts: 41
Hi All,

In our application, we have total four databases - dev/test/stage/production.
our requirement is, if prod database is updated by users, then automically our other test databases get updated.

So is there any tool/way which can synchronize one database if other is modified?

Please help.

Thanks,
Manisha
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Databases usually provide tools out the box to do this (or at lease the big databases do). Which database are you using?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

If you want to replace one database with another, or replace a strict setup of tables, there are tools for that. But I suspect if you have 4 databases you might need something more fine grain such as "replacing records in table Y after date X", which case I recommend writing your own synchronization tool.

By the way, what you're describing worries me. I'm familiar with 4-database teirs (dev/test/stag/prod) but usually we want the data kept completely separate. In fact, in many systems I've worked on the majority of developers do not have access to production-level data. In some cases (like medical systems for example) in violates laws for developers to be able to access it. I'd reconsider any solution that pushes production data immediately down to test systems and argue it should never be done. The reasoning being "If you took the time to setup 4 separate database tiers, then you probably dont want to muddle them". Else, mine as well just have 1-2 databases and not bother with the setup.


My Blog: Down Home Country Coding with Scott Selikoff
manisha Gupta Garg
Ranch Hand

Joined: Jul 03, 2009
Posts: 41
Thanks Paul and Scott..for the responses !

well we use Oracle database. The purpose of synchronizing all databases is - after 4 years of our application, some data available in test databases are not in use and new updates are happening in prod database. And we are the admins of our application and yes we have full access for production. Main reason for availing this facility to us is just to reduce our efforts in synchronising the databases. Beacuse it is us, who update the prod database along with other normal users too.

Please suggest me any solution to synchronize other test databases according to prod data and NOT VICE VERSA.
One solution is creating datalink and copying prod data in stage database. But that is very time consuming process and do copying one by one table not all at one time

Thanks,
Manisha
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Hi Manisha,

It seems to me that it is an issue that concerns dbas' attention. They mostly have common issues like this and may have a better solution than a developer can come up.

Regards,

Fatih.
Vinod Tiwari
Ranch Hand

Joined: Feb 06, 2008
Posts: 466
    
    1

You can use Oracle Links and created jobs that calls some procedure which fetches data from other database and insert/update the database.

However I would recommend that you write a utility that exports production database objects and imports them into other databases. This activity can be automated via job to scheduled daily / weekly.


Vinod Tiwari | Twitter | Shikshanirman | Guftgu
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Please suggest me any solution to synchronize other test databases according to prod data and NOT VICE VERSA.

That sounds particularily messy. What do you do if a tester working against the test database enters data that will prevent the import from production working? Sounds like a bit of a mine field - I reckon this might dramatically increase the effort synchronizing databses rather than reduce it.

Would it not be easier to restore these databses from production nightly?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30910
    
158

A datapump export/import of the full database is very fast. I would do that when needed and then load any specific test data you need present afterwards.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
manisha Gupta Garg
Ranch Hand

Joined: Jul 03, 2009
Posts: 41
hi Jeanne,


could you please let me know more about 'datapump' ?

Thanks,
Manisha
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Data pump is an Oracle tool to export/import data from/to another database. See the Oracle documentation for a full explanation.
manisha Gupta Garg
Ranch Hand

Joined: Jul 03, 2009
Posts: 41
ok thanks Paul
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Database Synchronization