Sql-server – Set up new publication on SQL Server 2014 database which is part of an availability group

availability-groupssql serversql server 2014transactional-replication

I like to setup a publication (transactional replication) on a SQL Server 2014 database which is part of an availability group. We have a primary and one secondary (non-readable) replica.

On the instance I have already another running publication. The distributor is running outside of the availability group, even on a different domain.

Problem

Initially the set up of the distributor was done on the other replica (which is currently the secondary). Also the other existing publication was initially created on the other hosts. That's the first publication I have to create after I did a failover of the availability group to the secondary.

Error

Running the following command on the database to publish 

EXEC sp_replicationdboption @dbname = 'mydatabase',
                            @optname = N'publish',
                            @value = N'true';

I received the following error:

Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 56 [Batch Start Line 0]
The Distributor has not been installed correctly. Could not enable database for publishing.

I wonder why that occur as I have already on publication up and running on another database without any error and also working properly.

Ideas

Checked the management logs doesn't help as nothing was logged. After cross checking the distributor I saw also that the publisher is registered properly.

Any ideas what could be issues here?

Best Answer

I found a solution for my problem and like to share it.

Solution

By using the fourth parameter ignore_distributor I could get around the problem and was able to set option publish for my db.

exec sp_replicationdboption @dbname = N'mydatabase', 
                            @optname = N'publish', 
                            @value = N'true', 
                            @ignore_distributor=1

But unfortunately it wasn't solving my overall issue on creating a new publication on this existing publisher.

New (second) Problem

The next step was to create the log reader agent job by executing the following command.

EXEC sys.sp_addlogreader_agent @job_login = N'<domain user>', 
                               @job_password = N'<password>', 
                               @publisher_security_mode = 0,
                               @publisher_login = N'<username>',
                               @publisher_password = N'<password>';

It resulted into the following error

The Publisher 'ServerB' does not exist. To view a list of Publishers, use the stored procedure sp_helpdistpublisher.

Ok, strange as I had a running publication from this server. So, I was checking it by executing the mentioned SP on my distributor. The result was showing me my publisher correctly. So far so good. Where is now the problem? Even after checking a couple of other thinks on my publisher I wasn't able to find any think wrong. Let's check the distributor and run a trace when I executed my above command.

At the end the issue was created by the system procedure sp_MSreplagentjobexists. It was checking the publisher configured in the table dbo.MSreplservers within the distribution db. Bingo! My publisher was missing in there. I only found my original publisher in there. So, getting the server id from sys.server and insert the current primary node of my availability into this table worked like a charm.

The last think I had to do was to redirect my publisher which are running on an availability group to my AG listener.

-- redirect ServerA
exec sys.sp_redirect_publisher
       @Original_publisher = 'ServerA_primary_node',
       @Publisher_db = 'mydatabase_to_publish',
       @Redirected_Publisher = 'AGListener';
GO

-- redirect ServerB
exec sys.sp_redirect_publisher
       @Original_publisher = 'ServerB_seconary_replica',
       @Publisher_db = 'mydatabase_to_publish',
       @Redirected_Publisher = 'AGListener';
GO

Finally my new publication was working :)

So, I am not sure if this a bug and configuration issue made in the past. As I have no idea how and when the entries are made in the table MSreplservers of the distribution.

Any thoughts?

Related Question