I'd like to determine an optimal setup for SQL Server Reporting Services to use as the integrated reporting tool for SaaS web-based applications. All reports are pre-defined, so users pick a report and enter some parameters in a web-based front end. Parameters are then passed to the report, which pulls the data, formats the results, and returns a PDF or Excel file to the web application.
While we have existing SQL Servers supporting our web apps, none have Reporting Services installed. Options include:
1) add Reporting Services to an existing SQL Server instance
2) create a new instance on a physical server that already runs SQL Server and use that new SQL instance for Reporting Services
3) have a separate physical server for Reporting Services
While #3 is optimal, it also costs an additional SQL Server license, which is not cheap. I don't know if I gain much with option #2, other than some stability. Option #1 may be the path of least resistance, but I understand that there's some considerable overhead caused by SSRS and I don't want too much of a performance hit.
Anyone need to do something like this? Anecdotes and opinions welcome.