SSRS Reports running from a separate server from SQL OLTP DB server

As a first step - it could help but won't be as helpful as you think. First - if you stand up a new server in Azure, you may find that performance is worse. It all depends on where that Azure server is and the latency between networks.

The stored procedures and queries that generate the data for the reports would still be running on the primary node. It is those queries that are contributing to performance issues in the application - since they take up IO, CPU and memory resources on that node.

To offload that performance, you need a separate instance on a separate node where you can run those queries. Using a read-only secondary is part of that - because that offloads that IO/CPU/memory to a different host and won't impact the production system.

To truly improve performance of SSRS - you need to build out tables and other structures to support the reports. These can be data marts, data lakes - cubes - etc...

From just an environment point of view - a read-only secondary with a separate reports database. To make SSRS redundant, you have several options - a standard windows cluster with shared storage is one option. Another is using VMWare (vmotion) - if something happens to the VM host the guest can be moved to another host. Azure also provides redundancy (I believe).

The problem with placing SSRS databases (ReportServer and ReportServerTempDB) in an AG is that you must manually restart the reporting services service after a failover. That service doesn't automatically redirect to a secondary even if configured to point to a listener. Moving those to a dedicated SSRS instance reduces the downtimes to only when that dedicated server is down (patching for example).

My guess is that report procedures running on the primary will still be slow - because they are competing with resources and access from the application itself. It may also be the lack of appropriate indexes to support the report queries. If that is the case, it makes more sense to build specific tables with appropriate indexes in a reporting database - which can be automated to refresh on a daily basis.

2 Likes