Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Continuous or Scheduled Replication (my two cents)
Published: Nov 11, 2013
This defines how often the SQL Server Agent job runs… does it run to a schedule, or does the job run continuously.

Each time the agent runs, any outstanding replicated data is passed from the distributor to the subscriber.

At this point I have to say that I believe Continuous to be a very bad choice and will only ever use a schedule for my replications. People may argue that this is incorrect, but I do have my reasons:

Despite its name, continuous replication is not continuous. The job itself is continuous but the data flow is not. If you were to look carefully at a continuous subscription in Replication Monitor, you can see the following:

As you can see… although the job is never ending, the act of moving data actually polls every 60 seconds, and therefore the frequency at which data is transferred is no different to having a 1 minute recurring schedule.

Also I have come across situations in which a job has mysteriously stopped and someone missed the alert as it was lost in a melee of emails. This meant that data was not flowing at all for several minutes until someone spotted an error on a company website. Not ideal at all.

The way I recommend avoiding this is that you either have a job that runs every minute checking for stopped continuous replication jobs and starting them or to add a secondary schedule to your job which runs every minute. Therefore this will re-start a continuous job within a minute of it stopping. However, if you’re going to go to these lengths why not just run with the minute schedule instead?

And lastly, for those of you still not convinced, there are some key elements within replication which rely on the agent starting and stopping otherwise they are never triggered.

For example, if you have an identity column in replication and you are relying on replication to manage the identity range, this will only happen if you stop and start the agent as only then will it check on your ranges and thresholds and allocate as necessary.

Likewise the replication history and general clean up jobs are allowed to perform much more efficiently if you are not using continuous replication. This reduces the size of your Distribution database, speeds up the cleanup jobs, and provides a slicker replication as a whole.

Therefore, as you can tell, I’m an advocate of everything being run as a schedule. But I know there are some who disagree with me.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.