MSrepl_agent_jobs does not exists
Setting up Transactional Replication in an AlwaysOn Availability Group environment and getting error similar to one shown below:
The specified table (‘MSrepl_agent_jobs’) does not exist.
MSrepl_agent_jobs is a new table added to Distribution database in SQL 2017 CU6 and SQL 2016 SP2-CU3 to support Replication in an AlwaysOn Availability Group environment.
“SQL Server 2017 CU6 and SQL Server 2016 SP2-CU3 introduces support for replication distribution database in an AG”
This table contains the SQL Server Agent Replication jobs information and is queried by the new SQL Server Agent job “Monitor and sync replication agent jobs”. This job calls sp_MSrepl_monitor_job_at_failover ensuring the required Replication Jobs are created and configured on each replica with the correct listener names following an Availability Group failover.
-- Name: sp_MSrepl_monitor_job_at_failover
-- Descriptions: Synchronize replication agent jobs to the new primary replica after
-- an availability group failover. Also enable distribution database
-- associated SQL Agent jobs on primary replica and disable them on
-- secondary replica.
When setting up replication with AlwaysOn Availability Groups with SQL Server 2016, ensure the Distribution role server has been upgraded to at least SQL 2016 CU6 or SQL 2016 SP2-CU3 and your running version 17.9 of SQL Server Management Studio containing the updated Replication Monitor.
Trying to set up a PULL subscription and getting this error, check your SQL Server version by executing “select @@version”. If shows 13.0.5264.1 you SQL Server is running Cumulate Update 5, upgrade Publisher, Distributor, and Subscriber Cumulate Update 6 to correct this problem.