Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Scale Out SQL Server 2008 R2 Reporting Services Farm using NLB Part 1


By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | > Reporting Services Network Load Balancing

Attend these FREE MSSQLTips webcasts >> click to register


Problem

Delivering reports is becoming more critical due to the increasing demand for business intelligence solutions. And while there are a lot of guides that walk us through building a highly available database engine, you'll rarely see one for SQL Server Reporting Services. How do I go about building a scale-out SQL Server 2008 R2 Reporting Services running on Windows Server 2008 R2?

Solution

A SQL Server 2008 R2 Reporting Services scale-out deployment is when two or more report server instances share a single report server database. This type of deployment enables increasing the number of users who concurrently access reports - usually for supporting high-volume reporting - and improving the availability of the report server. Microsoft has outlined two supported scenarios in implementing a SQL Server 2008 Reporting Services scale-out solution - for load balanced solutions and for improved performance of service features. This series of tips will walk you through building a scale-out SQL Server 2008 R2 Reporting Services using Windows Server 2008 R2 Network Load Balancing with two servers acting as nodes of the cluster. If you need your report server farm to be highly available as well, you need to make sure that the report server database is hosted on a SQL Server Failover Clustered instance. You can check out this series of tips on installing SQL Server 2008 on a Windows Server 2008 Cluster. It is assumed that you already have your database server up and running, thus, building the scale-out SQL Server 2008 R2 Reporting Services farm on Windows Server 2008 R2 Network Load Balancing from scratch. It is also important to have an architecture design in place before going straight to implementation as this series of tips does not cover design concepts.

What is Network Load Balancing?

Network Load Balancing (NLB) is a clustering technology offered by Microsoft as part of the Windows Server operating systems. The good thing about Windows NLB is that it is available on almost all of the editions of Windows Server 2008 R2, unlike the failover clustering feature where you need at least the Enterprise Edition to have it configured. NLB uses a distributed algorithm to provide load balanced network traffic for IP-based services such as Web, Virtual Private Networking, Streaming Media, Terminal Services, Proxy, etc. This makes Windows NLB and ideal choice for SQL Server 2008 R2 Reporting Services as it is hosted as a web service.

Similar to the failover clustering technology, NLB assigns a virtual IP address to the cluster. When a client request is made using this virtual IP address, the NLB maps it to the physical IP address of one of the cluster nodes based on the configurations made as well as availability of the nodes. As far as the end user is concerned, only a single server is serving the request. However, NLB nodes do not share data with each other like in a failover clustering technology. Each node requires a local copy of the data in order to serve requests. This is why the report server database needs to be on its own server as nodes need to access the same report server database.

Preparing your network

While this task is not common for the DBA, it is important to understand what needs to be prepared prior to deploying your Windows NLB cluster. Note that this is one of the reasons why you need to be in good terms with your systems administrator

Create a DNS entry for the NLB cluster application

Your DNS administrator will have to do this unless you are granted permissions to administer your DNS servers. As users will access the NLB cluster using a friendly name, a DNS entry needs to be created that maps to the virtual IP address that will be used by the NLB cluster for SQL Server 2008 R2 Reporting Services. Make sure that you already have an address allocated for the virtual IP. To create a DNS entry for the NLB cluster application, open DNS Administrator from the Microsoft DNS server. Right-click on the domain name and select New Host (A)...

building a scale- out sql server 2008 r2 reporting services running on windows server 2008 r2

On the New Host property page, enter the NLB cluster application name as well as the virtual IP address. Click Add Host

it is important to have different application names with their corresponding virtual ip address

In this example, I used the name SSRS2008R2NLB with an IP address of 172.16.0.80. This means that requests made to the application name will be directed to the specified IP address.

Note that we can have multiple applications hosted in the NLB cluster, thus, it is important to have different application names with their corresponding virtual IP address. For this example, we will only host one application - the SQL Server 2008 R2 Reporting Services instance.

Configure the server network cards

Depending on how your network infrastructure is configured, you may have different network card configurations. Your servers may only have a single network card configured with a single IP address or multiple network cards each with its own IP address. Configuration of the NLB cluster will depend on how your network cards are configured. It is important to consider the IP address that you will use to listen to requests as this will be the IP address that we need to use for the NLB cluster. Unlike in failover clustering technology where you would need an additional network card to act as the heartbeat, NLB can create a unique cluster adapter based on how your network adapter is configured. However, it is recommended to have an additional network adapter that is on a different subnet from the production network specifically for heartbeat use to improve communication between cluster nodes. In this example, I only have a single network card with a single IP address.

advanced tcp/ip settings

After the NLB cluster has been configured and the nodes added, we will check the network card configuration again to see what the NLB cluster configuration has done.

Adding the Network Load Balancing Feature

Similar to enabling the failover clustering feature in Windows Server 2008 R2, we will use the Server Manager console to add the Network Load Balancing feature. To add the Network Load Balancing feature:

  1. Open the Server Manager console and select Features.
  2. Click the Add Features link. This will run the Add Features Wizard
  3. In the Select Features dialog box, select the Network Load Balancing checkbox and click Next. (And while you're at it, you can include the .NET Framework 3.5.1 Features as well as this will be used by the SQL Server 2008 R2 installation)
    use the service manager console to add the nlb feature

  4. In the Confirm Installation Selections dialog box, click Install to confirm the selection and proceed to do the installation of the Network Load Balancing feature
    you can include .netframework3.5.1 features as well as this will be used by the sql server2008 r2 installation

  5. In the Installation Results dialog box, click Close. This completes the installation of the Network Load Balancing feature on the first node.
    this completes the installation of the nlb feature

These steps have to be repeated on all the other nodes of the NLB cluster before proceeding with creation of the new NLB cluster. In the next tip in this series, we will go thru the process of creating the NLB cluster in preparation for installing SQL Server 2008 R2 Reporting Services

Next Steps
  • Prepare a test environment to apply this deployment scenario.
  • Review the series of tips on Installing SQL Server 2008 on a Windows Server 2008 Cluster to create a highly available database engine for the report server database.


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, October 18, 2012 - 1:18:37 PM - bass_player Back To Top

Unfortunately, since you are actively using all of the nodes in your NLB cluster, you will have to license all of them to run SSRS in an NLB farm


Wednesday, October 17, 2012 - 9:11:50 AM - KBeuseli Back To Top

Hello Edwin,

Nice work. 

In order to build such a solution what are the licensing requirements ?   If you build a active/passive SQL cluster it would be very nice if you only one SQL Server 2008R2 ENTERPRISE license.  Is that the case with this reporting services farm ?

regards,

K

 


Wednesday, September 19, 2012 - 9:19:39 AM - bass_player Back To Top

The article only covers the Reporting Services side and makes it highly available by running it in a network load balanced environment. The database engine should be on a separate machine running on a failover cluster. This makes both the Reporting Services and the database engine highly available


Tuesday, September 18, 2012 - 4:14:16 PM - Francis Back To Top

Please what do you mean by this statement

"If you need your report server farm to be highly available as well, you need to make sure that the report server database is hosted on a SQL Server Failover Clustered instance"

Do you mean that one should configure the NLB for the reporting services on the same cluster that will be hosting the SQL server configuration as well? 

I look forward to hearing from you.


Wednesday, March 21, 2012 - 11:41:21 AM - PETER NERIDA Back To Top

asfaf

 

Edwin,

is chaging native mode to sharepoint mode make a difference in performance? We have a report server that shutdown the services everytime we execute some weekly jobs. What seems to be cause and where to find it. We think we have increase the memory but we are not sure. It's in the hands of sharepoint administrator. 

Thanks,

Peter


Learn more about SQL Server tools