nerdexam
Microsoft

70-467 · Question #141

A company runs SQL Server Database Engine and SQL Server Reporting Services (SSRS) in native mode. Reports are based on data that is cached in multiple shared datasets. Source data is purged each…

The correct answer is B. Write a script that calls the flushcache method to clear individual items from the SSRS cache. To ensure SSRS reports never return purged data, the report cache must be programmatically cleared after midnight using the SSRS web service FlushCache method rather than relying on schedules that may be paused.

Design a reporting solution

Question

A company runs SQL Server Database Engine and SQL Server Reporting Services (SSRS) in native mode. Reports are based on data that is cached in multiple shared datasets. Source data is purged each day at midnight for regulatory compliance purposes. The shared datasets may continue to cache data that should not be used in reports. Shared report schedules are often paused during nightly server maintenance windows. Reports must not return purged data. You need to create a fully automated solution to ensure that reports do not deliver purged data. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Options

  • ACreate a shared schedule. Configure the datasets to expire on the shared schedule.
  • BWrite a script that calls the flushcache method to clear individual items from the SSRS cache.
  • CCreate a SQL Server Agent job that uses a Transact-SQL (T-SQL) step to delete the data
  • DRepublish the cached datasets by using SQL Server Data Tools.

How the community answered

(14 responses)
  • A
    14% (2)
  • B
    43% (6)
  • C
    7% (1)
  • D
    36% (5)

Why each option

To ensure SSRS reports never return purged data, the report cache must be programmatically cleared after midnight using the SSRS web service FlushCache method rather than relying on schedules that may be paused.

ACreate a shared schedule. Configure the datasets to expire on the shared schedule.

Shared schedules are explicitly stated to be paused during nightly maintenance windows, making schedule-based cache expiration unreliable for guaranteeing the cache is cleared after the midnight purge.

BWrite a script that calls the flushcache method to clear individual items from the SSRS cache.Correct

The SSRS ReportService2010 web service exposes the FlushCache method, which can be invoked from a script to programmatically clear cached data for specific shared datasets immediately after source data is purged at midnight. This approach is fully automated and does not depend on shared schedules, which the question explicitly states are often paused during nightly maintenance windows. It also targets individual cached items precisely, satisfying the regulatory compliance requirement.

CCreate a SQL Server Agent job that uses a Transact-SQL (T-SQL) step to delete the data

A T-SQL step in a SQL Server Agent job can delete source data but has no mechanism to clear the SSRS report server cache, meaning reports could still return previously cached purged data.

DRepublish the cached datasets by using SQL Server Data Tools.

Republishing datasets via SQL Server Data Tools is a manual developer action, not a fully automated solution, and republishing does not reliably flush existing cached report data on a recurring basis.

Concept tested: SSRS shared dataset cache clearing via FlushCache web service

Source: https://learn.microsoft.com/en-us/sql/reporting-services/report-server-web-service/net-framework/calling-web-service-methods

Topics

#SSRS cache#shared datasets#FlushCache#data purging compliance

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice