March 21, 2018 at 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.
March 22, 2018 at 2:53 am
TrailRunner - Wednesday, March 21, 2018 7:41 PMHi,
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.
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
March 22, 2018 at 8:35 am
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).
BartL
Replication Blog
March 22, 2018 at 10:46 am
2. Two subscribers to one publication
March 22, 2018 at 11:03 am
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