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.
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 RSserver.fqdn.com -s DBServer.fqdn.com -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.
Hi Lane, I assume I run this batch file on the new primary replica after i failover
ReplyDeleteYou got it: that event 41074 is generated on the new primary replica, so it'll have to be set up on all replica servers.
DeleteDo I need to have reports config setup on the secondary replica for this to work?
DeleteThe application I am using is set to use the listener for the report server url(http://listener). It works fine but when I failover to secondary replica I am not able to access the page using listener but am able to using the web portal url(http://thsqlreports) that is setup for reports. Is there away to use the listener when failover over to secondary. I can change the application to use http://thsqlreports but is the server goes down not able to access report from new primary replica.
Interesting; are you saying you're able to use the SQL AG listener name as a URL for the reporting services instance, as well?
DeleteIf that's the case, then I'll bet there's a local firewall or other local configuration (SSRS?) piece that still isn't in place on that secondary replica. By default, the AG listener isn't designed to be used as a http end point, but I'm pretty certain you could make this work, as well.
Yes when I am connecting to the primary replica where reports is configured I can use the listener to access the reports page but when I failover to the seconday and that becomes the primary I cant.
DeleteShould I have SSRS configured on the secondary? Its installed but not configured.
Thank you for your feedback on this
Ah, yes: that's it! Yes: if you configure SSRS on that other replica (and you'll need to set it up as a scale-out installation, which always sounded to me to be more impressive than it really is), where you've got two SSRS web servers pointing to a single SSRS database.
DeleteIf your AG replicas are in different subnets (and thus your listener, in effect, has two IP addresses), you'll have to use this scheduled task idea to reconfigure SSRS each time a failover occurs. And you'll need to make sure the scheduled task hits each of the two SSRS web servers.
If, on the other hand, your listener just has one IP address, you shouldn't need to make any SSRS changes when a failover happens.
I don't have it configured on the other replica as I want the secondary to be passive. I don't have money to license both.
DeleteMy AG replicas are in different subnets. Is it possible to still have this work with only 1 SSRS servers
Ah, gotcha. This will totally work with only 1 SSRS server, but you won't be able to use the AG listener as the URL: the primary replica will be the owner of that network name, and it'll be the only one to respond to that name. So whichever server is the primary, that'll be the only one that can respond to the request.
DeleteIf that (currently listener) URL is the one you want to use for SSRS, you'll want to create a new name for the AG listener (which will only be used on the back-end, anyway). That way, when the AG nodes switch roles, the SSRS URL still will be pointing to the one SSRS server.
Create a new name where for the AG listener?
Deleteedirne
ReplyDeletehatay
ığdır
ısparta
muş
HE7