I manage a moderately complicated topology that includes one-way transactional replication with push subscriptions to and from several database instances. Due to my day-to-day tasks I know this topology pretty well and often I simply know what my publications are, what articles they include, and what servers are subscribers. This is fine and dandy for as long as I’m at the company but when the time comes for me to transition what does that leave my successor with?
The first thing I should do is document the topology and publicize the location of that document so that it can be referenced quickly. But let’s say I’m one of those lazy DBAs out there that finds documentation tedious and never gets around to it? What will my successor do when all the indexes that he just put on a table disappeared a few days later and he realizes that it might be a subscription article for transactional replication that re-initialized?
When I first thought of this blog post I set out to produce a single query that could be run on the subscriber which would confirm that this table was involved in replication and display the publisher and publication that it was subscribed to. I dived into BOL and found the documentation on Replication Tables. Scrolling down to the subscription database you will notice a bunch of tables related to merge replication. Naturally, merge replication is easier to identify because it is a two-way technology so much more meta data is required at the subscriber. But, my goal was for transactional replication and this is where I began to doubt myself. I knew there had to be a way to accomplish my goal but this list of tables didn’t seem to bear any fruit.
Funny enough, I was staring at my SSMS window and decided to just type .dbo.MSrepl. This triggered my Red-Gate SQL Prompt to populate an available table list and there is was, MSreplication_objects. This table is used to track the objects such as the stored procedures that are used for INSERT, UPDATE, and DELETE commands per article. Looking back at MSDN you will notice that this table is listed under the distribution database but apparently is available in the subscriber database also. In my case these were push subscriptions and I wouldn’t know where the distributor is without knowledge of the publisher which I didn’t have.
Without further ado; here is the query that I produced. You simply change the subscribing database name and pass in the table name into the WHERE clause and you will find your publisher, publication, and publisher database.
[sql]SELECT DISTINCT [publisher], [publisher_db], [publication]
FROM [].[dbo].[MSreplication_objects]
WHERE [article] = ‘MyTableName’;[/sql]
Leave a Reply