In this article, we will install SQL Server Reporting Services on 2 dedicated Reporting Services Servers Highly Available using Windows Network Load Balancing.
You can view the other parts to this series here:
Part 1 – Installing Highly Available SQL 2016 with SP1 for System Center 2016 – Part 1 (SQL Server AlwaysOn Availability Groups)
Part 2 – This Article
Part 3 – Installing Highly Available SQL 2016 with SP1 for System Center 2016 – Part 3 (Reporting Services on a SQL Cluster)
Part 2 – This Article
Part 3 – Installing Highly Available SQL 2016 with SP1 for System Center 2016 – Part 3 (Reporting Services on a SQL Cluster)
Preparing your network
Create a DNS entry for the NLB cluster application
To create a DNS entry for the NLB cluster application, open DNS console on one of your DNS Servers. Right-click on the domain name and select New Host (A)…
On the New Host property page, enter the name you want to use to connect to the Load Balanced Reporting Services as well as a virtual IP address that will be assigned to the NLB. Click Add Host
Adding the Network Load Balancing Feature
Open the Server Manager console on your first Reporting Server and select Add roles and features.
Follow the wizard through and add the Network Load Balancing feature
Continue thought the wizard until the feature is installed.
Once installed open the Network Load Balancing Manager console
Under the Cluster menu, select New to create a new NLB cluster. This will run the New Cluster wizard
Enter the hostname or IP address of the first Reporting Server that will be part of the NLB Cluster and click Connect then click Next
On the Host Parameters screen click Next
Click Add to enter the cluster IP address that you assigned the DNS record you created above. This will be the Virtual IP (VIP). Then click Next
(Note: 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)
Next, enter the Fully Qualified Domain Name (FQDN) if the DNS record you created earlier and select Multicast then click Next
(Note: 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)
On the Port Rules screen click Edit and change the port range From: 80 To: 80 TCP
Click OK
Click Finish
This will create a new NLB cluster with just a single server
Right-click the new cluster, and then click Add Host to Cluster
Enter the name of the second Reporting Server and click Next
On the Host Parameters screen click Next
On the Port Rules screen click Finish
The NLB Cluster is now up and running
Installing SQL Server 2016 Reporting Services on the NLB Cluster
Insert the SQL 2016 with SP1 Enterprise Edition media and run setup.exe
When the Installation Center opens, click Installation then click New SQL Server stand-alone installation or add features to an existing installation
Enter your Product Key or click Specify a free edition for a 180 free trial
Click I accept the licence terms and click Next
Select Microsoft Update if you wish and click Next
Acknowledge the prerequisites and click Next.
(Note: You may need to open up the SQL ports in the Windows Firewall depending on your environment. Use this guide if necessary https://technet.microsoft.com/en-GB/library/ms175043(v=sql.110).aspx)
Select the Reporting Services – Native feature and click Next
On the Instance Configuration select either the Default instance or Named instance, depending on your configuration and click Next
Enter your Service Account and password and change the Startup Type to Automatic and click Next
On the Configuration screen you will see we have no option than to Install only. Therefore, click Next
Click Install
Once setup is complete click Close
Now do exactly the same process on the second Report Server.
Configuring the first SQL Server 2016 Reporting Services instance
From the Start Menu open the Reporting Services Configuration Manager
On the Connection Screen Click Connect
Click Web Service URL then click Apply to accept the defaults
You should see success in the Results pane
Now click on Database then click Change Database
Click Create a new report server database and click Next
Enter the name of your AlwaysOn Availability group LISTENER and click Test Connection and ensure it succeeds. Then click Next
Rename the database if you wish and click Next
Accept the defaults for the credentials and click Next
On the Summary click Next
Once the wizard has ran and everything was successful click Finish
Click the Web Portal URL link. Accept all the default values and click Apply
Verify that the reporting services instance is configured and working properly. You can do this by opening a web browser and accessing the URL defined in the Web Portal URL page
Once we have a working SQL Server 2016 Reporting Services installation working for the first node we need to add the second node.
Configuring the second SQL Server 2016 Reporting Services instance
Log onto the second Reporting Services server.
From the Start Menu open the Reporting Services Configuration Manager
On the Connection Screen Click Connect
Click the Web Service URL and click Apply.
Click the Web Portal URL and click Apply.
Click the Database link and click the Change Database button. This will open the Report Server Database Configuration Wizard
Select the Choose an existing report server database option and click Next
Enter the name of your AlwaysOn Availability group LISTENER and click Test Connection and ensure it succeeds. Then click Next
Select your ReportServer DB from the drop down menu and click Next
Accept the defaults for the credentials and click Next
On the Summary click Next
Once the wizard has ran and everything was successful click Finish
Join the second SQL Server 2016 Reporting Services instance to the Scale-Out Deployment
In the past, you may not have paid attention to the last link in the Reporting Services Configuration Manager – the Scale-Out Deployment link. When you click on that link, you should see a message stating that you should connect to the report server that has already been joined to this scale-out deployment.
Click Web Service URL then click Apply to accept the defaults
Click Scale-out Deployment
When you click on that link, you should see a message stating that you should connect to the report server that has already been joined to this scale-out deployment
This means that we need to connect to the first Reporting Services Server that we configured and accept the second reporting services instance to join the scale-out deployment.
On the first Reporting Services Server in Reporting Services Configuration Manager click Add Server
The server should now successfully of joined the web farm
Go back to the second Reporting Services Server and Click the Web Portal URL link. Accept all the default values and click Apply
Now test to ensure you can navigate to the Web Portal URL
Encyption Keys
We now need to ensure that the encryption key from the first node is restored on the second node to be able to share encrypted data between the two.
On the Report Server Configuration Manager click Encryption Keys. Click Backup and save it on your network that can be access by the second Reporting Services Server.
Now restore the key on the second Reporting Services Server
Final Test
Now the installation and configuration is all complete, all that there is left to do is to test the Load Balanced URL. Navigate to http://loadbalanceddnsname/Reports
Adding DB’s to the AlwaysOn Cluster
One thing the wizards don’t do is add the Databases to the cluster therefore lets do this.
Open SQL Management Studio on the first SQL server and expand Databases. Right click the ReportingServer DB and click Properties and select Options and ensure the Recovery model is set to Full.
Closer the properties window and right click the ReportingServer DB Tasks > Backup
Click Add and specify the backup location then click OK
Right click the Availability Group and click Add Database
Once the wizard opens click Next on the Introduction screen and On the Select Databases screen select the ReportServer which should state “Meets prerequisites” then click Next
On the Select Initial Data Synchronization step enter a File Share location on your network and click Next
On the Connect to Existing Secondary Replicas step click Connect and connect to the secondary SQL Server and click Next
Ensure the validation passes and click Next
Then click Finish and the ReportServer database should now be added to the AlwaysOn Availability group
Now do the same with the ReportServerTempDB
No comments:
Post a Comment