September 15, 2023 at 4:18 am
Hi,
September 15, 2023 at 7:05 am
SSRS needs to be installed on a separate box ideally.
If SSRS is installed on node1 and node1 is hard down then the SSRS service is offline so you have no reports.
If you have SQL Enterprise you could look at a scaled out deployment where the SSRS is installed on Node1 & Node2 and joined together, there is plenty on the internet if you search for "SSRS Scaled Out Deployment" on setting this up.
Then if node1 is hard down, you have a backup SSRS service on node2 taking the workload, it is in a way setup like a network load balancer.
September 25, 2023 at 6:12 am
Things are a lot easier with Enterprise Edition. IMHO the HA capabilities of EE make it worth paying the additional price. Trying to roll your own stuff compared to having it packaged with EE is likely to be more error-prone and costly.
Back to the OP. Whenever I set up RS or PowerBI in-house on a cluster, it was always done using EE. Also since SQL2017 always install PowerBI in place of RS, you lose nothing and gain a lot. You should also plan to use DNS aliases to allow redirection, so a failover becomes transparent to your users.
Install PowerBI on node 1. Create a listener for PowerBI and also create a DNS alias for your reporting system. Configure the http reservations for the alias name. Then install IIS and configure the Default Document to point to PowerBI via the alias name.
You now have a single-node PowerBI system you can access simply by putting //aliasname into a browser. The default document will connect it up with PowerBI.
Next, install PowerBI on to node 2. Also install IIS, do the http reservations and the IIS default document. Then link the two PowerBI instances into a scale out group, using the same name as the listener. You can now fail over fron node 1 to node 2 and continue to access PowerBI just by using //aliasname
You should really be using https rather than http to get to PowerBI so get this configured. Part of this is changing the default doc to point to https.
If you want 2-site failover then configure a distributed availability group between the the clusters on each site. At this stage you will have two independent PowerBI scale out groups, one per site. You may also be wondering how to make cross-site failover transparent to your SQL db connection strings.
Create a DBS alias for the Distributed Availability Group. Point it to the AG listener name for the active site. Hopefully you will already be connecting to SQL via DNS aliases, so update these so their target is the DAG alias name. Next, update the DNS alias for PowerBI so it's target is the DAG alias name. When you do a DAG failover, you must also manually update the target of the DAG alias to point to the new active AG name. Your PowerBI connection via //aliasname will follow the updated path and land in the right place.
If you use SSAS and configure http access for SSAS, abd use an alias to get to it, you can repoint all your connection strings related to the DAG group simply by repointing the DAG alias. None of your users will know or care which site is active, things will carry on working.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 27, 2023 at 1:26 am
Yes, SSRS resource should be a shared resource on another SQL server for node 1 and node 2 on AG.
Agree with ANT-GREEN notes.
DBASupport
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply