Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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?
To continue this series on Building a Scale-Out SQL Server 2008 R2 Reporting Services Farm using Windows Network Load Balancing, we will look at building and configuring our NLB cluster in preparation for installing SQL Server 2008 R2 Reporting Services. In Part 1, we have completed adding the Network Load balancing feature on both of the servers that we will be using as part of the NLB cluster. This tip will walk you through creation and configuration of the NLB cluster.
Creating the NLB Cluster
As the Network Load Balancing feature has been installed on both of the servers, we can now create network load balanced clusters using the Network Load Balancing Manager console. You can run this administrative console on any of the servers that will become nodes of the NLB cluster.To create the NLB Cluster,
- Open the Network Load Balancing Manager console. In order to create a new NLB cluster, your account needs to be a member of the local Administrators group of the servers that will be a part of the cluster. However, you can configure a different account that will be used to do the creation of the cluster. To do that, select the Options menu and click Credentials..
You will be prompted to enter the credentials of a login that will be used to connect to the servers as you are creating the NLB cluster.
- Proceed with the creation of the NLB cluster. Under the Cluster menu, select New to create a new NLB cluster. This will run the New Cluster wizard
- On the New Cluster : Connect page, enter the hostname or IP address of the first server that will be a part of the NLB cluster. Click Connect. This will detect the available network cards on the server. Select a specific IP address that will be used by the NLB cluster traffic. As mentioned in the previous tip, the network card configuration will play an important role in how your NLB cluster works.
NLB clustering can be configured using different types of models, depending on the number of network cards available on the servers. While it is recommended to use more than one network card for optimum performance and high availability, a single network card configuration works just fine. The different types of NLB model are described below
- Single network adapter in Unicast Mode - this means that your servers only have a single network card. This configuration is suitable when network communication among cluster hosts is not required and in which there is limited dedicated traffic from outside the cluster subnet to specific cluster hosts. This also means that nodes in the cluster can communicate with addresses outside the cluster subnet, but communication between the nodes within the cluster subnet is not possible. Plus, the MAC address of the network card is disabled and the cluster MAC address is used.
Multiple network adapters in Unicast Mode - as the name implies, you have multiple network cards in your servers which may or may not be dedicated for NLB networking such as backups, monitoring and management. This configuration is suitable when network communication among cluster hosts is required. It is also appropriate when you want to separate the traffic used to manage the cluster from the traffic occurring between the cluster and client computers. Nodes within the cluster can communicate with each other within the cluster subnet and also with addresses outside the subnet. As this configuration is still in unicast mode, the MAC address of the network card is disabled and the cluster MAC address is used.
Single network adapter in Multicast Mode - again, single network card on the server but the NLB cluster is configured in multicast mode. This configuration is suitable when network communication among cluster hosts is required but there is limited dedicated traffic from outside the cluster subnet to specific cluster hosts. Nodes within the cluster can communicate with each other within the cluster subnet and also with addresses outside the subnet. Unlike in unicast mode, both the network card and cluster MAC addresses are enabled.
- Multiple network adapters in Multicast Mode - this configuration is suitable for a cluster in which ordinary network communication among cluster hosts is necessary and in which there is heavy dedicated traffic from outside the cluster subnet to specific cluster hosts. Nodes within the cluster can communicate with each other within the cluster subnet and also with addresses outside the subnet and that both the network card and cluster MAC addresses are enabled.
Understanding how you want your NLB cluster to function will help guide you in configuring your network cards prior to creating the cluster. For this series of tips, I will be using a single network card configured in multicast mode as I want the simplest configuration as well as be able to allow communication between my cluster nodes as well as within the network.
- Let's continue with the creation of the NLB cluster. On the New Cluster : Host Parameters page, select a value in Priority (Unique host identifier). This parameter specifies a unique ID for each host that you will add in your NLB cluster. The host with the lowest numerical priority among the current members of the cluster handles all of the cluster's network traffic that is not covered by a port rule. Think of it as the active node in the failover cluster. Click Nextto continue.
- On the New Cluster : Cluster IP Addresses page, click Addto enter the cluster IP address that is shared by every host in the cluster.
Enter the virtual IP address of the NLB cluster and click OK. This IP address will be used by the clients to communicate with the application, in our case, the SQL Server 2008 R2 Reporting Services instance. This IP address is the same as the one we added in our DNS server in the previous tip.
NLB adds this IP address to the TCP/IP stack on the selected network card of all hosts chosen to be part of the cluster. We will see this later when we check on the new TCP/IP configuration of the network card after the NLB cluster is created. NLB supports IPv6 but doesn't support Dynamic Host Configuration Protocol (DHCP). Since I don't have IPv6 configured on my network cards, the option is disabled. NLB also disables DHCP on each network card it configures, so the IP addresses must be static.
Note that the first IP address listed is considered the primary cluster IP address and used for cluster heartbeats for this configuration - single network adapter in multicast mode. This information will be useful later on when you want to capture network traffic as part of troubleshooting. Click Next to continue.
- On the New Cluster : Cluster Parameters page, under the Cluster IP configuration section, enter the fully qualified domain name of the application that will be running on the NLB cluster. In this example, I will be using the DNS entry that we created in the previous tip - SSRS2008R2NLB.TESTDOMAIN.local
In Cluster operation mode section, select Multicast and click Next.
- On the New Cluster : Port Rules page, click the Editbutton to modify the default port rules.
This will open the Add/Edit Port Rule page. Since SQL Server 2008 R2 Reporting Services is a web service, we will use the default port number for HTTP traffic - port 80. In cases where you would use a non-default port number for the web service, assign that port number in this page. We will use the Port Range section for this task and, for security reasons, limit the port number to only allow traffic for that specific port number. On the Protocols section, select TCP. On the Filtering mode section, select Multiple host. This specifies that multiple hosts in the NLB cluster will handle network traffic for this port rule. On the Affinity section, select Single. This configuration is commonly used for web servers especially when the application is not capable of maintaining state across servers. Single affinity forces clients to use the same server as the previous connection, thus, maintaining session state. While traffic from multiple clients will be distributed over multiple cluster nodes, the load may not be spread evenly, because, depending on the application requirement, one client could generate more traffic and sessions than another client. Click OK to close the Add/Edit Port Rule page.
On the New Cluster : Port Rules page, click Finish to create the NLB cluster.
This will create a new NLB cluster with just a single server.
As I've mentioned in the previous tip, we'll look at the new TCP/IP configuration of the network card on the first node that we've added in the NLB cluster
The configuration settings we did while creating the new NLB cluster is now implemented in the network card - Network Load Balancing (NLB) is already installed and enabled as well as the virtual IP address assigned for the NLB cluster is added in the list of IP addresses.
Adding hosts to the NLB cluster
To add hosts to the cluster, right-click the new cluster, and then click Add Host to Cluster. Configure the host parameters (including host priority and dedicated IP addresses) for the additional hosts by following the same instructions that you used to configure the initial host.
Since you are adding hosts to an already configured cluster, all the cluster-wide parameters remain the same, thus, these options will be disabled such as the Add/Edit Port Rule page.
Similar to how you would test a Windows failover cluster defined in this tip, you can run a continuous PING test on the virtual IP address or the fully qualified domain name while rebooting one node at a time to see how the NLB cluster responds. And since this NLB cluster will host SQL Server 2008 R2 Reporting Services, a TELNET test on port 80 would be helpful to see if the cluster is indeed listening.
In the next tip in this series, we will proceed to install SQL Server 2008 R2 Reporting Services on top of the existing NLB cluster.
- 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: 2011-03-28
About the author
View all my tips