Solved

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

Posted on 2009-07-01
10
644 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
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 42

Expert Comment

by:EugeneZ
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 42

Expert Comment

by:EugeneZ
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 42

Accepted Solution

by:
EugeneZ 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 42

Expert Comment

by:EugeneZ
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 42

Expert Comment

by:EugeneZ
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 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