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.
LVL 1
LukeBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
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
LukeBAuthor Commented:
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
Eugene ZCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

LukeBAuthor Commented:
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
Eugene ZCommented:
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
Eugene ZCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eugene ZCommented:
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
LukeBAuthor Commented:
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
Eugene ZCommented:
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
LukeBAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.