2 Tiered SQL Report Server Installation

Recently I have been helping out one of my customers with SQL Reporting Server 2005. It all started with my customer wanting to install SQL Reporting Server on a failover cluster. SQL Server Reporting services 2005 is not cluster aware. Meaning you don’t want to install both SQL Server Report Services and the reporting services databases on each nodes in a failover cluster. I also found was there was a quite a bit of confusion amongst some of the other people whom I knew. So I decide to try out each deployment type myself and publish my experience.

Microsoft in its Deployment Topology documentation mention a few deployment types

Single Server Deployment  – Here all the report server components including database are on the same box. I will not deal with this as it is pretty straight forward

Standard Server Deployment – This is the focus for this post. Here we install Report server on one box and Report Server Database another

Standard Scale-Out Server Deployment – Here we will have 2 Report server machine in a load balance configuration  and Report Server database will be on a separate machine

Standard Scale-Out Server Deployment  With Database on Failover Cluster – Here deployment will be same as Standard Scale-Out Server Deployment  but the Report Server Database will be on a failover cluster.

There is also Advanced Scale-Out Server Deployment, If I have enough time I will discuss this as well.

Standard Server Deployment

image

The picture is pretty self explanatory, so I will just get to the details on how I went about to achieve this.

Environment

In my environment, I use Virtual PC to create these machine and test them out.

Agni-Ssrs-Web machine, which is my Report server running windows 2003

AgniSql machine, which is my database server running windows 2003, SQL server 2005 enterprise edition

My Active Directory Domain is AgniNet.local

I have a AgniNet domain user sqldba for whom I have granted admin privileges on both the boxes

I also have AgniNet\RprtService user – just an ordinary domain user. I will use this user to run my Report Services

Deployment

On my AgniSql box, I logged in as sqldba user and Installed only SQL database server named instance “sqlinst2”. I did not install SSAS, SSRS, SSIS  or another thing Just database server

On my Agni-ssrs-web all I did was enable IIS before starting Report server installation

One thing I must admit I hate to type and write long sentences, so I will try to put more picture and type less.

Logged in as sqldba on to Agni-ssrs-web box, started SQL server installation

Report Server Installation

image

image

image

This took a while for me, (I doing this on 2 virtual pc’s), so please wait.

image

image

image

image

image

The next step is to configure Report Server and Create Report server Databases on AgniSql Database box

Configuring Report server

Click Start –> All Programs, –> Microsoft SQL Server 2005 –> Configuration Tools, click SQL Server Surface Area Configuration

image

Click In Surface Area Configuration for Services and Connections, verify that the Report Server Windows service is running.

image

Back in SQL server Surface Area Configuration, Click Surface area Configuration for Features

verify that Scheduled Events and Report Delivery is enabled. Verify that HTTP and Web Service Requests is enabled. Verify that Windows integrated security is enabled.

image

image

When I click windows integration Security I get error “Argument value was not passed for the argument Reporting service URL”

image

I just ignored it and went forward

Then I started Report server Configuration on Agni-ssrs-web box

image

Clicked Connect

image

I probably should have configure Report Server Virtual Directory (VD), then Report Manager Virtual Directory, then web service identity, but I attacked web service Identity first, got a small error setting web service Identity, that was because I had to have created the VD’s first. But it does not matter you can go back and create VD’s and it will just work fine.  I will just write what I did

On the web service Identity, I clicked new for creating a new application pool for the Report server

image

image

image

Selected New again for Report Manager Application Pool

image

Clicked Apply got the error, I mentioned earlier

image

Clicking on the link got “ReportServicesConfigUI.WMIProviderException: A Virtual Directory must first be created…”

image

So I am off to create virtual directory, I click Report Server Virtual Directory, click new

image

Click ok

image

Click Report Manager virtual directory

image

image

Back in web service identity. I see ASP.Net Service Account as got NT Authority\NetworkService

image

Database Setup

Open the Database Setup page, I have created AgniSql\SQLINSt2 instance. There are no report database in this instance.

image

Click new in database name field, I changed server name from AgniSql to AgniSql\sqlInst2

image

image

Database successfully created. Changed the server name from AgniSql to AgniSql\Inst2 and clicked apply

image

image

image

Looks like after the error happened it retried and went thru successful

I left the encryption keys thing as is

Verify reporting server installation

image

The 2 databases were created, on AgniSql\Sqlinst2

image

The Reporting Services was started successfully on Agni-ssrs-web

I reset IIS

image

browse to http://localhost/Reports

image

Got the current identity (..) does not have write access to c:\widows\Microsoft.net\framework\v2.0.50727\Temporary asp.net files

So I ran CMD

cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

aspnet_regiis -ga “agninet\RptService”

By doing this I am granting access to agninet\RptService account to IIS metabase and other directories used by ASP.net

image

iisreset, browse to http://localhost/Reports

image

Report page came up successfully

Advertisements

One thought on “2 Tiered SQL Report Server Installation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s