Solved

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

Posted on 2009-07-01
10
635 Views
Last Modified: 2012-05-07

(this is 'parallel' somewhat related question to my PAQ : Q_24532695 which i self-solved by following the tutorial at: http://msdn.microsoft.com/en-us/library/aa337437.aspx)


Why for distributed transaction replication does MS recommend that we need to setup local machiine accounts at the Publisher and Subscriber and the account names are the same?

For example the Replication Distributed transaction tutorial at
   http://msdn.microsoft.com/en-us/library/aa337437.aspx
and then following
  http://msdn.microsoft.com/en-us/library/aa337429.aspx
under "Setting database permissions at the Subscriber "

it says to make a local user, like
   HeadOfficeSQL01\repl_distribution  (the publisher)
and
  SatOfficeSQL01\repl_distribution (the subscriber)
note that the  names are the same.

Question:
- why local users and why the same name? Is this some sort of impersonating?
- why not just use a domain user like 'domainname\SQLReplcn' for everything and do away with all the 6 local accounts? Or do some of the accounts need to be the local machine, not domain accounts
- why need to do the 'Setting database permissions at the Subscriber " for {localmachine}\repl_distribution ? If this is not done, and that user set as an db owner role as per the tutorial then the replication does NOT work.
0
Comment
Question by:LukeB
  • 6
  • 4
10 Comments
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
where did you find :
"
 MS recommend that we need to setup local machiine accounts at the Publisher and Subscriber and the account names are the same? "
0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
Eugene,
from the tutorial :

http://msdn.microsoft.com/en-us/library/aa337437.aspx

and then Lesson 1 Creating Windows Accounts for Replication.

 http://msdn.microsoft.com/en-us/library/aa337433.aspx

Follow that all the way thru Lesson 1,2,3 and you will see what I mean.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
it is just tutorial with 1 one of ways to setup replication (secured one)
however there are more info about replication security
Security Considerations for Replication
http://msdn.microsoft.com/en-us/library/ms151187(SQL.90).aspx

--
if your servers on the same domain and sql agents are running under the same domain account
just do as in this example

SQL Server 2005 - Merge Replication Step by Step Procedure
http://www.codeproject.com/KB/database/sql2005-replication.aspx

0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
Eugene,
for your second suggestion, 'Merge Replication Step by Step' that is for merge, which reading several times the MS explaination of difference between 'transational replication with subscriber update' and 'merge replication' that in our enviroment we should really not use merge.

see
    http://msdn.microsoft.com/en-us/library/ms152565.aspx

i.e. we have server-server and the data changes very frequently on both sides and is a lot of data.

It is also 'lite' on how to setup the security for the llinked server. But I looked at it, is similar to the MS Help tutorial. The place I am confused at is shown in attached LoginForUpdatableSubscriptions.jpg'   - if I setup a linked server on the remote, and is testing ok, that is what I am using at this setup point - 'use a linked serve you already defined'. But it fails to do the subscription., see NewSubscriptionWiward_err.jpg.

If I specify instead 'create a linked server' the I get the error at the end :

TITLE: New Subscription Wizard
------------------------------
Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Login failed for user 'ServerB\repl_distribution'.
Changed database context to 'TestRplcn_Subscriber'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

If I keep going, the table from ServerB does get replicated to ServerA .  If I make changes on BeverB they do get passed to ServerA, so far so good. But if I make changes on ServerA then they do  not get passed to ServerrB, I get an error show in NoRowUpdated.jpg
LoginForUpdatableSubscriptions.jpg
NewSubscriptionWiward-err.jpg
NoRowUpdated.jpg
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
I did not create linked servers for replication - -everything what need - sql server creates for you...
 try to start over just  for test to do
SQL Server 2005 - Merge Replication Step by Step Procedure
http://www.codeproject.com/KB/database/sql2005-replication.aspx
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
Comment Utility
and try for test to use sa sql server login for
LoginForUpdatableSubscriptions.jpg

and let sql server to create linked server...
and see result

0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
please clarify:
as per
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_24532695.html#24758158

you have sql server 2008 sp1 and sql server 2005 (sp?)

can you please check  if sql 2005 has a fresh sp?
 also what is publisher, etc...?

0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
Eugene
your 1st comment :
"just  for test to do SQL Server 2005 - Merge Replication Step by Step Procedure ".
Yes, that works. But see my note above:
'Merge Replication Step by Step' that is for merge, which reading several times the MS explaination of difference between 'transational replication with subscriber update' and 'merge replication' that in our enviroment we should really not use merge.
see
    http://msdn.microsoft.com/en-us/library/ms152565.aspx

i.e. we have server-server and the data changes very frequently on both sides and is a lot of data.

This is going to be an issue (problem?) for us in that we need to do the 'transactional replication with subscriber updates' but MS is deprecating this type of replication and appears to be recommending peer-to-peer replication (see BOL). But this peer-to-perr is only available in Enterprise version and we just bougth Standard version.

Again, we need to use  'transactional replication with subscriber updates' because it exactly matches the scenario in  http://msdn.microsoft.com/en-us/library/ms152565.aspx - we have a lot of data that changes often and many, many records get added by both the publisher and subscriper servers.
your 2nd comment :
for the "LoginForUpdate , let SQL create linked server" I used user sa, it appears to fail and gives a warning (see NewSubscriptionWizard_err2.jpg below) - but if I go to ServerA and ServerB and look at the replcated table it has been replicated and replication is working (I can change/add/delete records on both, both replcate back and forth).
But what is not clear is which 'sa' user is this? Is this at the publisher/distributor or subscriber?

So it is working, even tho SQL gives warnings. I will try to setup again with my domain login and not 'sa' for security and confusion reasons.
NEW issue : the replication that is working is Publisher PUSH, but I want to setup as Subscriber PULL to reduce load on publisher. When I follow thru the Wizard again, but this time specify PULL specifing security as shown in DistributionAgentSecurity_PULL.jpg, I get the error shown in NewSubscriptionWizard_err_PULL.jpg

Creating the subscription also took a long time, like 4 mnutes.

Please advise on how to modify to make as a PULL subscription - are you able to do on your machines?
your 3rd comment :
We use SQL v08, SP1 in both machines , both machines are WinOS 2008 server
NewSubscriptionWizard-err2.jpg
DistributionAgentSecurity-PULL.jpg
NewSubscriptionWizard-err-PULL.jpg
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
Hi LukeB,
Could we please start over?
please tell about your environment and what is what:
2 sql servers - edition\Version\SP,are they on the same domain or not, OS\SP\ (64 or 32 bit)?
What is a planned  replication topology ?
How MSDTC service was set up on the boxes?
Could you undo all changes that you did  (manual linked creation, logins such as repl_distribution, etc - those that usually created by sql server.)?
 
 
0
 
LVL 1

Author Comment

by:LukeB
Comment Utility
Hi Eugene,
Currently it is working , see my previous post 'Comment 2'. I do not want to undo all my changes just yet. The problem was the RPC was not ticked on the linked server and PAL not set for the Publicatoin that MS gives in the tutorial.

I am 90% there and is working for what my guys need. So I am going to close this. I will make a new post for you to comment on 2nd comment above:

NEW issue : the replication that is working is Publisher PUSH, but I want to setup as Subscriber PULL to reduce load on publisher. When I follow thru the Wizard again, but this time specify PULL specifing security as shown in DistributionAgentSecurity_PULL.jpg, I get the error shown in NewSubscriptionWizard_err_PULL.jpg

Creating the subscription also took a long time, like 4 mnutes.

Please advise on how to modify to make as a PULL subscription - are you able to do on your machines?

This will just make the replication a bit faster if the subscriber can PULL instead of the Publisher/Distributor PUSH
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 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

8 Experts available now in Live!

Get 1:1 Help Now