Alright, I’m not famous, even though I share the name of a famous Jazz pianist. So, obviously I am NOT talking about that kind of agent. I’m actually referring to the distribution agent for transactional replication.

If you have worked with replication for any amount of time you will find that one of the more frustrating things is finding out what job is the distribution agent for a specific publication. My experience with this may be more painful than most due to working with large numbers of publications but I have to believe (or like to believe anyway) that I am not alone in this. Nobody likes to have pain alone but would rather that others suffer with them, right? Well, in this case wrong, hence this post in which I hope to provide a little overview of the distribution agent and then share a script which I think will ease that pain in the future.

Now someone is undoubtedly saying at this point, it’s easy to get the distribution agent information, all you have to do is right click on the Subscription in Replication Monitor, select View Details, select the Action drop down menu and then select Distribution Agent Job Properties.

While this is true, it’s not always that simple in that this is ONLY true when you are using PUSH subscriptions. Try doing that with a PULL subscription and you will find yourself without sections of hair.

Of course you could also take a gander at the jobs on your distributor and pull the proper one out there. If you only have a limited amount of replication that too might be sufficient. Try that when you have hundreds of publications and many distribution agents for a given database and…. Well, I think you get the point.

AND, if I can’t convince you based on those arguments then at least let me say that the script is cooler, and far more elegant AND it gets you exposed to the metadata tables where all the secrets of replication lie. All of which are good things.

So, the script uses two tables, both in the distribution database, Msdistribution_agents and Mspublications. The Msdistribution_agents table will potentially hold as many records as there are publications UNLESS you are using shared distribution agents which I will be covering in my next post in which case you will see a record that will show “ALL” for the publication.

The Mspublications table will contain one record for each publication for a publisher. Please note that you can have multiple publishers sharing one distributor / distribution database so you will need to keep that in mind as you query that table. Querying against either of these tables is safe and they will be relatively small, even when you have a lot of publications.

One note and word of caution, replication tables are open for updates, inserts and deletes in many (if not all) cases so, please don’t try updating stuff directly unless you have direct recommendation from MS Product Support Services, PSS.

The script included here will pull the distribution agent name (job name), associated publication name, publication database, job identifier and job step identifier.

USE distribution; 
GO
 
SELECTmda.name AS distribution_agent,
mp.publication AS publication_name,
mda.publisher_db AS publisher_database, 
job_id, 
job_step_uid
FROMMSdistribution_agents mda INNER JOIN MSpublications mp
ONmda.publication = CASE 
WHEN mp.independent_agent = 1 THEN mp.publication
ELSE 'ALL'
END
AND mda.publisher_db = mp.publisher_db
WHEREmda.publisher_db = 'YourPubDB'
AND mda.name LIKE '%YourPubServer%'
ORDER BY mda.name, 
mp.publication

Comments, suggestions and feedback is greatly appreciated.