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:

image

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

image_thumb29

Click Web Service URL then click Apply to accept the defaults

image_thumb30

You should see success in the Results pane

image_thumb31

Now click on Database then click Change Database

image_thumb32

Click Create a new report server database and click Next

image_thumb33

Enter the name of your AlwaysOn Availability group LISTENER and click Test Connection and ensure it succeeds.  Then click Next

image_thumb34

Rename the database if you wish and click Next

image_thumb35

Accept the defaults for the credentials and click Next

image_thumb36

On the Summary click Next

Once the wizard has ran and everything was successful click Finish

image_thumb37

Click the Web Portal URL link. Accept all the default values and click Apply

image_thumb38

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

image_thumb39

 

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

image_thumb40

Select the Choose an existing report server database option and click Next

image_thumb41

Enter the name of your AlwaysOn Availability group LISTENER and click Test Connection and ensure it succeeds.  Then click Next

image_thumb42

Select your ReportServer DB from the drop down menu and click Next

image_thumb43

Accept the defaults for the credentials and click Next

image_thumb44

On the Summary click Next

Once the wizard has ran and everything was successful click Finish

image_thumb46

 

 

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

image_thumb47

 

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

image_thumb48

The server should now successfully of joined the web farm

image_thumb49

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

image_thumb50

 

 

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.

image_thumb51

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

image

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

Note: it may take a little while!

image

 

 

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.

image_thumb[1]

Closer the properties window and right click the ReportingServer DB Tasks > Backup

Click Add and specify the backup location then click OK

image_thumb[2]

Right click the Availability Group and click Add Database

image_thumb[3]

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

image_thumb[4]

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

image_thumb[5]

Then click Finish and the ReportServer database should now be added to the AlwaysOn Availability group

image_thumb[6]

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.

image

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

Right click the Availability Group name and click Failover

image

Test the URL again

image

You now have Reporting Services integrated with your failover cluster Smile