Alerting For Replication Latency Using Tracer Tokens
Produced: 05/07/2015 07:53:00
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.

Gathering Tracer Token Data
Produced: 28/06/2015 07:50:00
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.

Tracer Tokens
Produced: 21/06/2015 07:42:00
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.

Updating Varchar Column Values In Replicated Tables
Produced: 22/03/2015 08:01:00
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.

Basically I was alerted that replication had ground to a halt and was suffering badly after a developer had performed an update. Their logic was “it was only 100,000 rows, it should be able to cope with that”.

Merge Replication Commands in Pipeline
Produced: 21/09/2014 11:38:00
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?

Remove Rogue Large Transactions From Replication
Produced: 27/07/2014 17:40:00
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.

Effectively what they had done was delete 2.5 million records in one transaction which amounted to around 5GB of data changes heading into the replication network. Not good.

Replication Verbose Logging
Produced: 20/07/2014 17:22:00
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 exact situation happened recently in which replication inexplicably decided that working was for suckers and it simply wasn’t going to bother… again only informing me that “verbose logging” was required.

Which Columns Are Being Updated
Produced: 15/06/2014 10:14:00
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.

Replication and the Disabled Index
Produced: 24/11/2013 17:53:00
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.

Anyway, having spoken to a couple of MCMs about this it turns out my “common practice” is actually incredibly rare and not many people know about it. Therefore, presuming this to be true, I figured I should blog about it.

How to easily move a replicated database in SQL Server
Produced: 10/11/2013 14:48:00
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.

Normally it’s a simple task of “detach -> move file -> attach” and you’re done. But of course this doesn’t work with published databases as you get the error:

Agent History Cleanup - Distribution Transaction Log Full
Produced: 13/10/2013 20:45:00
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.

Merge Replication Records Stuck with genStatus4
Produced: 06/10/2013 14:40:00
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.

What I witnessed was that replication suddenly slowed down on a machine and the CPU started to rise to uncomfortable and unusual levels.

SQL Server "Distribution clean up: distribution" Job Failing Large MSRepl_commands
Produced: 29/09/2013 16:04:00
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.

I had a look around on the server in which this job was failing and found that the MSRepl_Commands table was HUGE!!! (This is the table which holds all commands to be replicated).

Identity Management in Merge Replication
Produced: 01/09/2013 11:37:00
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.

Could not find stored procedure dbo.sp_MScheckIsPubOfSub
Produced: 07/07/2013 15:46:00
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…

This was the error…

Configure Distribution
Produced: 23/06/2013 18:25:00
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…

Replication Article Properties
Produced: 16/06/2013 17:32:00
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.

Continuous or Scheduled Replication (my two cents)
Produced: 26/05/2013 14:37:00
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:

How to Subscribe to a Publication
Produced: 19/05/2013 19:50:00
Once you have created your publication you will need to subscribe to it (otherwise it’s pretty pointless really).

You can start the Subscription wizard from either the Publisher or the Subscriber by clicking as follows:

How to Create a Publication
Produced: 05/05/2013 16:27:00
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.

Push vs Pull Subscription
Produced: 14/04/2013 20:14:00
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.