0

I have transactional replication setup in SQL Server 2022. I have a distribution server running push subscriptions to subscribers. I'm trying to figure out if I need to stop replication jobs (log reader and distribution jobs) when running maintenance jobs such as backups, re-indexing, update stats, and DBCC maintenance? I'm trying to avoid causing any replication outage due to maintenance jobs.

Replication jobs are setup to run continuously when SQL Server starts. I did this for best practices because we were seeing latency when I scheduled these jobs instead of running them continuously. I made reset jobs to run continuously among a few other changes and we no longer see latency.

If I need to stop jobs while running maintenance jobs; can someone recommend the best way to do this. Like I said, I was trying to avoid scheduling these jobs. I was thinking if needed; I could put a step in the first maintenance job that is scheduled to run to stop replication job and restart it after the last step of the last maintenance job. Any thoughts?

I haven't tried to run maintenance jobs yet as this is in a new production environment, and I didn't want to have to apply another snapshot in case maintenance jobs break replication.

2 Answers 2

0

I've never had a need to be concerned about database maintenance affecting replication, especially with backups, updating stats, and CHECKDB. Index Maintenance is a waste, and is wasteful on server resources no matter what, so it's usually not worth running anyway. But if you decide to anyway, I still wouldn't stop replication during such as a task.

FWIW, if your goal is "to avoid causing any replication outage due to maintenance jobs" then stopping replication to run your maintenance jobs would be counter to that. Just let replication continue to do its thing, naturally, and it'll eventually catch up on its own if the server resources become under contention. But generally those maintenance tasks shouldn't really affect it negatively.

-1

I have previously performed such maintenance tasks on older mirror replication setups. In line with your suggestion to prevent any impact on the replication process, I would pause the replication before carrying out index and statistics maintenance, and then resume it afterwards. However, newer replication models, such as Azure's geo-replication, have the capability to replicate the reindexing as it occurs on the primary database.

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.