Reporting Services – SQL Server 2008 Cluster

September 9, 2012

I created the following notes when configuring Reporting Services in our SQL Server 2008 active/active cluster. This particular SQL Cluster installation comprised of 2 SQL instances and 2 Cluster nodes. One SQL instance running on each node, hence active/active.

High level steps were as follows:

  1. Install Reporting Services on each node but do not configure when prompted in the install wizard
  2. Specify the same database on each report server using the Reporting Services configuration tool.
  3. Join the report server instances to each other by completing the scale-out area in the Reporting Services Configuration tool.

We were attempting to achieve the below scenario:

Reporting Services SQL Cluster

RS Service & Binary Installation

  • On Node1, run setup – Installation – new installation or add feature.

SQL Server Install Screen1

  • Select ‘Reporting Services’ from the feature list

SQL Server Install Screen 2

  • Select new instance name for this RS:  Instance1_RS_NODEA
  • Enter service account
  • Install but do not configure.
  • Repeat the above on Node1, specifying the instance name as: Instance2_RS_NODEA
  • At this point, there will be two RS services on Node1 named;
    • [ReportServer$Instance1_NodeA] and
    • [ReportServer$Instance2_NodeA]
  • Repeat the above steps on Node2, specifying Instance1_NodeB and Instance2_NodeB
  • At this point, there will be two RS services on Node2 named;
    • [ReportServer$Instance1_NodeB] and
    • [ReportServer$Instance2_NodeB]

Reporting Services Configuration

  1. On Node1, run ‘Reporting Service Configuration Manager’
  2. Connect to Instance1_RS_NODEA.
  3. Web service URL: http://node1:80/ReportServer_SQLinstance1
  4. Database: Create a new RS database
  5. Server name: MYSQLCLUSTER1\SQLInstance1
  6. Set Report Manager: http://node1:80/Reports_SQLInstance1
  7. Setup email, backup encryption keys.
  8. Exit
  9. On Node1, run the ‘Reporting Service Configuration Manager’
  10. Connect to Instance2_RS_NODEA
  11. Web service: http://node1:80/ReportServer_SQLinstance2
  12. Database: Create a new RS database
  13. Server name: MYSQLCLUSTER2\SQLInstance2
  14. Report Manager: http://node1:80/reports_SQLInstance2
  15. Setup email, backup encryption keys.
  16. Exit

At this point; we have Node1 with the two Reports URL’s pointing to the two reports database, each hosted on separate SQL Instances.

  1. On Node2, run ‘Reporting Service Configuration Manager’
  2. Connect to Instance1_RS_NODEB Web Service URL:      http://node2:80/reportServer_SQLInstance1
  3. Choose an existing report server database, connect to the cluster (MYSQLCLUSTER1\SQLInstance1) and select ReportServer
  4. Report  Manager: http://node2:80/reports_SQLInstance1
  5. Encryption key already done on first node.
  1. On Node2, run ‘Reporting Service Configuration Manager’
  2. Connect to Instance2_RS_NODEB
  3. Web Service URL: http://node2:80/reportServer_SQLInstance2
  4. Choose an existing report server database, connect to the cluster (MYSQLCLUSTER2\SQLInstance2) and select ReportServer
  5. Report  Manager URL: http://node2:80/reports_SQLInstance2
  6. Encryption key already done on first node.

We’ve now configured and joined RS on Node2 to Node1.

  1. On Node1, Run the ‘Reporting Service Configuration Manager’ and connect to SQLInstance1
  2. Click on ‘scale-out deployment’
  3. Add the server and click on Join. The status should change from ‘waiting to join’ to ‘joined’.
  4. Repeat the above 3 steps for SQLInstance2

The final result should be as follows:

Node1

http://Node1:80/reports_sqlinstance1

http://Node1:80/reports_sqlinstance2

Node2

http://Node2:80/reports_sqlinstance1

http://Node2:80/reports_sqlinstance2

Windows Cluster URL

http://WinClusterVIP/reports_sqlinstance1

http://WinClusterVIP/reports_sqlinstance2

It may seem very complicated but hopefully by referring back to the diagram as you step through the instructions things will become cleared and make sense. Thanks for reading.

Advertisements

One Response to “Reporting Services – SQL Server 2008 Cluster”


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: