Solved

Replication error - ' unable to begin a distributed transaction''

Posted on 2009-06-30
9
3,130 Views
Last Modified: 2012-05-07
Summary :

I am trying to setup replication between 2 servers (both Win2008 Server OS and SQL v2008) as 'transactional with subscriber updates'.
When I setup the subscriper server I get the error:
      'SQLNCLI for linked server "Site_SQL02" was unable to begin a distributed transaction'
      'SQLNCLI for linked server "Site_SQL02" returned message "No transaction is active" error - 7391'
      

But if I setup merge replication it works fine between these two servers.

Or when I setup the same (transactional replication with subscription) between this publisher and a different SQLServer (Win2003 OS Server and Sql v2005) subscriber it works fine.

Detail:


The 2 servers are in the same domain but at different physical offices connected by a 2mbps satellite connection (not that fast, has other apps on that bandwidth).



I setup up the distributor (Win2008 Server, SQL Std v2008 SP1) Site_SQL02:
      - turned off the firewall
      - turned on DTS, enabled remote
      - SQLAgent running
      - using SSMS, setup this SQLServer as a distributor
      (SQL wizard - Configure Distributor : sets up the trans db in another folder and I setup that folder as a shared network folder. The share permissions are Everyone=Full)
      - using SSMS, created new publication 'Test04_trans'. This is a 'transactional with updates subscription, Subscriber pull'. The publication item is a single very simple table. The wizard added a field to this 'msrepl_tran_version'
      
      The script for this is shown in code window below. This publication procedure and script works successfully

I setup up the subscriber (Win2008 Server, SQL Std v2008 SP1) Office_SQL02:
      - turned off the firewall
      - turned on DTS, enabled remote
      - SQLAgent running

      - using SSMS, I am sitting on the Site_SQL02 box and in SSMS I Connect now to the other SQLServer, 'Office_SQL02'
      - whilst connected to this subscriber, Office_SQL02, created new subscription to the above publisher. The script for this is shown in code window below.
      
      This takes quite a while to run, like 4 to 5 mins. When it completes I get the error:
      
      

            TITLE: New Subscription Wizard
            ------------------------------

            SQL Server could not create a subscription for Subscriber 'Office_SQL01'.

            ------------------------------
            ADDITIONAL INFORMATION:

            An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

            ------------------------------

            The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "REPLLINK_Site_SQL02750965912_TESTRPLCN_-1284163390_TEST04_TRA2138751039_TESTRPLCN_312375228" was unable to begin a distributed transaction.
            Changed database context to 'TestRplcn_Subscriber'.
            OLE DB provider "SQLNCLI10" for linked server "REPLLINK_Site_SQL02750965912_TESTRPLCN_-1284163390_TEST04_TRA2138751039_TESTRPLCN_312375228" returned message "No transaction is active.". (Microsoft SQL Server, Error: 7391)

            
What I have troubleshooted:

      - tried setting up as a Push subscription (Site push to Office instead of pull). I get same error as above.
      
      - tried setting up the subscriber same as above, a Pull subscription, but this time while sitting at Office_SQL02 terminal and using SSMS direct on the machine there rather than setting up while sitting at Site_SLQ02, I get this error:
      
            TITLE: New Subscription Wizard
            ------------------------------

            SQL Server could not create a subscription for Subscriber 'Office_SQL02'.

            ------------------------------
            ADDITIONAL INFORMATION:

            An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

            ------------------------------

            Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "REPLLINK_Site_SQL02750965912_TESTRPLCN_-1284163390_TEST04_TRA2138751039_TESTRPLCN_312375228".
            Changed database context to 'TestRplcn_Subscriber'.
            OLE DB provider "SQLNCLI10" for linked server "REPLLINK_Site_SQL02750965912_TESTRPLCN_-1284163390_TEST04_TRA2138751039_TESTRPLCN_312375228" returned message "Unable to complete login process due to delay in opening server connection". (Microsoft SQL Server, Error: 7303)


      
      - I CAN setup merge subscription between Site_SQL02 and Office_SQL02 and it works. But I would like to use 'transactional with updates subscription, Subscriber pull' because our tables are very large and can change a lot and this is likely more appropriate to our setup.
      
      - I CAN setup a subscriber on another machine, a Win2003 Server with SQL Std v2005 (not v2008) and it sits in the same physical office (so not accessed across the satellite link), to the publisher (no change in the publication) and the replication works fine, both ways. I did this the exact same way as for Office_SQL02.
      

Any ideas?

Also, do I need SQL Browser service running?
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
 

/ ** setup the new publcation on Site_SQL02 **/
 

use [TestRplcn_Publisher]

exec sp_replicationdboption @dbname = N'TestRplcn_Publisher', @optname = N'publish', @value = N'true'

GO

-- Adding the transactional publication

use [TestRplcn_Publisher]

exec sp_addpublication @publication = N'Test04_trans', 

	@description = N'Transactional publication with updatable subscriptions of database ''TestRplcn_Publisher'' from Publisher ''Site_SQL02''.', 

	@sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', 

	@snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', 

	@add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'true', 

	@autogen_sync_procs = N'true', @allow_queued_tran = N'true', @allow_dts = N'false', 

	@conflict_policy = N'pub wins', @centralized_conflicts = N'true', @conflict_retention = 14, @queue_type = N'sql', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', 

	@enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

GO
 
 

exec sp_addpublication_snapshot @publication = N'Test04_trans', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, 

	@frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, 

	@active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa_Luke', @publisher_password = N''
 
 

use [TestRplcn_Publisher]

exec sp_addarticle @publication = N'Test04_trans', @article = N'tblTest01_trans', @source_owner = N'dbo', @source_object = N'tblTest01_trans',

	@type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008035CDF, 

	@identityrangemanagementoption = N'manual', @destination_table = N'tblTest01_trans', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false'

GO

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
 
 

-----------------BEGIN: Script to be run at Publisher 'Site_SQL02'-----------------

use [TestRplcn_Publisher]

exec sp_addsubscription @publication = N'Test04_trans', @subscriber = N'Office_SQL02', @destination_db = N'TestRplcn_Subscriber', @sync_type = N'Automatic', 

	@subscription_type = N'pull', @update_mode = N'queued failover'

GO

-----------------END: Script to be run at Publisher 'Site_SQL02'-----------------
 

-----------------BEGIN: Script to be run at Subscriber 'Office_SQL02'-----------------

use [TestRplcn_Subscriber]

exec sp_addpullsubscription @publisher = N'Site_SQL02', @publication = N'Test04_trans', @publisher_db = N'TestRplcn_Publisher', @independent_agent = N'True', 

	@subscription_type = N'pull', @description = N'', @update_mode = N'queued failover', @immediate_sync = 1

exec sp_link_publication @publisher = N'Site_SQL02', @publication = N'Test04_trans', @publisher_db = N'TestRplcn_Publisher', 

	@security_mode = 0, @login = N'sa_Luke', @password = null

exec sp_addpullsubscription_agent @publisher = N'Site_SQL02', @publisher_db = N'TestRplcn_Publisher', @publication = N'Test04_trans',

	 @distributor = N'Site_SQL02', @distributor_security_mode = 0, 

	 @distributor_login = N'sa_Luke', @distributor_password = null, 

	 @enabled_for_syncmgr = N'False', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 2, 

	 @frequency_subday_interval = 30, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090630, @active_end_date = 99991231, 

	 @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0

GO

-----------------END: Script to be run at Subscriber 'Office_SQL02'-----------------

Open in new window

0
Comment
Question by:LukeB
  • 5
  • 3
9 Comments
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24750756
check if your MSDTC service was set

---------------------

see
How to troubleshoot error 7391 that occurs when you use a linked server in SQL Server
http://support.microsoft.com/kb/306212
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24750783
how-to setup MSDTC

You receive error 7391 when you run a distributed transaction against a linked server in SQL Server 2000 on a computer that is running Windows Server 2003

http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
0
 
LVL 1

Accepted Solution

by:
LukeB earned 0 total points
ID: 24758158
Eugene,
I do have MSDTC setup, sorry was a typo in my post:
  turned on DTS, enabled remote

should have been :
   turned on DTC, enabled remote
I've got it to work - it was a security thing under the subscriber, I followed the SQLServer 2008 BOL:
Tutorial: Replicating Data Between Continuously Connected Servers
(ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rp_1devconc/html/7b18a04a-2c3d-4efe-a0bc-c3f92be72fd0.htm)

The thing is I do not understand why those users (4 on the Publisher, 2 on the Subscriber) have to be setup and looks like a workaround to mimic/impersonate users (local user on one server to the other).  In the real world I will like just set up one 'replcn' user and simplify a bit. It was the last step in this tutorial that really threw, me, see:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rp_1devconc/html/5995b7d2-7c06-46f5-b96c-2bee879bcda2.htm
the part on 'Setting database permissions at the Subscriber', without that (looks like a workaround to mimic local users at both machines?) it will not work.

So ... would you like to explain that and keep this Q open or close this Q as 'self solved' and I post another Q with my new question?
 
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24758260
Good Job!
open  new one Q with reference to this Q..
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 1

Author Comment

by:LukeB
ID: 24759352
Eugene,

I've done that, see Q_24538267
0
 
LVL 1

Author Comment

by:LukeB
ID: 24766746
moderator: I spoke too soon, I have not 100% solved it. Please leave Q open ...
0
 
LVL 1

Author Comment

by:LukeB
ID: 24768930


spoke too soon, it is not 100% working.

If I follow that MS tutorial it  works. But if is for is for transactional replication only, not 'transactional replication with subscriber updates'.

If I do the tuturial again and this time make the publisher 'transactional replication WITH subscriber updates' that goes ok. Then whien I do the 'Create Subscription' part and it creates the subscription I am getting an error show in attached "UnableToSetThePublisherLogin.jpg"



I think it has something to do with the wizard making a linked server and the linked accts (see LoginForUpdatableSubscriptions.jpg).

If I look at what the wizard did it creating a linked server calls it something like (See attached LinkedServerProperties.jpg):

  'REPLINK_ServerB750965012...'
and for Security it uses:
  local login= repllinkproxy
  remoteuser = sa (this is the remote sa)
  password = {password of remote server}


If I right click on the this linked server and use Test Link I get the error message:
The test connection to the linked server failed.

An exception occurred while executing a Transact-SQL statement or batch.   (Microsoft.SqlServer.ConnectionInfo)
Access to the remote server is denied because no login-mapping exists

So what I tried to do is add a New Linked Server myself by using SSMS - I right click on Linked Servers and get New Linked Server :
- at the General Tab I type in my Linked Server name which is the actual name of it, Site_SQL02.
- at Security I enter Local login = sa and remote users sa and the remote password
- at the Server Options
 Data Acess = True
 RPC = true
 RPC out = true
 Connection timeout =90
 Distributor=false
 Subscriber = false
 Enable Promotion of Distributed Transactions=True

when I do that I get the error shown in CreateLinkedServerError.jpg

"Cannnot initialize the data sosurce object of OLE provide SQLNCLI10 for linked server SITE_SQL02 - Unable to comple login process due to delay in opening server connection"

So there is some step missing here in both the Subscription Wizard and my manual Linked Server setup?
(sorry for screen shots, when I try to 'copy to clipboard' button on the error boxes it does not work)
LoginForUpdatableSubscriptions.jpg
UnableToSetThePublisherLogin.jpg
LinkedServerProperties.jpg
CreateLinkedServerError.jpg
0
 
LVL 1

Author Comment

by:LukeB
ID: 24799339
Need to create a linked server first and enable RPC
0
 

Expert Comment

by:pwharker
ID: 24896736
I have had success making the Linked Server connection work by opening the properties and changing the Security "For a login not defined in the list above, connections will: set to "Be made using this security context:" and entering the remote login information. In my case, I created a login on both the publisher and subscriber called "AppReplication" with server admin rights (just to eliminate that as one of the variables).
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now