Greeting . I have a question. I have set up an AlwaysOn availability groups configuration for DR. Meaning I have a node1 in Site A and a Node2 in site B acting as a DR geographical let say 50 mile apart. The Mode is set Async, Can i read SSRS report from the Node2 ? Am using SQL Server 2016 enterprise If so how ? Thanks in advance
You can set an Asynchronous secondary to be readable and connect to it for read-only workloads. See the documentation here.
There are, however, a number of considerations for this:
- You must licence the secondary replica. Read workload counts as production workload, so you must have a fully licensed secondary and not rely on Software Assurance coverage which provides a free warm standby licence.
- Your data, in an Async replica, may not be current with production. Depending on your latencies, this could result in your reports returning incorrect data. You need to understand your current latency averages and determine if this is acceptable.
- You said "Can i read SSRS report from the Node 2?" - If you mean can you run reports from a report server database in the Availability Group from the secondary, no you cannot. SSRS must still access the ReportServer databases in an AG on the Primary Replica. If instead, you meant can you point your SSRS report datasource at a secondary database, yes you can.
- You should ensure your read-only routing URLs and lists are configured correctly so that you can utilise read-only routing without hard-coding secondary replica names in connection strings. This helps future-proof against architectural changes over time (new replicas added, server name changes etc).