I am trying to check if the below scenario will help.
Can we point the SSRS to run reports from a mirrored SQL server instead of the primary SQL Server. I have a alternate SQL server that is kept sync with the primary SQL server using transaction log shipping. Now what I am trying to achieve is that I want
my SSRS to execute reports using data in alternat SQL server.
I've investigated this is the past, and it looked theoretically possible, but it looked difficult to maintain. The main configuration steps are to manually modify the data sources in Report Manager, and to esnure the data mirroring leaves the mirrored
database accessible for read-only access. My two concerns with this were:
CRM may update the data sources, and there's no documentation on when this may happen
When the transaction log is applied to the mirrored server, the database is not accessible. It is possible to control the schedule of the log shipping to do this out of business hours, but it does mean the data can be out of date.