Forum Discussion

shivkumarm's avatar
shivkumarm
Copper Contributor
Jan 23, 2024

Transactional Replication - Publisher Issue

We have 3 Node SQL Server Cluster (Always On availability group).

I wanted to implement Transactional Replication on 3rd Node which is configured as Asynchronous.

 

When I tried to create Publisher, initally I got error: 

 

This database is not enabled for publication.
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
Changed database context to 'prod'. (.Net SqlClient Data Provider)

 


Then I tried to add the database for Publication using following command.

 

USE prod
EXEC sp_replicationdboption @dbname = 'prod',
@optname = 'publish',
@value = 'true'
GO

 


I got a another error:

 

Msg 3906, Level 16, State 2, Procedure sys.sp_MSdrop_pub_tables, Line 6 [Batch Start Line 0]
Failed to update database "prod" because the database is read-only.

 


The above error was surprising, since I can see in the Database options that Read Only option is False.



Then I tried to fix the update issue using following commands:

 

USE prod GO
ALTER DATABASE [prod] SET READ_WRITE WITH NO_WAIT
GO

 


and now I am facing error:

 

Msg 1468, Level 16, State 3, Line 9
The operation cannot be performed on database "prod" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 9
ALTER DATABASE statement failed.

 


I tried on Node 2, there I got the following error:

Msg 976, Level 14, State 1, Line 1
The target database, 'prod', is participating in an availability group and is currently not accessible for queries.
Either data movement is suspended or the availability replica is not enabled for read access. 
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  
For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

 

Following is my cluster configuration:

 

 

What steps or configuration I will have to make in order to enable Transactional Replication on Node 3 or Node 2

 

  • BabatundeDallas's avatar
    BabatundeDallas
    Brass Contributor
    Jan 24, 2024

    shivkumarm the 3rd error suggests that the database is part of a mirroring session or an availability group. You must temporarily suspend or remove it from these configurations to perform the necessary operations.  For mirroring, you can use: 

    ALTER DATABASE [prod] SET PARTNER SUSPEND

     You may need to temporarily remove the database from the availability group for availability groups or ensure the availability group is configured correctly and the secondary replicas are accessible for read access.

     

    After resolving these issues, you should be able to set up Transactional Replication on Node 3 or Node 2. Remember to reconfigure mirroring or availability groups after completing the replication setup.

     

    Always perform these operations during scheduled maintenance windows and ensure you have tested them in a non-production environment before applying changes to a production system.

     

     Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
    Note: 

    If this post helps, please consider Accepting it as the solution to help others find it more quickly. OR
    If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
     
    Best Regards,
    Dallas.

Share