I’m working on setting up a scale-out SSRS installation. We’ll be shutting down the old URL soon and everyone should be off it. I can tell who is still hitting the old URL, which leads to the old server by using this query.
We’re using the ExecutionLog2 view in the reportserver database. Much easier than joining tables together and trying to build paths to reports with a CTE.
More information on this view.
http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx
I’ve been trying to get an SSRS scale-out cluster working the past few months. I’d given up today and resorted to calling MSFT support. I’d read all the manuals and supplemental articles like a proper admin should. I’d posted questions on the MSFT forums to no avail. Time to bite the bullet and sit on the phone with an expert.
I was getting 401 errors with not much help from the error logs. A 401 is a security error. The problem was intermittent, sometimes happening, sometimes not. The problem always went away when a node was removed from the load balancer rotation. Major hint there. When you connect to the load balancer, you are routed to the same SSRS node – until you time out at 10 minutes – this is called “Sticky Sessions”. The SSRS servers also got this treatment from the load balancer – and that was the problem.
The tech and I went through hours of checking configurations until he asked me to add an entry to the hosts file for the F5 name which reporting services was using. This would make node 1 always request data from SSRS on node 1 and node 2 always request data from node 2. Fixed the issue. I imagine we can configure this on the load balancer as well, which I will probably have done.
This weekend I’ll be upgrading a copy of our 2005 instance to 2008. Upgrading the copy will let us fall back to the old version if needed. (update: and yes, we had to, see notes below).
I opted to create a new instance on another server – apart from our clustered SQL engine. Running IIS on a SQL server is not recommended and you can run into some memory issues with IIS and SQL 2005 reports. It’s also a security issue, serving pages from your database server.
My plan was to install some 2008 components on the server – analysis, database, integration. Then I installed 2005 reporting and created a copy of the ReportServer database on this report server.
When it came time to upgrade the 2005 instance, the upgrade would not proceed. There were no components detected by the wizard to upgrade. So I uninstalled the 2008 database, moved the database to our new 2008 DB cluster and the upgrade worked. Then I cleared everything off and reinstalled 2005 with all the services. I patched it up and once more, restored the ReportServer (and of course ReportServerTempDB) to my 2008 cluster.
A quick note – if you’re planning on hosting more than one SSRS database on a server and need to change one of the database set names, you’ll need to update all the stored procedures in the ReportServer database to point to the properly named TempDB. You can script out all the procedures in ALTER form in SSMS and then run a find/replace on ReportServerTempDB. Perhaps it should be a best practice when installing a report server not to give the database the default name – use some kind of numbering scheme, so we don’t have to resort to hacky solutions when things go bad and you may have to consolidate some apps onto one server.
Another note – I also tried to attach the 2005 report server database to a 2008 report instance – it didn’t automatically upgrade it, just got an error.
Another challenge is managing the subscription jobs when setting up a new server. I wrote a script which would find all the jobs with a category of reportserver and generate some scripts to enable, disable and delete the jobs. This let me set up the new production server and then disable all the jobs until the cutover – otherwise we’re going to get duplicate reports emailed to people. The script is below – set your output to text and run the output.
–disable
USE msdb
SELECT ‘EXEC msdb.dbo.SP_UPDATE_JOB @job_id = ”’ + CONVERT(VARCHAR(100),j.job_id) + ”’ , @enabled = 0′ FROM dbo.sysjobs_view j INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
WHERE c.NAME = ‘Report Server’
–enable
USE msdb
SELECT ‘EXEC msdb.dbo.SP_UPDATE_JOB @job_id = ”’ + CONVERT(VARCHAR(100),j.job_id) + ”’ , @enabled = 1′ FROM dbo.sysjobs_view j INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
WHERE c.NAME = ‘Report Server’
–delete
USE msdb
SELECT ‘EXEC msdb.dbo.SP_DELETE_JOB @job_id = ”’ + CONVERT(VARCHAR(100),j.job_id) + ”’ ‘ FROM dbo.sysjobs_view j INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
WHERE c.NAME = ‘Report Server’
Important notes from this install
If you want to upgrade 2005, don’t put 2008 on the box first. I’ve had it work once and fail twice.
Stopping and starting the report service in the report configuration manager will sometimes fix things.
Make sure the ssl certificate friendly name is the same as the url. Getting 2005 set up on server 2008 needed this.
Installing reporting services 2005 on IIS 7 takes a little additional config. You’ll need to enable some features in IIS7 and make sure to run SSRS in a classic .net app pool. http://support.microsoft.com/kb/934164
Be sure to assign the ssl certificate to the web site, just as you would in IIS6. If the check mark to require SSL is grayed out in SSRS configuration manager, you probably haven’t done this.
Make sure your sever can ping the url you are planning on using and that you have IIS running if you are planning on using ssl in 2008 – it is a dependency. If you are adding ssl bindings in the 2008 SSRS config tool and they are successful, but not showing up, then something has been hosed. This can be fixed by using the HttpSysConfig.exe tool to delete the http.sys bindings and then deleting the entries for those URLs from the report server config file. I had this issue during our first attempt and couldn’t figure it out during our allotted down time.
The new reporting server is on a VMWare virtual 2008 server – love it!
Phew!! Hopefully this weekend everything works out this weekend – I’m sick of Reporting Services.
Once a year, our certificates expire for ssl on reporting services. These can be replaced with zero down time in reporting services.
1. Every year, the certificate for reporting services must be renewed.
2. Obtain certificate .pfx file and password from web infrastructure team.
3. Connect remotely to the reporting server.
4. Open Control Panel > Administrative Tools > Public Key Management
5. Open Certificates (Local Computer) > Personal
6. All Tasks > Import
7. Let the wizard determine the store.
8. Open IIS > Web Sites > Default WebSite > Directory Security Tab > Server Certificate
9. Replace the current certificate
10. Pick new cert from the store
11. View the website and click the lock to the right of the url bar in IE. Click the ‘view certificate’ button and ensure the date has been changed to the new certificate effective/expiry dates.
As you can see, we didn’t restart the website and you’re using the new cert.
Microsoft’s licensing has once again saved me some work. To run Reporting Services in SharePoint integrated mode , you will need a SharePoint license on your reporting server. A SOAP endpoint is enabled on the report server which lets SharePoint talk to the report server. Seems somewhat of a strange departure from the typical MSFT licensing. We will not be buying another license, needless to say.
