In SQL Server Always On 2019 environment should SSRS/SSIS be on a separate server?

Question

I am looking for some advice in setting up a new SQL Server 2019 Always On instance.

We have a 2 server cluster right now, moving to Always On with a similar two virtual server setup.

I want to know if that is recommended.

We have about 200 databases, maybe 40 used per day.

We have about 350 SSRS reports run on average per day, up to max of 1100 in the past couple months. We have about 50 SSIS jobs (packages) that run, mostly every night.

Should SSRS and SSIS be on a separate server? Or should the SSRS temp databases be on the SQL Always On servers but the SSRS install be on the separate server?

Answer 1

The documentation at Reporting Services with Always On Availability Groups (SQL Server) states:

Reporting Services and Power BI Report Server offers limited support for using Always On availability groups with report server databases. The report server databases can be configured in AG to be part of a replica; however Reporting Services will not automatically use a different replica for the report server databases when a failover occurs. The use of MultiSubnetFailover, with the report server databases, is not supported.

Manual actions or custom automation scripts need to be used to complete the failover and recovery. Until these actions are completed, some features of the report server may not work correctly after the Always On availability groups failover.

So in general, you won't get any benefit of putting the reporting service database in an availability group without figuring out what needs to be done after a failover occurs.

Using SSIS with an availability group is supported and you can review the pertinent information at SSIS Catalog.

In general, putting them on a standalone server will be easier. If you have hardware redundancy at the VM host layer, you are getting almost no value out of putting them in an availability group, particularly if your VMs are on SSDs that can reboot in about 20 seconds.

Answer 2

The first issue you'll run into is installing SSRS on multiple nodes. In my dev environment, I installed SSRS on EACH NODE first because it places permissions in both master and msdb databases.

These need to exist and hence you cannot do a standard install and then copy or rather backup the ReportServer and ReportServerTempDB databases as you would a normal "add a database" to AlwaysOn.

Once you have completed the install on every node, then you can drop the databases on the replica sets and continue adding them into the AlwaysOn process.

Then you had to manually go back and assign the vip:80/Reports as your web interface on all your replicas. Of course, when you failover, this may hang as stated above. In my environment I had to turn off the agent of the primary node that failed over to the replica. I had to leave it off for 1-2 min and then the web interface came back.

Reporting Services Always On AG for more details. Reporting Services Database on AG also for more details.

Manual actions or custom automation scripts need to be used to complete the failover and recovery. Until these actions are completed, some features of the report server may not work correctly after the Always On availability groups failover.

I think this should be a BIG WARNING. But, like always, test, test, test and test again. Then you wonder, why didn't I just install this on a single server?

As our team discussed, even IF we got this to work, we dont get any "automatic" advantages as either our team or the applications team would have to be on-call for every failover to make sure the script "runs" or the web interface is up. Although we can do this, it adds complexity to maintainability in the future. What happens when you want to do an upgrade in-place? You can't just only do the databases, but now you have to add in Reporting Services and all the complex work you initially did.

This may be fine, but perhaps you might be elsewhere and some poor Joe gets this task in 10 years.

So make it easy for yourself, your team, and the future Joe's who have to support this database and application: Install SSRS on its own server.

Just my 2 cents.

Andy