SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication


Replication

Author
Message
kingdonshel
kingdonshel
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 216
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.
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2259 Visits: 2134
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
kingdonshel
kingdonshel
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 216
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.
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2259 Visits: 2134
Sorry about that. Did you turn on the data access true as well?
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2259 Visits: 2134
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
kingdonshel
kingdonshel
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 216
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.
raj200986
raj200986
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 2
Hello,

Did you started MSDTC, if not please start and allow inbound and outbound.
I think your problem should be resolved.
kingdonshel
kingdonshel
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 216
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.
kingdonshel
kingdonshel
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 216
Just to mention,I did not see any inbound or outbound option anywhere, I am using SQL Server 2008.
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2259 Visits: 2134
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search