April 21, 2010 at 10:14 am
Hi All,
Im trying to build a query which will give me a snapshot of our replicated environment showing things such as the publisher server, published database, articles, job schedules, transaction type, subscriber server and subscriber db plus a few other bits like identity range management values etc.
Im really struggling when trying to find where the subscriber server name is stored in the system tables. Im wondering if i need to query the subscription database and then somehow link back to the distributor on the distribution server.
Would anyone be able to assists?
Currently i have this.... Still very basic at the moment 😛
select P.Publication, P.Publisher_db, MS.Subscriber as MergeSubscriber,A.Article, S.subscriber_db
from MSpublications P
left join MSmerge_subscriptions MS on MS.publication_id = P.publication_id
left join MSarticles A on A.publication_id = P.publication_id
left join MSsubscriptions S on S.publication_id = P.publication_id and S.article_id = A.Article_ID and S.subscriber_id >= 0
order by P.Publication
April 21, 2010 at 1:28 pm
You need to query the distribution database and the mssubscriber_info table.
April 21, 2010 at 4:26 pm
Toby White (4/21/2010)
You need to query the distribution database and the mssubscriber_info table.
Thanks Toby. I did see that table, however there doesnt seem to be a lot to join on from the publications table or anything else for that matter?
April 22, 2010 at 7:25 am
I think what you want is information from the system tables that are on your publishing or subscribing database depending on your replication architecture. Mine are on my publishing databases and include the following tables:
dbo.sysarticlecolumns
dbo.sysarticles
dbo.sysarticleupdates
dbo.syspublications
dbo.sysreplservers
dbo.sysschemaarticles
dbo.syssubscriptions
dbo.systransschemas
April 22, 2010 at 8:30 am
Toby White (4/22/2010)
I think what you want is information from the system tables that are on your publishing or subscribing database depending on your replication architecture. Mine are on my publishing databases and include the following tables:dbo.sysarticlecolumns
dbo.sysarticles
dbo.sysarticleupdates
dbo.syspublications
dbo.sysreplservers
dbo.sysschemaarticles
dbo.syssubscriptions
dbo.systransschemas
Yeah i tend to agree with you, ive spent a few more hours looking at it today and the information does seem to be spread across a mixture of the published database, the distribution database and the subscription database. Its going to be one hell of a query this one but should give a really good snapshot of the replication model at any point in time.
If i ever get round to finishing it ill post it on here 🙂
April 22, 2010 at 8:49 am
Cool, I could use a script like that myself. One of the tricky things would be going accross servers, but I guess you would use linked servers for that. I have gotten myself in tight spots before when I've needed to restore production to a different server and recreate replication. I think something like what you are doing would help for situations like that.
May 1, 2015 at 12:36 am
It seems the following script is doing what you are trying to achieve.
select distinct pub.name [Publisher], P.Publisher_db, p.[Publication], s.subscriber_id, sub.name [Subscriber], [Subscriber_db]
from
master.sys.servers pub
join distribution.dbo.MSsubscriptions s
on s.publisher_id = pub.server_id
join master.sys.servers sub
on s.subscriber_id = sub.server_id
join distribution.dbo.MSpublications p
on s.[Publisher_id]=p.[Publisher_id] and s.[Publication_id]=p.[Publication_id]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply