April 3, 2006 at 12:39 pm
I currently have transactional replication setup between to SQL Server 2000 sp3 databases. The replication has been running great with push subscription to the subscriber. There is a new request that has come in to setup transactional replication for six of the tables on the subscriber database to an oracle database. Is there any problem with setting up a subscriber database as a publisher to a new database? Are there any gotchas?
April 6, 2006 at 8:00 am
This was removed by the editor as SPAM
April 6, 2006 at 9:46 am
I don't think there should be any problems. I have a publisher DB that is also a subscriber from another server.
Is this your scenario ?
Publisher pushes Tables A,B,C,D .... etc to Subscriber, and now you want to push the same Tables A,B,C from the Subscriber to Oracle ?? If so, why not go straight from the original Publisher to Oracle ?
April 6, 2006 at 9:57 am
I asked that same question, and the developers response was that they try to limit direct access to the production database which is currently the publisher. The current subscriber that they now want to be the publisher to the oracle database was designed specifically for other systems and processes to access the data without having to hit the production OLTP database. I am going to try to setup the environment in dev to see if there will be any issues. Thanks for the response.
April 10, 2006 at 8:41 am
Why not use the current publisher to both subscribers? Just break out the publication to 2 separate ones; one for one subsriber, and one for both subscribers.
April 10, 2006 at 9:21 am
What I meant is that its the same thing. It does really matter how many subscribers you have. It will be a single Log Reader agent going againt the publisher. Only the distributor is affected because it will now have to push the transactions twice where needed.
June 26, 2006 at 12:32 pm
I have a couple of more questions regarding this replication setup.
1. You indicated that by setting up the second publication from the original publisher to the oracle database there would be no extra processing being needed on the publisher. Is this true? Does that mean any table currently involved in transactional replication, no matter how many publications will only be processed once by the log reader agent, and that the extra work will be done at the distributor, which is on a separate server?
2. I was in the process of setting up the requested publication, by setting the current subscriber (server B) as a publisher. Next I went to create the publication, but ran into a snag. The publication from server A to subscriber server B did not have the option to include declared referential integrity, so the tables at the subscriber (server B) do not have primary key constraints and therfore cannot be added to a transactional publication. Is there any problems with adding primary key constraints on the table at the subscriber server B? Will it break the current replication from server A to server B? Do I need to reinitialize the replication from server A to server B to include declared referential integrity to add the primary key constraints?
June 26, 2006 at 12:56 pm
Here's what I think
1. Are you referring to J Woo here ? If so, I think it's true ... IF you're publishing from just 1 server to multiple subscribers. But in #2, your talking about your original plan where Server A is a publisher, and Server B is also a publisher, so then you're really doing double work ..... with twice the maintenance.
2. I think you can add the keys to Server B so that it can publish to Server C. Also, I think that when you originally set up replication between A and B, you could have specified to copy over the keys etc... from A to B. You can add that provision now, but it may mark your subscriptions for re-initialization.
June 26, 2006 at 1:08 pm
Thank you for the reply. Yes, I know I could have marked the original publication to 'include declared referential integrity', but I didn't. At the time I did not think they were going to want to make the subscriber a publisher. I am a little concerned that creating the primary key constraints on the tables may break replication. I don't see why it would, but it's been my experience that replication can act strangely sometimes, so I'm a little wary.
The other thing is that I would prefer to just create another publication from the original publisher with just the seven tables to be replicated to oracle. Actually it would just be a subset of the columns of seven tables. The same seven tables are already in a publication going to another sql server instance. There distributor is on it's own server. If I did that would there be any additional work done on the publisher server? If not I would prefer to do that.
June 26, 2006 at 1:20 pm
I share your thoughts on replication's "strangeness" at times, although I think adding keys should be safe. I would set up a little test scenario that mimics your Prod environment and test it. However, I think A to B and A to C is better overall anyway. I would guess that adding an "A to C" publication adds a tiny bit of load to the server, since it needs to keep track of more info. I don't know how you'd measure it, but it can't be much.
For testing, I have my own little DBA database on each server and then I'll create a table with a couple of records and muck around with it to see what happens. Then I can just delete it all later when I'm done playing.
June 26, 2006 at 1:27 pm
Thanks again for the input homebrew01. I have a play area too, and I plan to do some tests there before going to production. That is where I tested the A to B, B to C scenario and it worked fine in test, but I didn't have the primary key constraint missing like I do in production. I'll test out both and turn tracing on to see what difference adding another publication makes. Thanks again.
July 11, 2006 at 7:05 am
I setup the replication from my subscriber to Oracle in production last night all went well for awhile, but after a few hours the replication failed with this error:
ORA-02291: integrity constraint (FINDSS.SYS_C003059) violated - parent key not found
There is referential integrity between the tables on the publisher server A, but not on the subscriber server B where the initial replication is done. As I said eariler I failed to check the option to include declared referential integrity. Now server B is the publisher to the subscriber Oracle database where referential integrity again exists. Is this what is causing the error, or could it be something else? Any ideas?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply