Should I chain replication, or use multiple subscribers of the same publication?

• j-squared

  SSC-Addicted

  Points: 407

  More actions

  August 20, 2013 at 5:04 pm

  #395333

  Hi,

  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.

  Thanks,

  -Jason