A few days ago, I set up my very first SQL Always On cluster. It would be a very straightforward task, just two databases in a 2 basic availability groups and making sure one fails over with the other. Besides that, the customer also asked if we could let the report server fail over at the same time. Sure, no problem… except that this is an enterprise-only feature. Let’s see how we can achieve this on a standard edition.

Preparations

In this post, I am going to assume you already have:

• Windows failover cluster with 2 nodes
• SQL Server installed on both nodes (Exactly the same)
• A database in a basic availability group with a listener
• PowerShell v3 (recommended, v2 is the minimum) for DbaTools

Time to roll up your sleeves

As you can see, we start with two nodes (Node1 and Node2). We have a basic availability group AG_SQL_TSQL on database TSQLV4 and a listener LSNR_TSQL. In my example, I’m using Windows Server 2016 and SQL Server 2017. This will work for all versions above SQL Server 2012.

First, we will have to install the report servers on both nodes. When we got the report server installed on both nodes, we have to get the report server database synchronized. At last, we will put all of this in a simple script, so we don’t have to do all of this manually.

Reporting service

In my installation I have left everything default, just to keep things simple. If you want to use different URL’s for the services, make sure you configure the second node exactly the same.

We will need the encryption key from the first node. Go to encryption keys and backup the key. You can already copy this key to your second node. I suggest you put the folder with the key near the data file of your report server database.

When you installed and enabled your report service, you should now be able to see your reports when you go to http://<NameNode1>:80/Reports.

Now we will configure reporting services the same on Node2 as we did on Node1. We don’t need to back up the encryption key from Node2. When you have done everything correctly, you will be able to go to http://<NameNode2>:80/Reports.

Getting them in sync

We are now running a separate reporting service on both our nodes. Since we have a basic availability group with a listener, we are able to connect to the reporting service from the primary node through the listener of the availability group.
All you have to do is connect to the URL  http://<ListenerName>:80/Reports. In my case, this becomes http://lsnr_tsql:80/Reports.

When we connect to the URL while Node2 is primary, we will see the folder that we created on Node2.

This is nice but not really, what we needed. We want to see the same reports on both nodes.

An easy way to do this is a backup/restore from the primary database. The data in both nodes will be the same and the listener will switch between nodes on a failover.

Now we stop SSRS on the Node2 and restore the database with a full backup from Node1.

Make sure you select the options to overwrite the existing database.

Now we have the same data on both nodes and the connection will switch between nodes when they failover. Seems like we are done, or not? When we enable SSRS again and try to connect to http://LSNR_TSQL/Reports, we receive this error message.

To resolve this issue we have to delete the key entry in the database from Node2 and restore the key we saved from Node1.

Edit the table dbo.Keys and delete the records with the keys on Node2.

When we deleted the key from the database, we can now restore the key. Open SSRS Manager on Node2 and go to Encryption Keys. Click on restore and select the key file from Node1.

When you go to http://LSNR_TSQL/Reports now, you will see the same reports on both nodes. However, there is a drawback to this. Since we only synchronize from Node1 to Node2, reports can only be created on Node1.

Script it!

I don’t think any of you want to sync their reporting services once or twice a day by hand. Don’t worry, we can write a script to manage all this for us. Since I am more experienced in PowerShell for the time being, I wrote a small PowerShell scripts to do this heavy task for me. Even if you have no knowledge of PowerShell, it will be very easy to follow.

To make the script I will be making use of DbaTools. If you havn’t heard from DbaTools as a Dba, just stop reading and check their website http://dbatools.io. It is a very powerful PowerShell module, which allows you to do most Dba-tasks in a single line of code.

Let’s explain the script. We want it to do 5 things:

• Stop the SSRS service on Node2
• Restore Node1’s backup on Node2
• Start the service back up
• Delete the Key entry in the database
• Restore the encryption key from Node1 on Node2

If you don’t use the backup script from Ola Hallengern (https://ola.hallengren.com) then you just need to remove the switch “MaintenanceSolutionBackup” from the Restore-DbaDatabase function and set the path to the full backup from Node1 yourself.

You are able to schedule this script using Windows Task Scheduler or anything similar.

The argument in my case will be:

-ExecutionPolicy Bypass C:\SQLData\Scripts\Sync_ReportServer.ps1

Below I will give you the script I used to synchronize the report server. You need to fill in the parameters on the last line and schedule it. If you have done everything correctly, it should now synchronize the report server for you.

 
Function Sync-ReportServer {
    [CmdletBinding()]
    param (
        [string]$password,
        [string]$computerName,
        [string]$backupPath,
        [string]$keyPath
    )

    process {
        # Stop reporting service
        Stop-DbaSqlService -ComputerName $computerName -Type SSRS

        # Restore reporting database
        $backup = Get-ChildItem -Path $backupPath | Sort-Object LastAccessTime -Descending | Select-Object -First 1
        Restore-DbaDatabase -SqlServer $computerName -DatabaseName ReportServer -Path "$($backupPath)\$($backup)" -WithReplace -IgnoreLogBackup -MaintenanceSolutionBackup

        # Delete key
        Invoke-Sqlcmd2 "Delete from dbo.Keys where MachineName IS NOT NULL;" -ServerInstance $computerName -Database "ReportServer"

        # Start service
        Start-DbaSqlService -ComputerName $computerName -Type SSRS

        # Restore key
        rskeymgmt.exe -a -f $keyPath -p $password -i $computerName
    }
}

Sync-ReportServer -computerName '' -backupPath '' -keyPath '' -password ''

The script should take most of the burden off your shoulders. However, I wouldn’t recommend to use this solution when you edit or create reports constantly.
Thank you for reading, hopefully until next time!