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
The picture is pretty self explanatory, so I will just get to the details on how I went about to achieve this.
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
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
This took a while for me, (I doing this on 2 virtual pc’s), so please wait.
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
Click In Surface Area Configuration for Services and Connections, verify that the Report Server Windows service is running.
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.
When I click windows integration Security I get error “Argument value was not passed for the argument Reporting service URL”
I just ignored it and went forward
Then I started Report server Configuration on Agni-ssrs-web box
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
Selected New again for Report Manager Application Pool
Clicked Apply got the error, I mentioned earlier
Clicking on the link got “ReportServicesConfigUI.WMIProviderException: A Virtual Directory must first be created…”
So I am off to create virtual directory, I click Report Server Virtual Directory, click new
Click Report Manager virtual directory
Back in web service identity. I see ASP.Net Service Account as got NT Authority\NetworkService
Open the Database Setup page, I have created AgniSql\SQLINSt2 instance. There are no report database in this instance.
Click new in database name field, I changed server name from AgniSql to AgniSql\sqlInst2
Database successfully created. Changed the server name from AgniSql to AgniSql\Inst2 and clicked apply
Looks like after the error happened it retried and went thru successful
I left the encryption keys thing as is
Verify reporting server installation
The 2 databases were created, on AgniSql\Sqlinst2
The Reporting Services was started successfully on Agni-ssrs-web
I reset IIS
browse to http://localhost/Reports
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
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
iisreset, browse to http://localhost/Reports
Report page came up successfully