Sql-server – Reporting services scheduled reports are clogging up sql job history log

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.

1. 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.

2. 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.

3. 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.

To purely answer your question:

If you set up two different VMs one running the SQL Server and the other one hosting the SQL Server Reporting Services, you would be charged twice the per-hour price of a Virtual Machine, but only once for your the SQL Server License.

Best Practice

I guess the "best practice" answer would be to rely on SQL Azure and SQL Azure Reporting Services, since they provide licensing, installation, maintenance, redundancy, scalability and security built-in.

But since this doesn't seem to be an option to you, here is what I would do based on my experience:

• Put the database that hosts the data the reports are built on in SQL Azure. There you can restrict access to the database to only other Windows Azure components (Websites, Hosted Services, VMs) or a given IP-Range
• Setup the SQL Server Reporting Services on a Virtual Machine (not a Hosted Service), because Hosted Services lack data persistence
• Host your web application on Azure Websites (they are for free at the moment). You can later switch to Hosted Services if you need more performance.

Hope this answer helps.