Solved

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

Posted on 2009-07-01
10
651 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 24768324
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
ID: 24769199
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 43

Expert Comment

by:Eugene Z
ID: 24774635
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Author Comment

by:LukeB
ID: 24776523
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 43

Expert Comment

by:Eugene Z
ID: 24778725
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
 
LVL 43

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 24778727
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 43

Expert Comment

by:Eugene Z
ID: 24778732
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
ID: 24782105
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 43

Expert Comment

by:Eugene Z
ID: 24785073
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
ID: 24799301
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

729 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