Database synchronization concept needed
I have some Oracle databases.
Some tables need to be synchronized on a per record basis.
The same application is used worldwide to alter data on the different databases and the newer records need to be synchronized back to the other databases. Some tables are master and only get updated in 1 place.
The load is for roughly 20 tables with up to 10000 records.
Doing it from the database side with DBConnections and stored procedures is not what i'm looking for.
How would you do this with a Delphi application ?
Just the concepts will do, I'll work em out ...
I have a solution now, but not scaleable enough.
Some tables need to be synchronized on a per record basis.
The same application is used worldwide to alter data on the different databases and the newer records need to be synchronized back to the other databases. Some tables are master and only get updated in 1 place.
The load is for roughly 20 tables with up to 10000 records.
Doing it from the database side with DBConnections and stored procedures is not what i'm looking for.
How would you do this with a Delphi application ?
Just the concepts will do, I'll work em out ...
I have a solution now, but not scaleable enough.
ASKER
what do you do with the zipped table files ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what do you do with the zipped table files ?
send them to destination, then un-zip, LoadFromStream to TClientDataSet and update/insert
of course you can follow mike's suggestion but as my experience with oracle management is very poor... and you wanted concept for delphi app:)
ziolko.
send them to destination, then un-zip, LoadFromStream to TClientDataSet and update/insert
of course you can follow mike's suggestion but as my experience with oracle management is very poor... and you wanted concept for delphi app:)
ziolko.
ASKER
thx for the suggestion of database replication.
as i can have a per record master, the dba suggested i just program it my self ...
what is the system ? let me get a bit more into detail.
Active Directory is the site where user and computer information is kept.
I keep a copy of this in the Oracle database A. A GUI provides a list of computers and users.
Via this GUI information can be changed like user profile, telephone, alias for computer, installed applications, etc.
Some of this info is written back to Active Directory using a threaded application.
Recently we jumped from 1 Oracle database to 5 Oracle databases and all need this GUI and writing back intel to AD.
We don't want to read/write with our applications directly to AD as we want to have a buffer/backup for AD
and we store more information than there is kept in AD.
The application that synchronizes between AD and Oracle is slow (40 min).
This is no problem as the GUI solely works on the database.
The thing is that I let the AD synchronizer do the 5 databases = 40 min x 5
Now I need to create an app which compares the 4 other databases to the 1 AD synchronized database.
Maybe this will clarify it a little more.
The GUI writes changes to a separate table when doing a change and also to the table itself.
So i actually get to know who is the master using the ChangeLog table.
I can even update distinct fields from several slaves to the master.
Now i was looking for a different approach as to use 1 synched AD database and the other synched to that first one.
Database replication can't handle this ...
as i can have a per record master, the dba suggested i just program it my self ...
what is the system ? let me get a bit more into detail.
Active Directory is the site where user and computer information is kept.
I keep a copy of this in the Oracle database A. A GUI provides a list of computers and users.
Via this GUI information can be changed like user profile, telephone, alias for computer, installed applications, etc.
Some of this info is written back to Active Directory using a threaded application.
Recently we jumped from 1 Oracle database to 5 Oracle databases and all need this GUI and writing back intel to AD.
We don't want to read/write with our applications directly to AD as we want to have a buffer/backup for AD
and we store more information than there is kept in AD.
The application that synchronizes between AD and Oracle is slow (40 min).
This is no problem as the GUI solely works on the database.
The thing is that I let the AD synchronizer do the 5 databases = 40 min x 5
Now I need to create an app which compares the 4 other databases to the 1 AD synchronized database.
Maybe this will clarify it a little more.
The GUI writes changes to a separate table when doing a change and also to the table itself.
So i actually get to know who is the master using the ChangeLog table.
I can even update distinct fields from several slaves to the master.
Now i was looking for a different approach as to use 1 synched AD database and the other synched to that first one.
Database replication can't handle this ...
ASKER
ziolko
sounds good for performance to gather all changes in a zip file, send the file and update in the other db.
sounds good for performance to gather all changes in a zip file, send the file and update in the other db.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
master-detail ?
i'm guessing you mean foreign keys ...
first the tables are put into a treeview structure using the foreign keys as relations.
this structure is then run from top to bottom for inserts/updates
the nice thing is ... run it from bottom to top for deletes.
now you know what GetPrev is used for in a treeview :)
i'm guessing you mean foreign keys ...
first the tables are put into a treeview structure using the foreign keys as relations.
this structure is then run from top to bottom for inserts/updates
the nice thing is ... run it from bottom to top for deletes.
now you know what GetPrev is used for in a treeview :)
I neglected to mention one other small item... Currently the process starts by making all schema changes propagate out. New tables are added, new columns added, removed columns deleted, indexes added / deleted, etc. when a new column is added it knows to fill in the new value for all rows... you get the idea.
ASKER
schema changes we do ourselves
as not everything that is changed is ready for production
the configuration of what is to be updated is stored in a file
i'm nearly there, it has to work by next week :)
as not everything that is changed is ready for production
the configuration of what is to be updated is stored in a file
i'm nearly there, it has to work by next week :)
I'm nearly there, it has to work by next week :)
:-O
A database replication process in < 1 week? That's scarey
:-O
A database replication process in < 1 week? That's scarey
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Loki,
thx summing it all up.
1. 1 master, 3 slaves
2. 2 slaves comin still3. None, i'll be sending the update script (compressed) to the other server
4. It's only Application Control (Oracle uses DBLinks, has some drawbacks)
5. Update-update ? master gets final say, mail is sent to controller of data
6. Transactional is almost impossible
7. Good point (Didn't think about this)
8. Scripts get sent, only executed if application is running on other end
9. Scripts are handled sequentially, solves this issue.
Awareness:
1. Synchronization is only run from the master
2. Mails (if looked at) show this
3. Same as 2
Usefullnes
1. All synchronized data has primary key 1 field = integer
2. Only changed data is sent in script
3. Simple SQL script
4. FTP file copying
Did i miss anything ?
thx summing it all up.
1. 1 master, 3 slaves
2. 2 slaves comin still3. None, i'll be sending the update script (compressed) to the other server
4. It's only Application Control (Oracle uses DBLinks, has some drawbacks)
5. Update-update ? master gets final say, mail is sent to controller of data
6. Transactional is almost impossible
7. Good point (Didn't think about this)
8. Scripts get sent, only executed if application is running on other end
9. Scripts are handled sequentially, solves this issue.
Awareness:
1. Synchronization is only run from the master
2. Mails (if looked at) show this
3. Same as 2
Usefullnes
1. All synchronized data has primary key 1 field = integer
2. Only changed data is sent in script
3. Simple SQL script
4. FTP file copying
Did i miss anything ?
ASKER
thanks for all the intel.
Because of all other nick-nacks and other updates this update hasn't been installed yet.
Everythings is working test environment.
Regards
G
Because of all other nick-nacks and other updates this update hasn't been installed yet.
Everythings is working test environment.
Regards
G
ASKER
solution was to send insert/update/delete scripts as zip to other server and run the scripts sequentially there
ziolko.