September 21, 2012 at 10:14 am
After setting up Replication (Transactional Publication with updatable Subscription)
between two instances one instance being both Distributor and Publisher, with the other being a Subscriber running on my local machine.
I managed to replicate data between instances from the publisher Instance, however whenever I try to update the table that is being replicated at the subscriber which should then instantly update the publisher I get the errors below.
ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) --- Server 'repl_distributor' is not configured for DATA ACCESS. (Microsoft SQL Server, Error: 7411)
The RPC security information for the Publisher is missing or invalid
Use
sp_link_publication to specify it
Publisher server is not configured for RPC
The publisher is set up for RPC and the distributor was configured for data access. Not sure how to replicate updates from subscriber back to publisher.
September 26, 2012 at 1:42 am
kingdonshel (9/21/2012)
After setting up Replication (Transactional Publication with updatable Subscription)between two instances one instance being both Distributor and Publisher, with the other being a Subscriber running on my local machine.
I managed to replicate data between instances from the publisher Instance, however whenever I try to update the table that is being replicated at the subscriber which should then instantly update the publisher I get the errors below.
ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) --- Server 'repl_distributor' is not configured for DATA ACCESS. (Microsoft SQL Server, Error: 7411)
The RPC security information for the Publisher is missing or invalid
Use
sp_link_publication to specify it
Publisher server is not configured for RPC
The publisher is set up for RPC and the distributor was configured for data access. Not sure how to replicate updates from subscriber back to publisher.
I have never worked with this replication scenario, however the error message to me is somewhat familiar. When you create replication, a linked server is created on the publisher end for the subscriber.
Do 1 thing. Set up a demo replication in test environment. Go the the linked server. Right click on that and in properties enable RPC and Data access. Let me know if this works.
Thanks
Chandan
September 26, 2012 at 1:14 pm
I did have that before if you look at the bottom of my posting you can see where I said RPC is already set to True.
September 26, 2012 at 10:34 pm
Sorry about that. Did you turn on the data access true as well?
September 26, 2012 at 10:38 pm
My gut feeling is that you can get this done by tweaking linked server only. I will try to re-produce this in my environment today.
Thanks
Chandan
September 26, 2012 at 11:36 pm
Yes I have set data access to true as well , however I dont see why I should have to if the link server is created automatically for replication from what I can see DATA ACCESS should be set to true, anyway I did.
When you try this in you environment remembr the type of Replication option I was trying was
Transactional Publication with updatable Subscriptions.
This allows changes to replication back to the publisher even when changes are made at the subscriber, it also allows changes to be replicated to the Subscirber even when updates is made at the Publisher.
I think part of the problem is the link server created on the distributer (repl_distributor) is run under the distributor_admin user --see Logins under Security. However the link server at the Subscriber is running under a different user.This happens automatically and there is no access to the password for these usernames.
I tried to set up my own Subscriber link server and tried to set up local to remote server mappings based on my admin user account however the link server could not connect ,I got the RPC Error message.
Also remember I am not on a network I am using my local machine which is running 2 Instanced.
September 29, 2012 at 10:55 pm
Hello,
Did you started MSDTC, if not please start and allow inbound and outbound.
I think your problem should be resolved.
September 30, 2012 at 11:21 am
Thanks a million Raj, this works I droppped all link servers, users and logins created by replication, started MDTC in Services and recreated the type of replication described in my initial post. Now when I update the replicated table at the subcriber or publisher the updated table changes are updated in both places. Thank you so much.
This is the first time any of these sites has ever given me a solution that works, and thanks to for the suggestions from other user they helped to improve my knowledge.
September 30, 2012 at 11:24 am
Just to mention,I did not see any inbound or outbound option anywhere, I am using SQL Server 2008.
October 1, 2012 at 2:34 am
kingdonshel (9/30/2012)
Just to mention,I did not see any inbound or outbound option anywhere, I am using SQL Server 2008.
I am glad that this worked for you. MSDTC options would be configured from windows level. Control panel has some option called 'add windows component' and there you can configure msdtc. Use web to find exact options to configure it for your OS.
Cheers!
Chandan
October 3, 2012 at 6:26 am
As an addition to the above with MSDTC. If you are suing VMWare and deploying virtual machines from a Template, you may also see this error when setting up replication as the cids for msdtc will probably be the same.
I have written a quick blog post on this:
This behaviour can cause quite a few issues with SQL Server so it's a good one to watch out for in a Virual Server Environment....
October 4, 2012 at 1:28 am
Hi Raj,
I have a doubt, we commonly using MSDTC in cluster environment and i am configured lot of times replication but never touch the MSDTC services. Could you please collaborate this one..
Thanks
Satish
October 4, 2012 at 2:17 am
saidapurs (10/4/2012)
Hi Raj,I have a doubt, we commonly using MSDTC in cluster environment and i am configured lot of times replication but never touch the MSDTC services. Could you please collaborate this one..
Thanks
Satish
Hi Satish,
My reply was in regards to duplicate cids if you are deploying Virtual Servers from a preconfigured template.
You don't necessarily have to configure MSDTC for replication, but it does need to be running, from what I remember SQL would use MSDTC for the linked servers it creates when you set up Replication....
See the below from a post in the MSDN Forums:
Cheers Paul
October 5, 2012 at 4:19 pm
You'll need MS DTC running if you are using 'immediate updating' updateable subscriptions. The other option is 'queued updating.' Here the changes are stored at the client and pushed when the publisher can be contacted.
----------------------------------------------------
October 5, 2012 at 9:30 pm
"Did you started MSDTC, if not please start and allow inbound and outbound.
I think your problem should be resolved"
Hi guys in earlier post Raj suggested to turn on MSDTC, I turned it on in Serviced and my configured Repliction (Transactional Publication with updatable Subscription)
started to work. In his post he mentionded that I should allow inbouns and outbound when I turned on MSDTC, does anyone knows what or where inbound and outbound is,since I did not see those options when I turned on MSDTC.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply