次の方法で共有

Tuning scheduled/delivery-related processes in a SQL Server Reporting Services Reporting Farm

  • [アーティクル]

[Thanks to Robert Bruckner, Daniel Reib, Tudor Trufinescu and Mike Schetterer!]

Using rsConfig.exe and rsActivate.exe, you can easily set up a reporting farm in which multiple instances of the Report Server web and windows service use the same ReportServer database. Many people wonder exactly how we deal with scheduled tasks like snapshot generation, subscription delivery, etc. in this scenario Does one poor node get stuck with all this activity? Do we load balance? Can I tune which server does what?

Here’s the skinny.

Our make-believe report farm and work load (“Before”)

First, here’s a fairly basic scenario to use as the basis of discussion: Let’s say you have a three-node reporting farm set up like this:

Node1: Hosts the Report Server Windows and Web service, plus SQL Server itself (and therefore the ReportServer database)

Node2: Hosts the Report Server Windows and Web service, uses the ReportServer database on Node1

Node3: Same as Node2.

You create one subscription on a report which uses fileshare delivery and put this subscription on a schedule that fires every minute.

You also choose to generate a snapshot for a different report, and put this snapshot update job on a different schedule which also fires once a minute.

What goes where in SSRS?

First, it’s good to understand what’s happening under the covers. When you configured your two schedules in SSRS, you actually ended up creating two SQL Server jobs managed by SQL Server Agent. When it comes time to do work (every minute in our case), each SQLAgent job executes a stored procedure which drops a record into a table which lives in the ReportServer database on node1 – The table name is Event. If our two jobs execute at about the same time, we’ll see two records in the Event table. One record will have an EventType of TimedSubscription (send the subscription!) and the other will have an EventType of ReportExecutionUpdateSchedule (generate my snapshot!).

We also have a table named Notification in the ReportServer database, and this is where a list of subscriptions that need to be delivered live temporarily. Since one of the two records in the Event table is related to sending a subscription, you’ll quickly notice that the record in question (the one with an EventType of TimedSubscription) gets removed from ReportServer..Events. Then, a “matching” record appears in the Notification.

So, we now have a one record in ReportServer..Event (generate my snapshot!), and one record in ReportServer..Notification (send my subscription!). By default, BOTH of these tables are regularly polled by the Windows ReportServer service on all three nodes. The basic logic is as follows:

· Do I see a record in the Events (Notification) table?

· (Yeah, I do…) Does my machine currently have enough resources (CPU, free threads, etc) for me to handle this request fairly quickly?

· (Yes, my machine has enough bandwidth…) I’m going to handle this task and then delete the associated record from the Events (Notification) table.

So who does the work?

If you don’t make modifications to the rsReportServer.config file on each node, it’s pretty much dumb luck – Whichever of the Windows ReportServer services happens to poll the Events/Notification table first and sees available work will DO the work.

How to I change the default behavior?

There are three settings of interest: PollingInterval, IsNotificationService, and IsEventService

PollingInterval

Specifies the interval, in seconds, between polls of the event table by the report server.

0 to max integer.

The default is 10.

IsNotificationService

Specifies whether the report server dedicates a thread to polling the notification table in the report server database to determine if there are pending notifications.

True (default)

False

IsEventService

Specifies whether service processes events in the event queue.

True (default)

False

 

For each of your three nodes, IsEventService specifies whether the Windows ReportServer service will poll the Events table…If you don’t poll the Events table, you’ll never do stuff like process snapshots. So, you could set this sucker to False if you don’t want a particular node to process / update Snapshots.

 

IsNotificationService is very similar – you use it to tell the windows service whether or not to poll the Notification table. If you don’t poll this table, you won’t be sending out any subscriptions.

 

Finally, PollingInterval determines how often your windows service will actually DO the polling…the more often you poll, the more likely you’ll be “first in line” to do work.

 

Let’s make some changes (“After”)

 

NOTE: The changes below are in no way Microsoft best practices or recommendations…they are simply changes

 

So, I think about things for a while and decide that since SQL is running on Node1, I might not want it to doing lots of delivery related work. I therefore bump up the PollingInterval to 30 from 10. I leave IsNotificationService and IsEventService alone (defaulting to True). The decision to go with a value of 30 is completely arbitrary -- we're just going to be hitting the Events and Notification tables much less often...

 

For no particular reason, I decide that I want Node2 *not* to process subscriptions, so I set IsNotificationService to False on this box.

 

Finally, I set IsEventService to False on Node3, so that all it does is process subscriptions.

 

Remember that you’ll need to bounce the reportserver service on each of the three nodes for these changes to take effect. Once complete, you’ll have Node2 doing snapshots, Node3 doing subscriptions, and (hopefully!) Node1 dealing with snapshot and subscription “overflow” that the other nodes don’t get to quickly enough.

 

Clear as mud? :)

Comments