asked on

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

(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.

EugeneZ

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? "

LukeB

ASKER

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.

EugeneZ

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

LukeB

ASKER

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

EugeneZ

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

ASKER CERTIFIED SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EugeneZ

please clarify:
as per
https://www.experts-exchange.com/questions/24532695/Replication-error-'-unable-to-begin-a-distributed-transaction''.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...?

LukeB

ASKER

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

EugeneZ

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.)?

LukeB

ASKER

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