Home Forums SQL Server 2005 SS2K5 Replication Need to replicate multiple databases (publications) to one central subscriber RE: Need to replicate multiple databases (publications) to one central subscriber

  • Whenever you have a central subscriber topology I recommend changing the default behavior for what to do if the table articles already exist at the subscriber. To do this open the publication's properties, select the articles page, right click on each article and choose set properties of this table article (or choose set properties of all table articles, but be careful as this applies to all articles and may change custom settings you've already set up for other table articles). In the Destination Object section change the property for Action if name is in use to Keep existing object unchanged.

    Setting this property will allow you to apply the snapshot from each publisher as its added to the replication topology. Of course that assumes that you won't generate PK errors (which the OP suggested may happen). One way to avoid this in a central subscriber topology is to put a composite PK on each replicated table which includes a sysname column for the server name (with a default of @@servername). The added benefit is that you can tell where the row originated from when you're looking at the data on the central subscriber.

    Kendal Van Dyke
    http://kendalvandyke.blogspot.com/[/url]