How to look up instance names and publisher databases

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

How to look up instance names and publisher databases

Postby SQLAdminCJ » Fri Jun 22, 2012 9:36 pm

Hey all,

I am writing a program using RedGate SQL Compare that syncs data between nodes in a transactional replication topology. In cases where there are more than 2 nodes, I need an automated way of creating pairings of SQL instance/database name so that I can sync all the links in the tree.

I believe this information is stored in the distribution database. I have found tables that contain the names of the publication database and subscription database (MSsubscriptions).

However, although publisher_id and subscriber_id are listed as fields, I don't see how I can use that to get the names of the SQL instances.

The MSsubscriber_info in the distribution database contains the SQL instance names of the publishers, but there is no ID column.

Does anyone know how I can obtain the publisher matched with the publication database? I would appreciate any advice anyone can give. Thanks.
SQLAdminCJ
 
Posts: 10
Joined: Wed Dec 28, 2011 2:03 am

Postby SQLAdminCJ » Fri Jun 22, 2012 10:52 pm

Someone helped me find the answer to this. The server names are listed in master.sys.servers.
SQLAdminCJ
 
Posts: 10
Joined: Wed Dec 28, 2011 2:03 am

Postby andy.campbell.smith » Mon Jun 25, 2012 12:53 pm

Glad to hear you found the answer to your question. Let us know if there's ever anything else you have a question about!
Andy Campbell Smith

Red Gate Technical Support Engineer
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests