Transactional Replication with one or multiple publications

  • TrailRunner

    SSCommitted

    Points: 1540

    Hi,
    I have a existing transactional replication configured and running: one publication on server A and one subscriber on server B. We use the subscriber as reporting server. As the number of reports increased, we see the need for a second reporting server. Here is the question: which of the following configuration do you recommend and why?

    configuration 1:
    ===========
    Create a new identical publication on server A and a new subscriber on server C.

    configuration 2:
    ===========
    Use existing publiction on server A and create a new second subscriber on server C.

  • ChrisM@Work

    SSC Guru

    Points: 186087

    TrailRunner - Wednesday, March 21, 2018 7:41 PM

    Hi,
    I have a existing transactional replication configured and running: one publication on server A and one subscriber on server B. We use the subscriber as reporting server. As the number of reports increased, we see the need for a second reporting server. Here is the question: which of the following configuration do you recommend and why?

    configuration 1:
    ===========
    Create a new identical publication on server A and a new subscriber on server C.

    configuration 2:
    ===========
    Use existing publiction on server A and create a new second subscriber on server C.

    We have Transactional Replication for a bunch of tables required for reporting. The tables are spread across about a dozen different publications so we can reinitialise any one of them independently from the others. Each takes roughly an hour. Each of those dozen publications has two subscribers, one live, one dev/test.
    Whilst I'm in favour of multiple publications in this configuration, I can't see what benefit you might expect from two identical publications.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Bart Lewandowski

    Ten Centuries

    Points: 1099

    Configuration 2 will be way to go. 1 publication adding second subscriber.
    The problem with Conf 1 is that you are creating new publication with same tables but from Replication point of view same tables will have two entries in sysarticles (as these are in different publications). Which also means you will double size of your distribution database. Each insert or update will create two commands (in msrepl_commands) instead of one (like in Configuration 2 with two subscribers).

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717790

    2. Two subscribers to one publication

  • TrailRunner

    SSCommitted

    Points: 1540

    Fantastic! Thank you all! I appreciate all your quick responses.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply