If you have lost the historical data (i.e. it gets overwritten rather than stored as a transaction history) then you can't reconstruct the historical state - full stop. However, there are a few approaches to dealing with this.
Build an automated process that queries the system through SSRS or some other mechanism and saves the historical reports to an archive somewhere. SSRS exports a web service API that lets you run reports (paramaterised if necessary) and then save the output to a file. This can be operated by anything capable of consuming it.
Build an ETL process that takes a snapshot of the data and compares it with the previous snapshot position. Where changes are detected they can be written out to a historical table. If you have the option of putting triggers on the source database then you can make triggers that write out the audit logging information instead.
If you need something more elaborate then you could build a data mart - do a google search on 'type 2 slowly changing dimensions' for some ideas on how to implement this. You can use a change capture mechanism like that described in (2) to source the data as necessary. Either you can store the transaction history and reconstruct the status in the reports, or you can make a periodic snapshot of the data with its status at that point.
Note that Standard Edition doesn't support changed data capture.
Suggest you not to remove people but disable their login or just revoke access.
Then after a full business cycle, if no one complains then remove those users.
Is there a way to find out who has accessed certain reports during past month?
Below is the query that will give the report you need. There are more report queries available at this link :
Note: Adjust the query as per your need.
– Execution Log:
– For debugging (remove next 4 lines after pasting to dataset) –
DECLARE @DateFrom Date
DECLARE @DateTo Date
SET @DateFrom = ’2008-01-01′
SET @DateTo = ’2008-06-30′
– End debugging script –
SELECT
DATEPART(Hour, TimeStart) AS ReportYear
, DATEPART(Month, TimeStart) AS ReportMonth
, DATEPART(Day, TimeStart) AS ReportDay
, DATEPART(Hour, TimeStart) AS ReportHour
, Type
, COUNT(Name) AS ExecutionCount
, SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
, SUM(TimeProcessing) AS TimeProcessingSum
, SUM(TimeRendering) AS TimeRenderingSum
, SUM(ByteCount) AS ByteCountSum
, SUM([RowCount]) AS RowCountSum
FROM
(
SELECT TimeStart, Catalog.Type, Catalog.Name, TimeDataRetrieval,
TimeProcessing, TimeRendering, ByteCount, [RowCount]
FROM
Catalog INNER JOIN ExecutionLog ON Catalog.ItemID =
ExecutionLog.ReportID LEFT OUTER JOIN
Users ON Catalog.CreatedByID = Users.UserID
WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo
) AS RE
GROUP BY
DATEPART(Hour, TimeStart)
, DATEPART(Month, TimeStart)
, DATEPART(Day, TimeStart)
, DATEPART(Hour, TimeStart)
, Type
ORDER BY
ReportYear
, ReportMonth
, ReportDay
, ReportHour
, Type
Best Answer
Reporting Services can be demanding in terms of memory and CPU, so it often makes sense to separate it from the database server element of SQL Server - obviously this isn't cheap in terms of licencing. One thing to bear in mind is that the metadata for SSRS is stored in a SQL database, but this can be on a different box. Where SSRS gets the business data from depends on where that data resides - it could be anywhere, but it might be a SQL database somewhere.
If your SSRS deployment uses subscriptions to generate reports out-of-hours, and your SQL database is used for other requirements during business hours, then it might make sense to keep them on one box.
Depending on your SQL edition, and your requirements, you can also treat the separate SSRS boxes like webservers, and have them in a high availability Network Load Balanced cluster - you can't cluster SSRS in the same way that you can with the database engine for high availability.
Like anything, try to test it all in a pre-production or Proof Of Concept environment.