| Author |
Topic |
|
kwilliams
194 Posts |
 Posted - 2003-08-12 : 10:26:50
|
| Hi there,I currently have a system with one internal server (DB1), and one external server (DB2). On DB1, employees use ASP front-ends on our intranet to insert/update/delete data from 4 different database tables. Then the data get's pushed to DB2 using transactional replications with push subscriptions. This works great, but this setup can't work on the 4th DB table. That's because it's a website form on the external DB2. I want the opposite to happen...I want the data from DB2 to insert into DB1. I've tried this using merge replication, but It's always had problems. I've heard of a way to accomplish something called 2-way transactional replication that acts like a merge replication. Can anyone tell me how to do 2-way transactional replication? Thanks.KWilliams |
|
|
kwilliams
194 Posts |
Posted - 2003-08-20 : 11:59:10
|
| I've gotten no responses about two-way transactional replication since I originally posted this message last Tuesday, Aug 18. Does anyone have any ideas on how I can set up two-way trans. replication, or a place which has information on this setup? I've already looked through the SQL 2000 Books-Online, and it did mention that it was possible. But it didn't really give any details. All help & suggestions are greatly appreciated. Thanks everyone!KWilliams |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-20 : 13:07:39
|
| Replication isn't discussed much here. When people post questions about replication here, a lot of the questions go unanswered. You might want to try one of the SQL Server newsgroups instead if you don't get an answer soon here.Tara |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-08-20 : 13:32:33
|
| There is a complete example in SQL Server 2000 Books Online. Look at the topic with the title: "Implementing Nonpartitioned, Bidirectional, Transactional Replication"--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
kwilliams
194 Posts |
Posted - 2003-08-20 : 14:24:28
|
| Vyas,I looked up "Implementing Nonpartitioned, Bidirectional, Transactional Replication" in my SQL 2k Books Online as you suggested, and it came up with no results. If I searched for just "Implementing Bidirectional Transactional Replication", it pulled up info on Merge Replication. But I still can't find any details about two-way transactional replication, only tid-bits. Any other suggestions?? Thanks.KWilliams |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-20 : 14:29:54
|
| I can't find that topic in BOL either.Tara |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
|
|
kwilliams
194 Posts |
Posted - 2003-08-21 : 09:37:55
|
| Tara & Vyas,I'm close to a solution, but I'm getting confused on the actual setup of the push vs. pull scubscription. After reading the BOL (thanks Vyas), I realize that I need to set up an "immediate updating or queued updating subscriber", and I need the have it set up this way for a subscriber to be allowed to make changes also:External Server-SubscriberInternal Server-Distribution-Publisher...but what I'm confused about is whether I set up a push or pull subscription, and on which server do I do it on. The internal server is not allowing me to create a pull subscription on it. The external server will allow a pull subscription, but it doesn't actually update the DB table, even though the job says that it was successful.With SQL 7, I had the Publisher on the internal server, and a pull subscription on the external server with merge replication. But with transactional replication, this setup makes the publisher update the subscriber (which is the opposite of how it works with merge replication).So basically, should I use a push or pull subscription with 2-way transactional replication, and on which server should I place it? Thanks for all of your help.KWilliams |
|
|
|
kwilliams
194 Posts |
Posted - 2003-08-21 : 10:09:40
|
| Hi Vyas & Tara,I did a test, and realized that I had it backwards. So the external server should be the Publisher, and the internal server should contain a Pull Subscription and the Distributor. But the Snapshot agent is giving this weird error message:This server has been disconnected. You must reconnect to perform this operation.All of the transactional replication that's set up on the internal server is working fine, but as soon as I put a Publisher on the external server, this error message happened. But it only happened with the Snapshot agent, not the Distribution agent, or the LogReader. Any ideas or suggestions?KWilliams |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-21 : 12:22:32
|
quote:
Originally posted by VyasKN
I have no idea, why you and Tara couldn't find that topic in BOL, but I am using the latest SP3 BOL.
Hmmm, I'm using SP3a BOL, which I'm sure is what you mean by latest SP3.Tara |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-08-21 : 12:39:59
|
| kwilliams, >> After reading the BOL (thanks Vyas), >> I realize that I need to set up an "immediate updating >> or queued updating subscriber"No. That is not correct. For non-partitioned, biderectional, transactional replication you don't need an immediate or queued updating subscriber.In BOL if you read the page titled "Nonpartitioned, Bidirectional, Transactional Replication", it says:"Merge replication and transactional replication with immediate or queued updating are key technologies supporting multisite update replication. If you require transactional consistency, and immediate or queued updating is not appropriate, you may want to extend transactional replication programmatically to support nonpartitioned, bidirectional topologies. Microsoft® SQL Server™ replication provides features that allow nonpartitioned, bidirectional replication, but substantial customization and programming is usually required."If you follow the BOL instructions, you'll realize that you need to create normal transactoinal publications, but with custom stored procs option and cycle detection. That is both the servers will act as 'publisher and subscriber'I suggest you try out the sample provided in BOL.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
kwilliams
194 Posts |
Posted - 2003-08-21 : 12:47:21
|
| I snow see what you were talking about, but I couldn't find any instructions on how to extend transactional replication programmatically to support nonpartitioned, bidirectional topologies in SQL 2k. It just says that you should do that?Then it states "Microsoft® SQL Server™ replication provides features that allow nonpartitioned, bidirectional replication, but substantial customization and programming is usually required."This makes it sound like A LOT of work to set up. It's almost better to set up merge replication, and just spend the time dealing with the problems.P.S. I have SP3 too, but that subject didn't come up when searched.KWilliams |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-08-21 : 12:49:19
|
quote:
Originally posted by tduggan
quote:
Originally posted by VyasKN
I have no idea, why you and Tara couldn't find that topic in BOL, but I am using the latest SP3 BOL.
Hmmm, I'm using SP3a BOL, which I'm sure is what you mean by latest SP3.Tara
Tara, I am pasting the page title from my BOL:Implementing Nonpartitioned, Bidirectional, Transactional ReplicationI'll be really surprised if its not there in your BOL.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-08-21 : 12:51:59
|
| kwilliams, in the original link I posted, you will see links to 6 different steps, that walk you through the complete process. Have you checked all those steps?--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-21 : 13:00:54
|
quote:
Originally posted by VyasKN
Tara, I am pasting the page title from my BOL:Implementing Nonpartitioned, Bidirectional, Transactional ReplicationI'll be really surprised if its not there in your BOL.
Not sure why I can't find it. How did you pull it up in BOL? I have searched on all of the above words, but none of the hits lead me to that article. I'm sure it's in there, I just can't find a way to pull it up. Thanks for the link though so that I can read about it.Tara |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-08-21 : 13:04:15
|
quote:
Not sure why I can't find it. How did you pull it up in BOL? I have searched on all of the above words, but none of the hits lead me to that article. I'm sure it's in there, I just can't find a way to pull it up. Thanks for the link though so that I can read about it.Tara
Just go to the Index tab of BOL and type in the word Bidirectional.This should bring up the item "Bidirectional Replication". Double click on it, you will be presented with links to two BOL pages.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-21 : 13:12:20
|
| I've got nothing for Bidirectional. I've got BETWEEN operators and then bigint data type. Bidirectional should be betwen those two. Also if I go to replication, I don't see Bidirectional underneath it either. The last thing that I installed here was service pack 3a for SQL Server 2000.Tara |
|
|
|
kwilliams
194 Posts |
Posted - 2003-08-21 : 14:12:44
|
| I looked up "Bidirectional" in my BOL, and it only pulled up subjects related to Merge Replication. They included:Replication Merge Agent UtilityExchangeType PropertyAgendts & MonitersEXCHANGE_TYPEHow Merge Replication Works...and when I opened any of the generic subjects, the text inside was related to merge.KWilliams |
|
|
|
PiecesOfEight
Posting Yak Master
200 Posts |
Posted - 2003-08-21 : 15:07:17
|
| If you open the "Contents" tab in BOL, navigate to Replication -> Planning for Replication -> Designing a Replication Topology. You can read the brief topic "Nonpartitioned, Bidirectional, Transactional Replication". At the bottom of the topic is a link to "Implementing Nonpartitioned, Bidirectional, Transactional Replication".As you noted, this is somehwat complicated to set up, but it can meet your needs if merge doesn't. Before you embark on this approach or choose merge: (1) What issues were you having with merge? (2) For table 4, are changes made at both DB1 and DB2 or only at DB2? If only at DB2, then you don't need bi-di or merge -- you can simply create two publications, one on DB1 (tables 1,2,3) and another on DB2 (table 4). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-21 : 15:17:41
|
I still don't see it in BOL. Here's what I see: Why is my BOL different? I'll redownload BOL and see if that fixes it. I must have a different version.Tara |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-08-21 : 16:29:10
|
| Tara, let us know, if redownloading fixes this. I checked it on my home laptop as well and I see this topic in BOL.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
Next Page
|
Two-Way Transactional Replication Setup