August 23, 2013 at 4:57 pm
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
January 8, 2014 at 6:56 pm
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).
January 9, 2014 at 2:15 am
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. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply