none
Can someone confirm or clarify

    Question

  • We are having discussions on enabling transactional replication on multiple database instances.  We are questioning whether using a single distributor database server with a single distribution database is possible OR if a distribution database is required per published database instance  (a single distributor database server, but many distribution databases).

     

    If either may be done, is there a best practice or recommendation on the two?

     

    Thanks.

    Friday, January 25, 2008 2:40 PM

Answers

  • I'm assuming when you say multiple database instances, you mean multiple server instances.

     

    The general rule for distributor\publisher relationship is,

    - A distributor can host multiple publishers and multiple distribution databases

    - A publisher can be associated with only one distributor and one distribution database on that distributor.

     

    To answer you question:

    Yes it is possible for multiple publishers to share a single distributor and a distribution database.

     

    The decision to use one distributor per publisher vs. a shared distributor really depends on your topology and workload.

    - Are you going to have push or pull subscriptions?

    - How many subscriptions do you have across all the publishers?

      If it is push, then that determines the number of agents that will run on a single distributor, if there are a lot of agents (i  

      mean 100 or more) due to lot of subscriptions then it would make sense to have multiple distributors or even

      one distributor per publisher (you can create a local distributor for each publisher)

     

    If you are going to share a distributor across multiple publishers it is generally a good idea to have one distribution database per publisher to distribute the transaction load.

     

     

     

    Friday, January 25, 2008 6:47 PM
    Moderator
  • for 67 articles i'll go with one distribution database.

     

    as your enterprise grows, you may benefit from multiple distribution dbs, but at this point, probably not -- unless those 2 in the one publication are extremely volatile. also, it should be noted that to get real performance gain from multiple distribution dbs, they should be on seperate physical disks, else u give back much of the potential gain.

     

    Saturday, January 26, 2008 6:24 AM

All replies

  • I'm assuming when you say multiple database instances, you mean multiple server instances.

     

    The general rule for distributor\publisher relationship is,

    - A distributor can host multiple publishers and multiple distribution databases

    - A publisher can be associated with only one distributor and one distribution database on that distributor.

     

    To answer you question:

    Yes it is possible for multiple publishers to share a single distributor and a distribution database.

     

    The decision to use one distributor per publisher vs. a shared distributor really depends on your topology and workload.

    - Are you going to have push or pull subscriptions?

    - How many subscriptions do you have across all the publishers?

      If it is push, then that determines the number of agents that will run on a single distributor, if there are a lot of agents (i  

      mean 100 or more) due to lot of subscriptions then it would make sense to have multiple distributors or even

      one distributor per publisher (you can create a local distributor for each publisher)

     

    If you are going to share a distributor across multiple publishers it is generally a good idea to have one distribution database per publisher to distribute the transaction load.

     

     

     

    Friday, January 25, 2008 6:47 PM
    Moderator
  • Yes, multiple server instances.

     

    We will have pull subscriptions using Informatica PowerExchange.

     

    We expect to have 65 tables from one instance (though this may grow by a small margin over time) and only 2 tables from another instance.

    Friday, January 25, 2008 8:31 PM
  • for 67 articles i'll go with one distribution database.

     

    as your enterprise grows, you may benefit from multiple distribution dbs, but at this point, probably not -- unless those 2 in the one publication are extremely volatile. also, it should be noted that to get real performance gain from multiple distribution dbs, they should be on seperate physical disks, else u give back much of the potential gain.

     

    Saturday, January 26, 2008 6:24 AM