1

We are currently using transactional replication in SQL Server 2008 to keep a secondary reporting server synchronized with a primary database server. This has been working weel and keeps some of the load off the primary server. Would it be possible to scale this solution to multiple reporting servers? We're expecting an increased load of read-only queries and it would be nice to be able to add reporting servers as needed.

The general idea was the following:

  1. Each reporting server would use a "pull" subscription to get the data from the primary database publication. These reporting databases could be a couple of minutes behind the primary server without it being an issue.
  2. The reporting servers would be NLB'd together. All read-only queries would be directed to the NLB which should spread the load across the servers.
Improve this question
1
  • Unlimited scaling? NOTHING allows that. People fight even for a lot less (see: facebook servers, google farms).
    – TomTom
    Jul 28, 2013 at 18:07

3 Answers 3

Reset to default
1

Sounds like it would work, to me. As long as you're only doing queries to the NLB name. However, I would take a long deep look into the MS SQL deployment and architecture documentation to see if there's anything that says "build it this way, taking care that you do X and don't do Y" or "It won't work at all because of Frob."

Improve this answer
2
  • Yes, we would only be doing read-only queries to the NLB so there's no issue with data getting out of sync through replication conflicts. I've gone down that road before!
    – John Keranos
    Jun 21, 2010 at 17:00
  • Then it definitely would work.
    – TomTom
    Oct 30, 2013 at 9:17
0

Wouldn't it be a lot better to have multiple servers running Reporting Services, all of them querying a single Database Engine server?

There's no need at all for RS to use a DE on the same machine.

About NLBing Reporting Services: yes, you can, but it has some caveats. See http://technet.microsoft.com/en-us/library/cc281307.aspx.

Improve this answer
2
  • 1
    The problem with that approach is that you would still have a single database server taking the brunt of the load. It would scale the rendering of the reports but that's a minor problem compared to the I/O and CPU contention on the primary database server.
    – John Keranos
    Jun 21, 2010 at 16:57
  • Oh, well, I suppose your approach could work. There's no obvious reason for it not to...
    – Massimo
    Jun 21, 2010 at 17:12
0

The usual bottleneck is the distributor. Make sure you do not have the publisher act as its own distributor, with many ('unlimited') scaling the subscribers load on the distributor becomes quite significant. One solution is to stage the distribution, have one of the subscribers (or more) act as a publisher/distributor too. This way more subscribers can be added as subscribers to this second-hand publication without adding further load on the original distributor.

But given the caching capabilities of Reporting Services and extensive built-in capabilities for scale out (see Planning for Scale-Out Deployment), one has to wonder whether such a replication topology is truly needed.

Improve this answer

You must log in to answer this question.