Transactional Replication with one or multiple publications

  • 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.

  • 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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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

  • 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).

  • 2. Two subscribers to one publication

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

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

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