Results 1 to 3 of 3

Thread: Transactional Replication - multiple databases

  1. #1
    Join Date
    Oct 2017

    Transactional Replication - multiple databases

    I have to setup Push Transactional replication between 2 servers (SQL Server), and the Publisher will also act as the Distributor.
    The publisher has about 50 databases that must all be replicated. The Subscriber will then be a readable secondary for reporting.
    My question is: For the configuration and after-setup maintenance, what is the best option for the Publication?
    -- Should I create One Publication with all the databases and all objects?
    -- OR Should I create a separate Publication for each database?
    -- Based on the number of Publications, should I have 1 or multiple distribution databases?

    What is the best option, specifically to make maintenance and administration of Replication easier?

  2. #2
    Join Date
    Nov 2002
    New Jersey, USA
    Create separate publications for each database. It is easier to maintain. Because anytime you make schema changes you only have to deal with that particular publication.
    I am not sure you can have more than one distribution database on the same server. Is data change volume high to require another distribution database?

  3. #3
    Join Date
    Oct 2017
    Thank you for your reply
    It is quite a busy server with many data changing transactions. Perhaps I should just start off with a single distribution DB...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts