Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database Synchronization

 
manisha Gupta Garg
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Databases usually provide tools out the box to do this (or at lease the big databases do). Which database are you using?
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
manisha Gupta Garg
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 466
1
IntelliJ IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
manisha Gupta Garg
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi Jeanne,


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

Thanks,
Manisha
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ok thanks Paul
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic