·
5 min read

Configure SSRS for load balancing in your Azure deployment

Using the Cloud-hosted environment tool in Lifecycle Services, you can deploy a High Availability environment. When you deploy a High Availability environment, two BI servers are deployed by default. Each BI server has SQL Server Reporting Services (SSRS) and SQL Server Analysis Services (SSAS) configured as a unique local instance. Use the following information to configure SSRS for load balancing.

 

Here’s what you’ll need to do:

  • Create an Azure Internal Load Balancer (ILB)
  • Set up SSRS in scale-out mode
  • Connect Microsoft Dynamics AX to SSRS and then deploy reports
  • Add the ReportServer database to the AlwaysOn Availability Group
  • Complete final configuration steps
  • Verifying the Azure ILB

 

Create an Azure Internal Load Balancer (ILB)

  1. Install and configure Azure PowerShell
  2. If deploying a High Availability or Test environment, steps a and b have already been done for you. You may want verify the Azure ILB settings.
    Note: We recommend that you paste the following commands into a text document with a PS1 extension, and then run in PowerShell. 
    1. Use the Get-AzureInternalLoadBalancer command to review the existing ILB
      Get-AzureInternalLoadBalancer -ServiceName <Name of the Azure Cloud Service name for the BI servers>
    2. Use the Get-AzureEndpoint command to review the existing endpoints for a specific machine.
      $test = Get-AzureVM
      foreach ($t in $test){
      $ILBs = Get-AzureVM -ServiceName $t.ServiceName -Name $t.Name | Get-AzureEndpoint |? {$_.InternalLoadBalancerName -eq "ILB"}
      if($ILBs -ne $nul){
      ("VMName=" + $t.Name)
      $ILBs
      "'r'n"
      }
      }
  3. Use the Add-AzureEndpoint command to add endpoints to an existing ILB for SSRS traffic.
    Note: We recommend that you paste the following commands into a text document with a PS1 extension file for each BI server and service name, and then run in PowerShell.
    $AzureCloudServiceName="<service name>"
    $EndpointName="ReportingServices"
    $LoadBalanceSetName="ReportingServices"
    $Protocol="tcp"
    $Port=80
    $LoadBalancerName="ILB"
    $VirtualMachineName="BI-1","BI-2" #BI VM names separated by commas
    ForEach ($VM in $VirtualMachineName)
    {
    Get-AzureVM -ServiceName $AzureCloudServiceName -Name $VM | Add-AzureEndpoint -Name $EndpointName -Protocol $Protocol -LocalPort $Port -PublicPort $Port -DefaultProbe -InternalLoadBalancerName $LoadBalancerName -LbSetName $LoadBalanceSetName | Update-AzureVM     
    }

 

 

Set up SSRS in scale-out mode

  1. On BI1 server:
    1. Open the Reporting Services Configuration Manager and connect to the local instance.
    2. Click Database.
    3. Click Change Database and use the wizard to create a new database for this SSRS instance using the SQL listener name (for example: SQL-LS-xxx). Note that this name can be found on the AOS VM in the Microsoft Dynamics AX Server Configuration Utility (on the Database Connection tab).
    4. Verify that you can access http://BI1/reports and http://BI1/reportserver.
      Note: In the above URLs, replace BI1 with the name of your BI server. 
  2. On BI2 server:
    1. Open the Reporting Services Configuration Manager and connect to the local instance.
    2. Click Database.
    3. Click Change Database and use the wizard to point to the database created in step 1.c.
  3. On BI1 server:
    1. Open the Reporting Services Configuration Manager and connect to the local instance.
    2. Click Scale-out Deployment and add the BI2 server.
      Note: If you do not see the BI2 server in the list then check <InstallationID> GUID in C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config of all BI servers. If this value is the same then change to be unique for each BI server (for example ending in …81 on BI2 server and …80 on BI1 server).
    3. Verify that you can access http://BI2/reports and http://BI2/reportserver.
      Note: In the above URLs, replace BI2 with the name of your BI server.
    4. Open the web.config file located in C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportManager. Add a unique machineKey element to the <system.web> section. For example
      <machineKey
      validationKey="2D2BB19AF64B6A8A641FD12924907684E5D7386B037C38A3F6DE0E4514D8BABD43A34CF0BA40D8FD0F18108A7F2A844B2C397E48BC2A77CB39AEFBAEC0114B4B"
      decryptionKey="6A01767D72BADEEB5D28EAFD213DFCA5087476BC5A4BC70A"
      validation="SHA1"
      decryption="AES" />
      Note: You can use the AOS VM to generate the machineKey. See this blog post for more information.
    5. Add the machineKey element (used in step 3.d.) to the the web.config file located in C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer.
  4. On BI2 server:
    1. Add the machineKey element (used in step 3.d.) to the web.config file located in C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportManager.
    2. Add the machineKey element (used in step 3.d.) to the web.config file located in C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer.
  5. Restart SQL Server Reporting Services instances on BI1 and BI2.

 

 

Connect Microsoft Dynamics AX to SSRS and then deploy reports

Open Microsoft Dynamics AX and use the Report servers form (System administration > Setup > Business intelligence > Reporting Services > Report servers) to connect AX 2012 to SSRS. Using 2 AOS servers and 2 BI nodes with an NLB host (ILB) as an example, there will be the following 6 entries in the Report servers form.

Delete original configuration entries.  

Now open PowerShell and run the following commands to deploy reports to each node:

  • Publish-AXReport -Id RSConfigC -ReportName *
  • Publish-AXReport -Id RSConfigE -ReportName *

 

 

Add the ReportServer database to the AlwaysOn Availability Group

Note: Manual actions must be completed after a failover. Until these actions are completed, some features of the report server may not work correctly. See the section entitled “Steps to complete disaster recovery of Report Server Databases” in Reporting Services with AlwaysOn Availability Groups (SQL Server) to see the steps that must be completed after a failover occurs.

 

Add the ReportServer database to the existing AlwaysOn Availability Group (AG). To do so: 

  1. Open SQL Server Management Studio and connect to your instance (for example: SQL-LS-xxx).
  2. Perform a full backup of the ReportServer database.
  3. Expand AlwaysOn High Availability.
  4. Right-click the existing AG and click Add Database.
  5. Complete the wizard.
    Note: You must have a path that is shared between servers, such as \\server\Backup. (This is one of the SQL Servers, like SQL1.)

Then, back up the ReportServerTempDB database on the primary SQL Server. Restore it on the secondary SQL Server.

 

 

Complete final configuration steps

On client and EP VMs:

  1. Browse to this location: C:\Windows\System32\drivers\etc\hosts.
  2. At the bottom of the file, provide the IP address of the Azure ILB and SSRSNLB name, for example: 10.1.1.6 SSRSNLB.

Repeat this procedure on all client and EP VMs.

 

On BI VMs:

  1. Browse to this location: C:\Windows\System32\drivers\etc\hosts.
  2. At the bottom of the file, provide the IP address of the Azure ILB and AOSLoadBalancer name, for example: 10.1.1.6 AOSLoadBalancer.
  3. Open the Microsoft Dynamics AX Configuration Utility.
  4. In the Configuration target list, select Business Connector (non-interactive use only).
  5. On the Connection tab, edit the server name to be AOSLoadBalancer.
  6. Proceed with WCF refresh configuration when prompted.

Repeat this procedure on all BI VMs.

 

 

Verifying the Azure ILB

The psping.exe tool can be used to verify Azure ILB ports. Example: psping.exe SSRSNLB:80 

These ports can be verified from Client or EP VMs. 

Eric Moos

Related posts