Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Should I chain replication, or use multiple subscribers of the same publication? Expand / Collapse
Posted Friday, August 23, 2013 4:57 PM

SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 12, 2015 5:55 PM
Points: 47, Visits: 406

My application, built using SQL Server 2008 R2 Standard, currently has two databases, "OLTP" and "Reporting". These two databases are hosted on separate servers (VMs, actually).

I use transactional replication to replicate a subset of the app's data from OLTP to Reporting, with a pull subscription and the agent running continuously. The SQL Server instance which hosts the OLTP database acts as its own distributor. There's nothing "fancy" about the publication - no articles are filtered, no data is changed in Reporting other than via replication, etc.

This has been working just fine. (Although I'm not positive I'm better off with a pull subscription than a push subscription...)

Now, however, we are going to be adding a third database, which will allow clients of my application to run their own ad-hoc queries. Call it "AdHocReporting". It will contain a subset of the data already being replicated to the Reporting database, and will also be hosted in a separate SQL Server instance on a new/separate VM. I'm trying to figure out whether I should (a) set up both Reporting and AdHocReporting as subscribers to the publication from OLTP, or (b) set the instance hosting the Reporting database up as a publisher too, and create a publication from Reporting, with AdHocReporting as its subscriber.

Does it matter that I do not intend to replicate all of the articles to AdHocReporting that are being replicated to Reporting? Like, if I set AdHocReporting up as a subscriber to the existing publication on OLTP, but it doesn't contain a table for every article in the publication, will that cause problems?

Which arrangement is most likely to become a performance drag on the OLTP database and/or its SQL Server?

What other kinds of issues should I be considering as I make this decision? What gotchas should I be aware of, both about having multiple subscribers and about having "chained" replication? Any advice, success/horror stories, etc are very welcome.

Post #1488066
Posted Wednesday, January 8, 2014 6:56 PM


Group: General Forum Members
Last Login: Wednesday, May 14, 2014 6:39 PM
Points: 21, Visits: 93
I believe that transactional replication is the only mode that allows peer to peer replication for chaining, but I'm not sure that would give what you are wanting. I don't think it would allow you to have a separate publication on the initial subscriber with different article filters, just a continuation of the same publication (so the same). I could be wrong though as most of my experience is with merge replication (which does not unfortunately support peer to peer).
Post #1529184
Posted Thursday, January 9, 2014 2:15 AM

SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 12, 2015 5:55 PM
Points: 47, Visits: 406
Thanks for the reply, Michael.

I had trouble getting any feedback or advice regarding this topic on any of the forums I read, so eventually I just tried some things to see what would happen (using QA systems first, of course).

After a few successful experiments in the QA environment, I set up the server which hosts the Reporting database as its own distributor, and used transactional replication to replicate the subset of the data from there to the AdHocReporting database, which is on a different server/VM. I set that up as a pull subscription as well.

So data flows like OLTP -> Reporting -> AdHocReporting (all one way).

That's been working fine.

The only hitch (for me) was that I need to transform the data in various small ways as it is replicated from Reporting to AdHocReporting, so I needed to replicate using stored procedures instead of straight SQL statements, which I hadn't done before. But a couple of hours in BOL and some more experimentation sorted that out nicely. :)
Post #1529249
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse