| Author |
Topic |
|
BendJoe
Posting Yak Master
128 Posts |
 Posted - 2007-11-19 : 09:09:26
|
| I am new to Databases and have a situation where I have to synchronize tables in two different databases. One database is the back-end of an intra net application and the other is part of a web application. What is the best approach here. |
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-19 : 09:36:08
|
| Will both of the tables be updated with data. Will the tables on database2 need to have the same data as database1? Will they start with the same data |
 |
|
|
BendJoe
Posting Yak Master
128 Posts |
Posted - 2007-11-19 : 09:48:49
|
| The data will be updated from both sides meaning the intra net and public web app. Well I will explain the situation a bit more. We have a big intra net application running . Now I am adding a feature which will allow customers to make service requests through the web. So I am creating a new database for my web application. In that database there will be a few tables which needs to be synchronized with some tables in the intra net app. Change will be made to these tables from either sides,those changes needs to be reflected instantly. They might not start with the same data( 99% sure). There will be a column which identifies the origin point of the record. Like a record which was created on the intra net side might have a column called IN001 and on the public website will have WEB001. I don't know how to deal with these columns and the Identity columns of the two tables.Please ask me if some thing is unclear.Thanks |
|
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-19 : 10:19:54
|
| This is not possible! The closist thing to this is merge replication. If you had a table (or more) that you needed to update in two tables this could be done providing the data started the same. You could then move data between the two with no problems.Book On Line has a better explnation of replication. But it sounds like you are looking for merage replication. |
|
|
|
BendJoe
Posting Yak Master
128 Posts |
Posted - 2007-11-19 : 10:51:51
|
| Peer-to-Peer transactional replication seems to be a good option for me. But one thing I am worried about is will there be any Internal Id conflict during replication . Or how much latency is there while data is being synchronized. Will there be a time gap which allows each tables to create a record with the same internal IDs when Inserting. |
|
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-19 : 10:56:24
|
| This all depeneds on what kind of state your data is in. Have you got Primary keys?Dave Dunckley says there is a law for the rich and a law for the poor and a law for Dirty Davey. |
|
|
|
BendJoe
Posting Yak Master
128 Posts |
Posted - 2007-11-19 : 11:03:42
|
| Yes there is a Primary key column. That is why I want to know about the chances of encountering a conflict. |
|
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-19 : 11:23:39
|
| You will not encounter any conflicts as SQl will use the P.keys to update the records. But like I said before both tables HAVE to start 100% the same. If not you cant use replication. Again IO would also think that from the sounds of it you are looking for merhe replication. This has a conflict resalution built in.Dave Dunckley says there is a law for the rich and a law for the poor and a law for Dirty Davey. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-19 : 12:13:29
|
| If both database servers are in 2005 you can use Peer-Peer replication. You can do updates/inserts/deletes on either side and the transactions are replicated to the other node.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
BendJoe
Posting Yak Master
128 Posts |
Posted - 2007-11-19 : 15:29:04
|
| I wanted to test bi-directional transactional replication. In order to do some testing is it possible to do this on two tables in two databases residing on the same SQL Server Instance. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-19 : 23:43:54
|
| Yes you can. But why don't use peer-to-peer replication? Bi-directional transactional replication needs both dbs online for real time replication, otherwise process will fail. |
|
|
|
|
Two way replication