If you need to monitor your transactional replication with a custom monitoring service, Microsoft has provided some useful tools to help. Recently I was having trouble reliably monitoring my replication, and then I discovered this page.

http://msdn2.microsoft.com/en-us/library/ms146951.aspx

If you’re using transactional replication there is no better way to monitor its health than using your own tracer token. This is just like inserting a tracer token using the replication monitor utility in SQL Server Management Studio.

First you need to create a connection to the server.

   server = new ServerConnection(sci); 

then create a TransPublication object.

    TransPublication transPublication = new TransPublication(publicationName, publicationDBName, server);

Call LoadProperties of the new object to make sure that all publications and subscriptions are loaded.

   transPublication.LoadProperties(); 

Then post the tracer token and call refresh to send it on its way. You need to save the ID of the token so that you can clean it up later.

   id = transPublication.PostTracerToken();
   transPublication.Refresh(); 

Now that the tracer token is on its way we need to create a publication monitor to look for its return. You have to go through a couple of layers to get to it.

   Microsoft.SqlServer.Replication.ReplicationMonitor monitor =

      new Microsoft.SqlServer.Replication.ReplicationMonitor(server);
   PublisherMonitor pub = monitor.PublisherMonitors[publisherMonitor];
   PublicationMonitor publicationMonitor = pub.PublicationMonitors[publicationDBName, publicationName];

Now we need to enumerate all of the tokens in the publication. You must call LoadProperties to refresh this list.

   publicationMonitor.LoadProperties();
   ArrayList tokens = publicationMonitor.EnumTracerTokens(); 

You can cast the items in the array list to a TracerToken type to find the token we sent with the ID we saved earlier.

   TracerToken token = null;
   foreach (TracerToken t in tokens)
   {
      if (t.TracerTokenId == tokenID)
      {
         token = t;
         break;
      }
   } 

Now that we have our token we need to enumerate the tracer token history with this call.

   DataSet tth = publicationMonitor.EnumTracerTokenHistory(token.TracerTokenId); 

This returns a dataset that you need to parse through to get the data we are interested in. These include distributor latency, subscriber latency, and overall latency. If these values are blank, then the token has not returned yet. You need to then enumerate the tokens and check again. When the token has returned you then need to cleanup. A simple call will do this for us.

   publicationMonitor.CleanUpTracerTokenHistory(tokenID);

If any part of the replication fails for any reason, the token will fail to return and you know you have a problem.