How fast is my Transactional replication throughout the day?

If you want to test the speed of your Transactional replication there is a neat functionality called Tracer Tokens. This will tell you how quick is it to get a record from the publisher to the distributor and from there to the subscriber. In the old days you’d have to create a dummy table to do all this but since SQL 2005 it’s out of the box :).

To add a tracer token use the replication monitor and edit the Transactional publication as below:

Basically, a token is written to the transaction log of the publisher. This is treated as a normal replicated command, and passes from the transaction log to the distribution database and then is ‘run’ by the distribution agent.

You’ll see the various latency times recorded for each subscription. If you have a distribution agent set to work on a schedule (i.e. not running continuously) you’ll see the following sort of scenario until the distribution agent runs.

To add one programatically for reporting purposes just roll the following type of script:

DECLARE @myTokenID AS int
EXEC sp_posttracertoken
@publication = ‘YourPubName’,
@tracer_token_id = @myTokenID OUTPUT;
select @myTokenID 

Trapping the TokenID is actually optional but is very useful if you want to check on the resulting latencies for this particular token. So,

EXEC sp_helptracertokenhistory
@publication = ‘YourPubName’,
@tracer_id = -2147483641 

where -2147483641 is the ID of the tracer token.

 

Or just roll your own report based on;

select * from distribution..MStracer_tokens

This way you can create a custom alert so the support team know when there is an issue. There are SQL Agent alerts based on the Log Reader Agent and Distribution Agents which can be configured, but the method above is a little more under our control and can be used for reporting purposes. This way you can add a Tracer Token every 10 minutes and plot performance over the course of a day.

“Keep existing table unchanged” – why would I ever want to use this?

There is an option on the article properties to “keep existing object unchanged”.

This seems a little odd at first. Surely we always drop all subscriber objects when we reinitialize? Well for a particular table we could have something different in mind. A real edge case but there is a scenario this is designed for. It is where we have multiple publishers to a single subscriber. The table is created on the subscriber by the first publisher during initialization and hence has the replication objects are created at the subscriber. Subsequent publishers will send their commands and records there but leave the table in place. This can be used for centralised reporting from several municipal offices to head office. Any ddl changes such as adding a column are fine – we do them on the first publisher (the one which did the initial drop and recreate at the subscriber) so the ddl change goes to the subscriber.

When initializing do I need to drop FKs at the subscriber?

This is an interesting question. In the publication properties there is the option on the snapshot tab to run extra scripts before and after the snapshot is applied. This is the same for both merge and transactional publications (below).

Many DBAs will have scripts which drop all the FKs on the subscriber and readd them after the snapshot is applied so the initialization runs smoothly and we don’t get the following sort of error:

Could not drop object ‘dbo.tCity’ because it is referenced by a FOREIGN KEY constraint“.

However the snapshot generation is different between Transactional and Merge. In Transactional all the FKs are dropped for you and re-added later on. This doesn’t happen for merge. There’s probably a good reason for it but can’t see why they should run differently at present. Anyway, the message is that you don’t need to roll your own logic to deal with subscriber FKs in Transactional but you still do in merge!

Adding an article – do I really want a complete snapshot?

Have you ever noticed that when you add an article to a transactional publication and run the snapshot agent it does a complete snapshot of all tables? This can be a real pain for big publications. Fear not – it is configurable. The setting is visible in the Replication Monitor. Actually 2 settings – shown below. We need both to be False but the observant DBA will notice that the main one of these is greyed out!

 

A little scripting solves it though. We just run the script below before adding a table and all is well – the snapshot agent then just creates the new article.

EXEC sp_changepublication
@publication = ‘pubTestTransactional’,
@property = ‘allow_anonymous’,
@value = ‘false’
GO

EXEC sp_changepublication
@publication = ‘pubTestTransactional’,
@property = ‘immediate_sync’,
@value = ‘false’
GO

Peer-to-Peer Update Conflicts – Be careful with the OriginatorID!!!

In Peer-to-Peer Transactional Replication we can allow for conflict detection and also to continue after a conflict.

Really changes to the data at different nodes should be partitioned so conflicts are not possible but not everyone sets it up this way so there is a rudimentary conflict resolution mechanism in place for us to use.

For an update-update conflict we’ll see mention of it in the conflict viewer in a format like the one below. One node skips the conflict ie preserves its value, while the other applies the update and so overwrites its value.

 

In the case above, both records end up being “Madrid” and we have data in sync. But how was this decided? The point to notice is the numbers above “peer 100” and “peer 1”.

We can see which node has which value when looking at the topology:

..and the number 1 was allocated when we set up the subscriptions :

In the conflict, the row that originated at the node with the highest ID wins. The value of 100 is assigned to the Publisher by default, and as above we left the default of 1 at the first subscriber.

Now – here’s the point – the publisher in this scenario will always beat the 1st subscriber. The second subscriber always wins against the first, the third always beats the second and so on. This is not something we can change afterwards.

So, we need to decide which nodes are the most important before we set this up!