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 Tuesday, August 20, 2013 5:04 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 #1486477
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse