1

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

2

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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).
  • At HandyD What if i create a separate AG fro the reports database on Node1 (Example ReportAG) then fail it over to Node 2. Surely i can the reports from Node 2 and take the workload off Node1. obviously that is taking onto account Node2 is fully licensed. – Daniel May 23 at 14:35
  • Yes, that would work. That is an Active-Active architecture, where both nodes are running a primary workload. As you've stated, both must be licensed. Again, you must set your read-only routing URLs and connection strings to offload the main workload for your reports to the readable secondary otherwise you'll get minimal benefit from offloading the SSRS databases to a new replica, or you'll have to hard code secondary replica names in your conn strings, which means they won't failover with the AG. – HandyD May 24 at 0:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.