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.