Ensuring Smooth SSRS Subscriptions in an Always On Availability Group

Introduction

SQL Server Reporting Services (SSRS) is a powerful tool for managing and delivering reports in an enterprise environment. When configuring SSRS within a SQL Server Always On Availability Group, it’s essential to take specific steps to ensure that SSRS subscriptions continue to function properly during failovers. This blog outlines the key actions required to maintain seamless SSRS operations in such a setup.

Configuring Shared Data Sources

To ensure that SSRS reports consistently connect to the current primary replica, configure shared data sources to point to the Availability Group listener. This setup allows SSRS reports to automatically connect to the active primary replica, ensuring continuous data availability.

Steps to Point SSRS to the Availability Group Listener:

1. In the SSRS Report Manager, navigate to the shared data sources used by your reports.
2. Edit each shared data source.
3. In the connection string, replace the server name with the Availability Group listener name.
   • Example: Replace Server=myServerAddress;Database=myDataBase; with Server=myAGListener;Database=myDataBase;
4. Test the connection to ensure that it correctly resolves to the primary replica.

This configuration ensures that whenever a failover occurs, SSRS reports will continue to connect to the current primary replica through the listener.

Setting Up SSRS with Availability Groups

SSRS does not natively support Always On Availability Groups, but you can configure it to use the Availability Group listener for its report server database. By doing so, SSRS reliably connects to the primary replica, regardless of which replica is active, maintaining continuous access to the report server database.

Steps:

1. Open the SSRS Configuration Manager.
2. Under the “Database” section, change the server name to the Availability Group listener name.
3. Apply the changes and verify that SSRS can connect to the report server database through the listener.

Handling Failover Scenarios

During a failover, the secondary replica becomes the new primary. The Availability Group listener automatically redirects connections to the new primary, minimizing disruption. However, after a failover, SSRS subscriptions may appear, but the corresponding jobs may not execute correctly until the SSRS service is restarted on the new primary replica.

Managing Scheduled Jobs

Scheduled jobs within SSRS are managed internally within the SSRS database. However, because job definitions are stored in the MSDB database, they are not replicated across replicas. In an Always On AG setup, each replica has its own MSDB database and SSRS service, resulting in multiple instances of scheduled jobs. This design distributes workloads across SSRS services, even during failovers.

Restarting SSRS Service Post-Failover

SSRS subscription jobs are recreated each time the SSRS service starts. To ensure that these jobs are executed correctly after a failover, it’s necessary to restart the SSRS service. The following PowerShell script can automate this process:

$serviceName = "SQLServerReportingServices"
$service = Get-Service -Name $serviceName

if ($service.Status -eq 'Running') {
    Restart-Service -Name $serviceName -Force
} else {
    Start-Service -Name $serviceName
}

Ensure that the SSRS service user is included in the MSDB database role (RSExecRole) on each replica to maintain proper functionality.

Considerations During Failover

In the event of a planned failover, connections are gracefully redirected to the new primary replica, allowing SSRS subscriptions to continue without interruption. During an unplanned failover, there may be a brief disruption in SSRS subscriptions until the failover is complete and connections are reestablished.

Monitoring and Testing

Regular monitoring of both the Availability Group and the SSRS instance is important to quickly identify and address any issues during failover events. Setting up alerts for failover events and potential issues with SSRS subscriptions can help ensure timely responses. Periodically testing failover scenarios is also recommended to confirm that the configuration works as expected and that SSRS subscriptions continue to operate without significant disruption.

Considerations for Scale-Out Deployment

In a scale-out deployment with multiple SSRS servers, it is important to ensure that all instances are properly configured to connect to the Availability Group listener. This setup will help maintain consistent operations across all SSRS instances.

Conclusion

By following these guidelines, you can maintain the reliability and functionality of SSRS subscriptions within a SQL Server Always On Availability Group. Proper configuration, regular monitoring, and testing are key to preventing disruptions and ensuring continuous SSRS operations during failover events.