Install reporting services 2005 on a web server

Question

I have a SQL box and a web box; the SQL box is close to capacity. I'd like to install reporting services and thought about installing it on a SQL instance i would install on the web box, and only use that instance for reporting services. Is this a good option? What pitfalls will I have if I try to do this?

Answer 1

It's recommended to not mix IIS and SQL on the same box. Because 0f different access patterns and security at least.

The fact your SQL box is close to capacity should set alarm bells ringing and big red flags waving. The Report Server databases are quite small, and I'd lie awake at night if I thought it would overload my your DB server

SO: Should SQL Server be on the same machine as your IIS installation?

Answer 2

If you just install the Reporting Services (SSRS) web piece this should be fine. SSRS comes in 2 sections, the web piece and the database catalog so you could have a web site run on the web server and the catalog database would live on you normal database server. You will need to manually configure SSRS through the SSRS configuration utility and specify the remote server name during the database set up.

The downside of this is that you would be required to purchase an additional SQL license for the web server, since an SSRS installation counts as a SQL instance, but you should be able to use a standard edition license to for both instances.

I didn't find a lot a great resources but the steps would be like this:

--install just SSRS (and Client tools to debug connection issues) on the web server --run the SSRS configuration tool and configure the app pools, virtual directories --in the same config tool in the database section just specify the remote SQL server and select "create database" and it will create the database for you and apply the permissions needed to the database instance.

One thing I have found to be kind of quirky about the SQL 2005 SSRS config tool is that you have to "apply" changes when it doesn't seem that intuitive, so beware.

Here's an OK link: http://www.databasejournal.com/features/mssql/article.php/3573361/SQL-2005-Reporting-Services-Part-1.htm