Updateable Transactional publishing

Publisher and Distributor: Server (DC)

Subscriber: KingPC (member server)

Configuring MSDTC at both Publisher and Subscriber

RUN -- dcomcnfg

Component Services --Computers -- properties

MSDTC tab -- select "Use local coordinator"

--Security Configuration button --Select "Network Security Access"

Setting up Queue Reader Agent security

Db_owner of distribution database, publishing database, and subscriber databases.

Security for snapshot agent and distribution agent refers to my other blog.

Create a SQL Server account: QueueUpdate in publisher. QueueUpdate must has SELECT, DELETE, INSERT and UPDATE permissions on the publishing objects (tables, etc.). I assign it to be a member of db_datareader and db_datawriter, to simplify the permission assignment.

Publication Access List includes the QueueUpdate account.

Creating the subscription in subscriber

Checking the subscriber SQL server, you should have a Linked Server defined as:

When you are updating the data in subscriber, if you get error: "The subscription is uninitialized or unavailable for immediate updating as it is marked for reinitialization. If using queued failover option, run Queue Reader Agent for subscription initialization. Try again after the (re)initialization completes.Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8Updateable Subscriptions: Rolling back transaction.Msg 3609, Level 16, State 1, Line 2The transaction ended in the trigger. The batch has been aborted.", the agent security is mostly the problem. I launch the Replication Monitor

The above error message occurs because Queue Reader Agent has error. The above graph does not include the Distribution agent because the subscriber is using a pull subscription.