How to make Reporting console part of high Availability in SCOM

disclaimer:
In this blog post, I will share a possible solution for a problem that I encountered in my work. However, I want to make it clear that this solution is not officially supported by Microsoft, and it may have some risks or side effects that I am not aware of. Therefore, use this solution at your own discretion and responsibility.
                (I repeat, this is NOT SUPPORTED !!!)

SCOM (System Center Operations Manager) is a powerful tool for monitoring and managing your IT infrastructure. One of the features of SCOM is the Reporting console, which allows you to create and view reports on various aspects of your environment, such as performance, availability, alerts, and configuration.

However, the Reporting console is not part of the SCOM high availability (HA) solution by default as per the following.

As per the following article from SSRS, Reporting service cannot be a part of failover cluster.

Host a Report Server Database in a SQL Server Failover Cluster – SQL Server Reporting Services (SSRS) | Microsoft Learn

Although the Report Server service cannot participate in a failover cluster, you can install Reporting Services on a computer that has a SQL Server failover cluster installed. The report server runs independently of the failover cluster. If you install a report server on a computer that is part of a SQL Server failover instance, you are not required to use the failover cluster for the report server database; you can use a different SQL Server instance to host the database.

As per HA for SSRS, they can do the following, however SCOM doesn’t support this. The reason is SCOM reporting install its own custom Security extensions.

High availability in SQL Server Reporting Services – SQL Server Reporting Services (SSRS) | Microsoft Learn

While SQL Server Reporting Services supports a scale-out deployment model that allows you to run multiple report server instances that share a single report server database, it isn’t supported with Operations Manager. Operations Manager Reporting installs a custom security extension as part of the setup of the front-end components, which can’t be replicated across the web farm.

Designing for High Availability and Disaster Recovery | Microsoft Learn

In SCOM, if you want to repoint to another SSRS, you need to uninstall the reporting role and reconfigure it.

How to move the Reporting Server role | Microsoft Learn

This means that if the server hosting the Reporting console fails, you will lose access to your reports until you restore or rebuild the server. This can be a problem if you rely on reports for troubleshooting, auditing, or compliance purposes.

Fortunately, there is a way to make the Reporting console part of the SCOM HA solution. In this blog post, we will show you how to do it in a few simple steps.

how to make Reporting server databases part of AG

1. A standalone server i.e., HV-HOST22-01\INST1 where SSRS is installed, and reporting service is running. A step-by-step process on how to install SSRS.

Now I will configure SSRS on HV-HOST22-01\INST1 and create the database on this server i.e., HV-HOST22-01\INST1.

Now click on Change Database and Select the task “Create a new Report server database”.

1. Now provided the server’s name i.e., HV-HOST22-01\INST1 and click on Test Connection and click Next.

Now Provide the database name like SCOMReportServer and click Next.

Now Provide the Credentials, in this case it would be SCOM reader account.

Then Click Next, Next and Finish.

Now we will install the Reporting console role on HV-HOST22-01\INST1. A step-by-step process is here

How to Install the Operations Manager Reporting Server | Microsoft Learn

Now once reporting console is installed on HV-HOST22-01\INST1, now will create couple of schedule reports as well and take the backup of the encryption keys.

Now we have made the recovery as full for both databases i.e., SCOMReportserver database and SCOMReportservertemp database. We have also taken a full backup for both the databases.

Now add the databases in AG and while selecting the secondary replica, please make sure to use the following option.

Now both the DBs are part of AG.

Now change the database string to listener in to the SSRS configuration manager on server HV-HOST22-01\INST1.

Now click on Change Database and Select the task “Choose an existing Report server database”.

1. Now provided the listener’s name i.e., SCOMListener,1400 and click on Test Connection and click Next.

Now select the database name from the drop-down list i.e. SCOMReportServer and click Next.

Now Provide the Credentials, in this case it would be SCOM reader account.

Then Click Next, Next and Finish.

Now open the SCOM console and access reporting, it will work like below.

Now do a failover to the secondary replica and check.

1. First of all, please take a backup of existing SQL SRS Report databases and please take a backup of SQL SRS Encryption key backup of SQL SRS instance.

For example, I have taken backup for the databases from server instance HV-HOST22-01\INST1 (Primary Replica) and taken encryption key backup from the SQL SRS instance HV-HOST22-01\INST1\SSRS.

1. Now do a failover to the Secondary replica i.e. HV-HOST22-02\INST1 for the report server databases.

1. While configuring the SQL SRS on server HV-HOST22-02\INST1(Secondary Replica SRS server), click on Change Database and Select the task “Choose ab existing Report server database”.

1. Now Provide the SQL instance of HV-HOST22-02\INST1 and do a Test Connection.
   

1. Now Select the Report Server database and Click Next.

1. Now provide the credential for SCOM data Reader account and click Next >> Next and Finish.

1. IF URLs are not accessible, need to follow the following steps.

1. if URLs are not accessible on SSRS server HV-HOST22-02\INST1 and we have the following error.

1. We will be using ResetSRS.exe to reset the extensions to default.
   
2. So, open CMD with elevate admin privilege and go to the SCOM setup file directory. But it will fail with the following error because we won’t be able to install Reporting on top of Always on configuration. Here you need to provide the credential of SCOM reader account.

C:\Users\administrator.CONTOSO>cd “C:\SQL\System Center Operations Manager\SupportTools\amd64” C:\SQL\System Center Operations Manager\SupportTools\amd64>ResetSRS.exe ssrs System Center Operations Manager — Reset SQL Reporting Server Tool Copyright (C) Microsoft. All rights reserved. Please enter the account to use for Setting up database connection contoso\omread Please enter the password to use for Setting up database connection The account is validated, now we will try and restore this SRS instance.   Unhandled Exception: System.Data.SqlClient.SqlException: The operation cannot be performed on database “ReportServer” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. ALTER DATABASE statement failed.    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()    at System.Data.SqlClient.SqlDataReader.get_MetaData()    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)    at System.Data.SqlClient.SqlCommand.ExecuteScalar()    at Microsoft.EnterpriseManagement.Mom.ReportingTools.ResetSRSTool.ResetSRS(String instanceId)    at Microsoft.EnterpriseManagement.Mom.ReportingTools.ResetSRSTool.Main(String[] args)   C:\SQL\System Center Operations Manager\SupportTools\amd64>

1. So, we will remove the reporting server databases from AG now.
   
2. Now run the ResetSRS.exe command again and it should work now. Here you need to provide the credential of SCOM reader account.

1. Now check the URLs and they should be accessible.
   
2. Now install the reporting console role on server HV-HOST22-02\INST1.

1. Now allow the SCOM extension on the SSRS as per the following article.

Operations Manager reports fail to deploy – Operations Manager | Microsoft Learn

Now restore the Report server databases.

Now restore the encryption key as well.

Now check in SCOM console and check the reports. It should be accessible now.

If accessible, then please connect to SQL instance HV-HOST22-01\INST1 and delete both the Report Server databases.

Now add the Report server databases in the AG.

Now on SRS server HV-HOST22-02\INST1, Open Report Server Configuration Manager and reconfigure the databases by Selecting the task “Choose an existing Report server database” followed by providing the listener’s name.

Now we have reporting console installed on both sides. If we want to switch between datacentre reporting console, we can simply run the following SQL query against OperationsManager database and reporting will be available. It would be working like switch on one of the reporting by updating the information in the database and turn off the other one.

Select * from GlobalSettings where ManagedTypePropertyId = ‘8033F6CD-5C04-8771-BEDA-EE46D3FA660C’     Update GlobalSettings Set SettingValue = ‘http://HV-HOST22-01:80/ReportServer‘ where ManagedTypePropertyId = ‘8033F6CD-5C04-8771-BEDA-EE46D3FA660C’

We might need to restore encryption key again.