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

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

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)...

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

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.

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)
   
   

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
   
   

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

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.

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips