Installing Highly Available SQL 2016 with SP1 for System Center 2016 – Part 3 (Reporting Services on a SQL Cluster)

Now whilst in the last 2 articles we looked at a Highly Available Scaled Out deployment of SQL and Reporting Services, this requires 4 servers coupled with Service Manager Deployment with  5 servers for Service Manager, for example, would total 9 servers!  The aim of this article is to show you how to save money and how we can reduce the SQL Deployment to just 2 servers.  In this article, we will look at how we can install Reporting Services on the two servers that have the SQL Server installed in the AlwaysOn Cluster configuration.

Part 1 – Installing Highly Available SQL 2016 with SP1 for System Center 2016 – Part 1 (SQL Server AlwaysOn Availability Groups)
Part 2 – Installing Highly Available SQL 2016 with SP1 for System Center 2016 – Part 2 (Network Load Balancing Reporting Services)
Part 3 – This Article

This is a brief diagram of what the SQL infrastructure will look like:

Licencing

First, with SQL Server 2016, passive failover scenarios require the primary server to be licensed with Software Assurance. If the secondary server is truly passive — in other words, it’s only used in failover situations and not for anything else — then you don’t need to purchase an additional SQL Server license for that server.

However, if you’re using that server to run any other sort of additional workload, you do need to license it — for instance, if you’re using AlwaysOn Availability Groups to protect the primary server and the secondary server is used for read-only reporting or backups.  So later on in this article I will show you how to ensure that Reporting Services is only being used on one server.

Licensing is an ever changing subject so you should consult with your license provider for your particular scenario.

Installing the Reporting Services Role

Add the Reporting Services role to both the SQL Servers.

Configuring the first SQL Server 2008 2016 Reporting Services instance

Once the Reporting role has been installed click on the Start Menu and 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 SQL server.

From the Start Menu open the Reporting Services Configuration Manager

On the Connection Screen Click Connect

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

Configure a the Cluster for Reporting Services

This step allows the cluster manager to ensure that SSRS is only running on the currently active node. If you wish to run SSRS concurrently on both nodes then both servers have to be licenced!

Open Cluster Failover Manager.

Right click cluster and select Create Empty Role

Right-click your AlwaysOn and select Add Resource > Generic Service and scroll down to select SQL Server Reporting Services.  Click Next through the wizard and click Finish

If necessary right click the SQL Server Reporting Services (MSSQLSERVER) role and Bring Online

Note: it may take a little while!

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

Test Reporting Services URL

Next, open a Browser and navigate to the LISTENER name (eg http://listnername/reports) to test Reporting Services is working correctly.

Now failover the AlwaysOn Availability Group and ensure you can still connect the Reporting Server.

Right click the Availability Group name and click Failover

Test the URL again

You now have Reporting Services integrated with your failover cluste


Part 2                                                                                                                                            Part 1