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.
