Replication

This is a direct follow up to my previous post on Tracer Tokens and Replication Latency.


This post will include some simple code to check for excessive latency in your replication and, if appropriate, send out an email informing you of the problem.

After my last post surrounding Tracer Tokens I was told by a friend that it was a little harsh to simply end saying that you can just join to a few system tables and make something meaningful. Therefore I’ve given in and this post will show you how you can use tracer token and system data to your advantage.

If you’re using Transactional Replication then these can be invaluable to you. These are incredibly lightweight, easy to use, and they will help you with all manner of reporting and troubleshooting within your replicated environment.

This is just a quick post as it’s something I came across the other day and I thought it warranted a note and word of caution.

Ever been in one of those situations in which you see endless blocking whilst replication makes new generations and you’re left wondering if there’s a specific table causing it or whether it’s just generic replication volume which is causing your problem?

Yesterday I had an issue in which someone had accidentally run a tidy up script on a transactionally replicated table without considering the consequences this would have on replication.

I’ve had numerous occasions in which I see something going wrong with replication, but rarely are the error messages clear and, to be honest, most of these messages simply specify something along the line of “enable verbose logging”.

This came about as a result of a series of mass updates being applied to a replicated table. This series of updates was causing replication to lock up and become so far behind that the only acceptable course of action was a full rebuild. This is by far and away the course of action I least like taking, but in this example it was unavoidable.

This is something that I never thought to blog about as I figured it was a common practice. I’ve no idea where I got that idea from as I’d never spoken to anyone about it, just used it within a couple of environments.

This is a topic which I encountered myself a few months back and it drove me mad for a while, but the solution is actually incredibly simple.

A company I was visiting were having problems with the "Agent History Clean up" job that Microsoft installs as standard on a Distributor server. They had disabled the job because, as the job history showed, it was no longer completing as expected but was running forever and in doing so was causing the distribution database log file to rapidly increase in size until it Windows reported the disk as full. Once full they were having to kill the job, restart SQL Server (very risky as the job was now in rollback but couldn't comlpete due to disk space), truncate the transaction log, and then fix the backup chain for the sake of their disaster recovery requirements. This was clearly a huge problem for them as, without this job running, the distribution database would never stop increasing in size.

This is an issue I’ve only seen a few times in our system, but when it does appear the effects are very noticeable indeed.

This was something I came across when working in a heavily replicated environment… the replication created job “Distribution clean up: distribution” suddenly began to fail constantly. This led me on a trail which had such drastic results that I felt compelled to document it.

One of the most crucial and most complex stages in selecting the Article Properties of a table with an Identity column is how to utilise replication's Identity Range Management.

I was working on one site when we got a very strange replication error appear. I’ve still no idea what caused it, but that won’t stop me posting the error message itself along with the simple solution…

In order to get up and running creating Publications on your server, you first need to either set up a Distributor, or register a pre-existing remote Distributor. To do this we’ll use the Wizard.


In SSMS, right click Replication and select “Configure Distribution…”:

When adding articles to replication one of the most important things to consider are its properties. Get these wrong and your replication may not work as expected, but get them right and you’ll be plain sailing.

This defines how often the SQL Server Agent job runs… does it run to a schedule, or does the job run continuously.

Once you have created your publication you will need to subscribe to it (otherwise it’s pretty pointless really).

This is generic guide as, to be honest, setting up all replication is pretty much the same. There are a couple of extra screens for Merge replication and I’ll highlight those where necessary. Otherwise it’s all the same.

When Subscribing to a Publication you will need to decide whether you want to use a Push or a Pull subscription. The difference may not sound much on paper, but there are some key points that really need to be noted before you make your choice.