Wednesday, July 13, 2016

Using SSRS ReportServer database in SQL Multi-Subnet Availability Group

Using SSRS report data sources on a SQL Availability Group that is behind a multi-subnet listener is supported, and it mostly works. 

Having the SSRS ReportServer database on a multi-subnet AG, however, isn't supported, as detailed here.  To wit:
The use of MultiSubnetFailover, with the report server databases, is not supported.

So.  What's one to do if you need a ReportServer DB to follow an application DB that needs to be protected with a Multi-Subnet AG?

Script it.

Happily, when SQL Server transitions to the primary replica in an Availability Group, it writes this to the event log with a specific event ID:  41074.  We can create a scheduled task to trigger from that event and remotely run the RSCONFIG utility to point SSRS to the new primary database server.

Then, to make sure our AG-hosted report data sources get picked up correctly, we'll restart the SSRS service, too.

The Scheduled Task

Here's what the scheduled task looks like:

For the action, we have a batch file that runs.  Here's the contents of that.
rsconfig -c -m -s -d reportserver_DB -a Windows -u domain\username -p <password>

After the batch file runs, we use sc.exe to stop the SSRS service, and then start it (restart isn't an option with SC).

This ensures that RS is always pointing to the primary replica instance of Availability Group, but it doesn't rely on the AG listener, so the system continues to function.