4

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.

ddavis
  • 75
  • 1
  • 4

4 Answers4

1

You didn't indicate what version of SQL Server. Keep in mind SSRS 2005 requires IIS installed on the box as well. SSRS 2008 does not. It uses HTTP.SYS natively. The optimal answer is #3, however, it really depends on the existing load on the physical server as well as the type of reports you would be running. #1/#2 are essentially the same option with respect to performance.

The truth is, without knowing the performance characteristics, no one here can tell you the best option. Your best path is to get a good idea of how your SQL Servers are performing today, to set up a small dev SSRS environment and profile it as you test the reports, and then make the decision for there. If you're not sure where to start on getting a performance profile on your SQL Servers, allow me to recommend:

Brent Ozar's posts on Performance Tuning

K. Brian Kelley
  • 9,004
  • 31
  • 33
  • Existing boxes have SQL 2005. The new server would be SQL 2008, so as to avoid the whole need for IIS (I like to keep a low attack surface for any web-facing servers). – ddavis Aug 07 '09 at 14:37
0

We are going the #1 route until we have some free licenses for a dedicated rs/as/mirroring server. I have capped iis memory usage for the app pool. With that and reasonable timeouts, we're hoping to reduce impact on the db engine.

I wouldn't allow any adhoc (report builder) on the #1 system.

I'd really push for #3. You can also use it for db mirroring of critcal databases or running ssis.

We served 3600 reports last week in 4 days - memory sits around 300-1500MB.

Sam
  • 1,990
  • 1
  • 14
  • 21
0

Considerable overhead, I think, is in the eye of the beholder. For instance, one of the server's I administer is a SQL Server 2000 Data Warehouse with an instance for SQL Server 2005 to hold Reporting Services. I would hardly say that SSRS has considerable overhead for us, but that has alot to do with how we use it. We're consolidating the two into a single SQL Server 2008 default instance for numerous reasons, mostly dealing with how small a footprint SSRS is for us relative to the data warehouse. We have less than 50 reports, with the vast majority of them being scheduled. We also don't do any caching or snapshots. So, in our case, consolidating is definitely the way to go.

Tim Benninghoff
  • 51
  • 2
  • 4
0

We use ESX virtual servers with datacentre licences and SQL CPU licences so we dont have an issue with the number of SQL instances we host so my answer is...

Keep reporting services on a separate server. The main reason we do this is that SSRS tends to be the service the upgrade to the "latest and greatest" version the first to get the maximum benefit from the new functionality (of SSRS and Visual Studio 2008).

Our SSRS is SQL 2008 - most databases are still SQL2000 / and 2005

It also helps if you can keep the data on a separate server. We found that a busy SSRS server with the database on the same system slowed down the web browsing for other users on the system. This was less so when we moved the data onto a dedicated "report data" server.

Guy
  • 2,658
  • 2
  • 20
  • 24