SQL Server Performance

Updatable Subscription Issue

Discussion in 'SQL Server 2005 Replication' started by jaco.els, Sep 9, 2008.

  1. jaco.els New Member

    I’ve set up transactional replication with updatable subscriptions. Server A is the Publisher and Server B the subscriber. The database on A is Published and the snapshot generated without any issue. When I create the Subscription on Server B (push subscription from A), the snapshot is read successfully, but I get the following error:The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "LINKED_SERVER_ON_SERVER_B" was unable to begin a distributed transaction.Changed database context to 'Replicated_Database'.OLE DB provider "SQLNCLI" for linked server "LINKED_SERVER_ON_SERVER_B" returned message "The transaction manager has disabled its support for remote/network transactions.". (Microsoft SQL Server, Error: 7391)I then completed the following as per the MS forums:To allow the network transaction, you must enable MSDTC. To do this, follow these steps: a. Click Start, and then click Run. b. In the Run dialog box, type dcomcnfg.exe, and then click OK. c. In the Component Services window, expand Component Services, expand Computers, and then expand My Computer. d. Right-click My Computer, and then click Properties. e. In the My Computer Properties dialog box, click Security Configuration on the MSDTC tab. f. In the Security Configuration dialog box, click to select the Network DTC Access check box. g. To allow the distributed transaction to run on this computer from a remote computer, click to select the Allow Inbound check box. h. To allow the distributed transaction to run on a remote computer from this computer, click to select the Allow Outbound check box. i. Under the Transaction Manager Communication group, click to select the No Authentication Required option. j. In the Security Configuration dialog box, click OK. k. In the My Computer Properties dialog box, click OK. source: http://support.microsoft.com/?kbid=873160I also made some changes to the linked servers created during replication setup. I’ve got the remote server login set up as a sql server admin account. I can run distributed queries from both Server A and B using the linked servers.My issue is that when I do an update on the subscriber, I get the following error:Msg 21079, Level 16, State 1, Procedure sp_getpublisherlink, Line 52The RPC security information for the Publisher is missing or invalid. Use sp_link_publication to specify it.Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8Updateable Subscriptions: Rolling back transaction.Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.Any help would be very welcomeThanksJaco
  2. ghemant Moderator

  3. satya Moderator

    Welcome to the forums.check that your replication package is properly configured with correct authentication information. Make sure to create linked server between the replication instances, http://support.microsoft.com/kb/320773 fyi.
  4. Jeff Fortney New Member

    We were having this same issue during the setup of the subscribing server. The process would complete with the warning above. The solution we found was that the local firewall settings had the DTC options disabled. This was found through the Windows Log on both the Subscribng and Publishing servers under the event id 4359 (http://technet.microsoft.com/en-us/library/cc774168(v=ws.10).aspx). When we looked that id up we found a section describing the firewall settings and network connectivity. It was the firewall section that lead us to the solution, which was simply enabling the DTC options. The settings on the Publishing server were enabled, but there were 3 options on the Subscribing server that we had to enable through the Advanced Settings section: Distributed Transaction Coordinator (RPC), Distributed Transaction Coordinator (RPC-EPMAP) and Distributed Transaction Coordinator (TCP-In). It's possible only one of these options needed enabling, but we enabled all 3. We then removed the existing subscription and recreated it with no issues.

Share This Page