Who’s My Agent?

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
 
SELECT	mda.name AS distribution_agent,
		mp.publication AS publication_name,	
		mda.publisher_db AS publisher_database, 
		job_id, 
		job_step_uid
FROM	MSdistribution_agents mda INNER JOIN MSpublications mp
			ON	mda.publication = CASE 
									WHEN mp.independent_agent = 1 THEN mp.publication
									ELSE 'ALL'
								END
				AND mda.publisher_db = mp.publisher_db
WHERE	mda.publisher_db = 'YourPubDB'
		AND mda.name LIKE '%YourPubServer%'
ORDER BY mda.name, 
		mp.publication

Comments, suggestions and feedback is greatly appreciated.

Posted in Uncategorized | Tagged | Leave a comment

Here it is!

Well, I have been threatening (myself and others) to start a technical blog for quite some time and have finally decided to jump.

Before I go too far I will tell you outright that I am not a super-dynamic communicator and don’t have any intent on really changing that. However, I do hope that I will be able to provide some solid tips and helps along the way in areas of SQL Server that I have gained experience in over the years.

You can expect blogs that will deal mainly with technical tips and scripts which will center around performance tuning, monitoring, replication, high-availability and disaster recovery.

At present my goal is to provide one post a week however, all that will be dependent on workload and family needs during that time.  So, come back and check periodically!

Posted in Uncategorized | Leave a comment