November 15, 2007 at 5:13 am
Hello all,
i have a big problem designing a distributed database system where one Database "MasterDB" must synchronize with "n" "SlaveDBs". The comunication between the Master and Slave Databases (bidirectional) is only offline posible throught XML files.
The Master and Slave Dbs are allowed to insert, update, and delete rows.
My problems are:
When I delete a record in a table in the MasterDB, how do I let the Slaves know that the record was deleted?
Till now, every single table had a bit column "IsDeleted", so I really didn't deleted the row, It was only marked as deleted.
So I could export the deleted row to the SlaveDBs and by the import function I could update the IsDeleted flag in the corresponding row.
The bad news for me are :
->I have more rows per Table.
->I cannot enforce referencial integrity since I do not "phisycally" delete the rows. !!
->I need more logic in every stored procedure/view/function in order to ignore all rows that are marked as deleted
->I need more logic in every stored procedure/view/function in order to enforce referencial integrity
....
....
An alternative is to code a delete trigger for all the tables in the database and the trigger would save in a extra table called "DELETED_ROWS" which table and rowid was deleted.
->This way I could enforce referencial integrity through foreing keys.
->This way I would not need the IsDeleted column.
->But i would have to change the existing export/import algorithm, so that when I import an XML package I would have to go through the "DELETED_ROWS" table
and proceed to physically delete this rows in the destination db.
Does anyone have a good reference how I can solve this problem? A book, link, a comment?
Thanks in advance,
Nito
November 15, 2007 at 2:50 pm
Is there a good reason to only use XML files? Replication might be a good way to solve the problem (the details will decide which type).
November 16, 2007 at 12:28 am
matt stockham (11/15/2007)
Is there a good reason to only use XML files? Replication might be a good way to solve the problem (the details will decide which type).
The main reason is that some slave databases do not have an internet connection.
I know it sound incredible nowadays, but unforunately that's the way it is. I have to make the best out of it.
That's the main reason why I cannot use any type of replication. 🙁
November 16, 2007 at 7:57 am
I think your alternative would probably work as well as anything ... could you include the records to be deleted into the XML files? Presumably the process that imports these has some method to handle conflicts. In fact, you probably have to deal with all the issues involved in merge replication ... how do you prevent an update feeding back down to the original slave DB, how do you do deal with two slaves updating the same record etc.
You could possibly still use replication under certain conditions - at the start of your sync process, backup each of the slaves, restore them onto a connected server, run the merge process, backup the connected version, and restore over the slave. Obviously the slaves would need to be offline for the duration, and it would depend on the size of the databases, and you would have to deal with objects outside of the database, but it would at least alleviate the need to rewrite chunks of the replication engine.
November 20, 2007 at 12:18 am
Hallo matt,
matt stockham (11/16/2007)
I think your alternative would probably work as well as anything ... could you include the records to be deleted into the XML files?
yes the deleted records are exported in the XML files. That exactly the reason why I mark the rows as deleted, so that I can send them in the XML files during the export function.
matt stockham (11/16/2007)
In fact, you probably have to deal with all the issues involved in merge replication ... how do you prevent an update feeding back down to the original slave DB, how do you do deal with two slaves updating the same record etc.
Yes, unfortunatelly I would need to re-code merge replication. I that's exactly what I wanted to avoid.
matt stockham (11/16/2007)
In fact, you probably have to deal with all the issues involved in merge replication ... how do you prevent an update feeding back down to the original slave DB, how do you do deal with two slaves updating the same record etc.
Thanks for the idea. I will keep it in mind.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply