January 13, 2011 at 2:05 am
Hi,
We have 2 databases db1 and db2, they may or may not be on the same server.
Now db2 has got a particular table called foo. when db2 enters a record into foo its data needs to be reflected in db1. Based on the the data from from foo db1 will make corresponding changes in its database and then insert a new record in the table foo. db2 will then read the record and make corresponding changes in its database.
This happens in Real time.
(and the cycle continues)
the only rights db1 has got over table foo is (insert, update and delete) queries on table foo.
The question is what are the possible options to implement this scenario.
We have taught of Table replication as one possible option but since this will be the first time we will be working on this kind of scenario we dont have an idea of the complications involved.
(I am aware for replication to happen we will have to create a New Table in db1 also and use the push pull technique).
We have also taught of using Stored procedures or maybe Triggers.
Are any of these valid options or there are better options available.
Also please let us know what are the things we need to keep in mind during implementation of any suggested methods.
January 16, 2011 at 6:31 pm
Not really sure what you are attempting to accomplish. Perhaps if you show the flow of the data between the databases, and the interactions that occur between databases and with in the databases, we might be able to provide you with some guidance.
January 16, 2011 at 10:40 pm
hemangmehtaa (1/13/2011)
Hi,We have 2 databases db1 and db2, they may or may not be on the same server.
Now db2 has got a particular table called foo. when db2 enters a record into foo its data needs to be reflected in db1. Based on the the data from from foo db1 will make corresponding changes in its database and then insert a new record in the table foo. db2 will then read the record and make corresponding changes in its database.
This happens in Real time.
(and the cycle continues)
I guess the corresponding changes in db2 after read record from db1 will not be flow across into db1 again (that will be a dead loop). In that case, you might better off with implementing the business logic all on db2 side to do cross database insert/update via linked server. The logica may look like this on db2:
add record into db2 --> insert a record into db1 from db2 via linked server with the corresponding logic --> db2 make corresponding changes on itself
January 17, 2011 at 10:59 am
hemangmehtaa (1/13/2011)
Hi,We have 2 databases db1 and db2, they may or may not be on the same server.
Now db2 has got a particular table called foo. when db2 enters a record into foo its data needs to be reflected in db1. Based on the the data from from foo db1 will make corresponding changes in its database and then insert a new record in the table foo. db2 will then read the record and make corresponding changes in its database.
This happens in Real time.
(and the cycle continues)
the only rights db1 has got over table foo is (insert, update and delete) queries on table foo.
The question is what are the possible options to implement this scenario.
We have taught of Table replication as one possible option but since this will be the first time we will be working on this kind of scenario we dont have an idea of the complications involved.
(I am aware for replication to happen we will have to create a New Table in db1 also and use the push pull technique).
We have also taught of using Stored procedures or maybe Triggers.
Are any of these valid options or there are better options available.
Also please let us know what are the things we need to keep in mind during implementation of any suggested methods.
Have you thought of Merge Replication. If I understand it correctly, you want
when record is entered in db1 --> it should reflect in db2 and vice verse.
HTH
Cheers
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 18, 2011 at 8:35 am
As of now we have decided to go ahead with Transaction Replication.
I wanted to know if there were problems in Replication because we cannot afford to redo entire process using some other technique at a later stage.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply