March 23, 2024 at 4:07 pm
I have transactional replication setup in SQL 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 Maintenace? I'm trying to avoid causing any replication outage due to Maintenace jobs.
Replication jobs are setup to run continuously when the 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 reset jobs to run continuously among a few other changes and we no longer see latency.
If I need to stop jobs while running Maintenace 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 Maintenace 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.
March 23, 2024 at 8:46 pm
Step 1 would be to stop doing the re-indexing. Chances are that you're actually causing more damage than you're fixing especially if your using REORGANIZE or the old supposed 5/30 "Best Practice" that was erroneously adopted by more than 95% of the world.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2024 at 5:46 pm
I have transactional replication setup in SQL 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 Maintenace? I'm trying to avoid causing any replication outage due to Maintenace jobs.
Replication jobs are setup to run continuously when the 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 reset jobs to run continuously among a few other changes and we no longer see latency.
If I need to stop jobs while running Maintenace 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 Maintenace 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.
I have never needed to stop or pause any transactional replication or maintenance because there was contention. Snapshot replication is a different story. You should not need to either, unless you are running on a severely under powered machine.
DBCC creates a snapshot of the database. Updating stats is so lightweight that you should not see any effect, unless you are blindly updating stats on every table if it needs it or not. Backups are meant to work with these operations. And as Jeff said, you are wasting resources by reindexing.
Can you provide some specifics on the replication? How large are the databases? What is the frequency and size of the log backups? Do you have virus scanning excluding the proper files and folders?
And, what issues are you seeing?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 26, 2024 at 7:35 am
Hey! You've got a good question about keeping your replication running smoothly during SQL Server maintenance. Here's the scoop:
In most cases, you don't need to stop your replication jobs (like the log reader and distribution agent) when running regular maintenance tasks like backups, re-indexing, or even DBCC CHECKDB. These jobs are designed to work alongside replication without causing any issues.
Here's why:
Replication is built to handle these maintenance tasks. It can keep track of changes happening during maintenance and apply them to your subscribers later.
Stopping and restarting replication jobs can actually introduce some lag or downtime, which you're trying to avoid.
However, there are a few exceptions:
If you're doing some seriously heavy-duty maintenance that might mess with the transaction logs (like a major rebuild), it might be a good idea to pause replication temporarily. But for most routine stuff, you're good to go.
Here's the thing about scheduling the replication jobs – if running them continuously caused latency before, it might be worth investigating that further. There could be some other setting causing the slowdown.
Now, if you're really set on pausing replication during maintenance, you can add a step to your first maintenance job that stops the replication and then another step in the last job to restart it. But honestly, it's usually not necessary.
Since this is a new environment, it's totally understandable to be cautious. But based on what you described, your regular maintenance shouldn't break replication. Just keep those jobs running and your data will keep flowing smoothly to your subscribers.
Marry JWQ
March 26, 2024 at 5:51 pm
In most cases, you don't need to stop your replication jobs (like the log reader and distribution agent) when running regular maintenance tasks like backups, re-indexing, or even DBCC CHECKDB. These jobs are designed to work alongside replication without causing any issues.
Why is reindexing part of your regular maintenance?
From Microsoft's recommendations, https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16
Index maintenance decisions should be made after considering multiple factors in the specific context of each workload, including the resource cost of maintenance. They should not be based on fixed fragmentation or page density thresholds alone.
What benefit have you measured from rebuilding indexes?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply