0

I am trying to setup Transactional Replication to from Server A (Publisher) to Server B (Subscriber) using a remote Distribution Server - Dist A (Distribution). Dist A already services multiple publications from multiple publishers.

Details about environment:

Server A:

  • Version: SQL 2017 v 14.0.3465.1
  • Cluster: Traditional FCI - No Availability Groups
  • Server A is a new installation with 1 empty database that has never been setup for replication before.

Dist A:

  • Version: SQL 2022 v 16.0.4085.2
  • Cluster: Traditional FCI - No Availability Groups
  • Dist A is an existing Distribution Server used for Production Transactional and Merge Replication by 4 Publisher Servers and 20 Subscriber Servers with ~30 Unique Publications.

I am trying to create my first publication on Server A using a brand new database. I successfully configured the Publisher settings on Dist A (allowing Server A to be a Publisher and dictating which Distribution Database it will use). On Server A, I added Dist A as its distributor, then enabled the database for Transactional Replication.

I am now attempting to create the Log Reader for the Database on Server A and receiving the following error:

USE [Database];
GO
EXEC sys.sp_addlogreader_agent
        @job_login = N'<UserName>',
        @job_password = '<Password>',
        @publisher_security_mode = 1;
GO

Msg 3933, Level 16, State 1, Procedure sys.sp_MSrepl_addlogreader_agent, Line 184 [Batch Start Line 16] Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.

When researching this error, I am only finding posts referencing Replication and Availability Groups. We do not have Availability Groups on Server A or Dist A.

We have confirmed that the items in sys.configurations match those of other servers who are actively publishing. Linked Server settings to the Distributor are equal to those working servers as well.

I am trying to find out what setting or configuration item was missed.

1 Answer 1

0

It turns out the issue was the "Remote Proc Trans" configuration item. When set to 1, sp_addlogreader_agent SP fails to execute. The setting must be set to 0 when attempting to setup / configure replication. Once the Publication / Subscribers were created, we were able to turn "Remote Proc Trans" back to 1 in and our user processes were able to continue working.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.