Reporting Services with AlwaysOn Availability Groups (SQL Server)

This topic contains information about configuring Reporting Services to work with Always On Availability Groups (AG) in SQL Server 2014. The three scenarios for using Reporting Services and Always On Availability Groups are databases for report data sources, report server databases, and report design. The supported functionality and required configuration is different for the three scenarios.

A key benefit of using Always On Availability Groups with Reporting Services data sources is to leverage readable secondary replicas as a reporting data source while, at the same time the secondary replicas are providing a failover for a primary database.

For general information on Always On Availability Groups, see AlwaysOn FAQ for SQL Server 2012 (https://msdn.microsoft.com/sqlserver/gg508768).

Requirements for using Reporting Services and AlwaysOn Availability Groups

To use Always On Availability Groups with SQL Server 2014 Reporting Services, you need to download and install a hotfix for .Net 3.5 SP1. The hotfix adds support to SQL Client for AG features and support of the connection string properties ApplicationIntent and MultiSubnetFailover. If the Hotfix is not installed on each computer that hosts a report server, then users attempting to preview reports will see an error message similar to the following, and the error message will be written to the report server trace log:

Error message: "Keyword not supported 'applicationintent'"

The message occurs when you include one of the Always On Availability Groups properties in the Reporting Services connection string, but the server does not recognize the property. The noted error message will be seen when you click the 'Test Connection' button in Reporting Services user interfaces and when you preview the report if remote errors are enabled on the report servers.

For more information on the required hotfix, see KB 2654347A hotfix introduces support for the AlwaysOn features from SQL Server 2012 to the .NET Framework 3.5 SP1.

For information on other Always On Availability Groups requirements, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

Report Data Sources and Availability Groups

The behavior of Reporting Services data sources based on Always On Availability Groups can vary depending on how your administrator has configured the AG environment.

To utilize Always On Availability Groups for report data sources you need to configure the report data source connection string is to use the availability group Listener DNS name. Supported data sources are the following:

• ODBC data source using SQL Native Client.

• SQL Client, with the .Net hotfix applied to the report server.

The connection string can also contain new AlwaysOn connection properties that configure the report query requests to use secondary replica for read-only reporting. Use of secondary replica for reporting requests will reduce the load on a read-write primary replica. The following illustration is an example of a three replica AG configuration where the Reporting Services data source connection strings have been configured with ApplicationIntent=ReadOnly. In this example the report query requests are sent to a secondary replica and not the primary replica.

The following is an example connection string, where the [AvailabilityGroupListenerName] is the Listener DNS Name that was configured when replicas were created:

Data Source=[AvailabilityGroupListenerName];Initial Catalog = AdventureWorks2008R2; ApplicationIntent=ReadOnly

The Test Connection button in Reporting Services user interfaces will validate if a connection can be established but it will not validate AG configuration. For example if you include ApplicationIntent in a connection string to a server that is not part of AG, the extra parameter is ignored and the Test Connection button will only validate a connection can be established to the specified server.

Depending on how your reports are created and published will determine where you edit the connection string:

• Native mode: Use Report Manager for shared data sources and reports that are already published to a native mode report server.

• SharePoint Mode: Use SharePoint configuration pages within the document libraries for reports that are already published to a SharePoint server.

• Report Design: SQL Server Report Builder for SQL Server 2012 or SQL Server Data Tools (SSDT) when you are creating new reports. See the 'Report Design' section in this topic or more information.

Additional Resources:

• Manage Report Data Sources

• For more information on the available connection string properties, see Using Connection String Keywords with SQL Server Native Client.

• For more information on availability group listeners, see Create or Configure an Availability Group Listener (SQL Server).

Considerations: Secondary replicas will typically experience a delay in receiving data changes from the primary replica. The following factors can affect the update latency between the primary and secondary replicas:

• The number of secondary replicas. The delay increases with each secondary replica added to the configuration.

• Geographic location and distance between the primary and secondary replicas. For example the delay is typically larger if the secondary replicas are in a different data center than if they were in the same building as the primary replica.

• Configuration of the availability mode for each replica. The availability mode determines whether the primary replica waits to commit transactions on a database until a secondary replica has written the transaction to disk. For more information, see the 'Availability Modes' section of Overview of AlwaysOn Availability Groups (SQL Server).

When using a read-only secondary as a Reporting Services data source, it is important to ensure that data update latency meets the needs of the report users.

Report Design and Availability Groups

When designing reports in SQL Server Report Builder for SQL Server 2012 or a report project in SQL Server Data Tools (SSDT), a user can configure a report data source connection string to contain new connection properties provided by Always On Availability Groups. Support for the new connection properties depends on where a user previews the report.

• Local preview: SQL Server Report Builder for SQL Server 2012 and SQL Server Data Tools (SSDT) use the .Net framework 4.0 and support Always On Availability Groups connection string properties.

• Remote or server mode preview: If after publishing reports to the report server or using preview in SQL Server Report Builder for SQL Server 2012, you see an error similar to the following, it is an indication you are previewing reports against the report server and the .Net Framework 3.5 SP1 Hotfix for Always On Availability Groups has not been installed on the report server.

Error message: "Keyword not supported 'applicationintent'"

Report Server Databases and Availability Groups

Reporting Services 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.

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.

Differences between SharePoint Native Mode

This section summarizes the differences between how SharePoint mode and Native mode report servers interact with Always On Availability Groups.

A SharePoint report server creates 3 databases for each Reporting Services service application you create. The connection to the report server databases in SharePoint mode is configured in SharePoint Central Administration when you create the service application. The default names of the databases include a GUID that is associated with the service application. The following are example database names, for a SharePoint mode report server:

• ReportingService_85c08ac3c8e64d3cb400ad06ed5da5d6

• ReportingService_85c08ac3c8e64d3cb400ad06ed5da5d6TempDB

• ReportingService_85c08ac3c8e64d3cb400ad06ed5da5d6_Alerting

Native mode report servers use 2 databases. The following are example database names, for a native mode report server:

• ReportServer

• ReportServerTempDB

Native mode does not support or use the Alerting databases and related features. You configure native mode report servers in the Reporting Services Configuration Manager. For SharePoint mode you configure the service application database name to be the name of the "client access point" you created as part of the SharePoint configuration. For more information on configuring SharePoint with Always On Availability Groups, see Configure and manage SQL Server availability groups for SharePoint Server (https://go.microsoft.com/fwlink/?LinkId=245165).

Prepare Report Server Databases for Availability Groups

The following are the basic steps of preparing and adding the report server databases to an Always On Availability Groups:

• Create your Availability Group and configure a Listener DNS name.

• Primary Replica: Configure the report server databases to be part of a single availability group and create a primary replica that includes all of the report server databases.

• Secondary Replicas: Create one or more secondary replicas. The common approach to copying the databases from the primary replica to the secondary replica(s) is to restore the databases to each secondary replica using 'RESTORE WITH NORECOVERY'. For more information on creating secondary replicas and verifying data synchronization is working, see Start Data Movement on an AlwaysOn Secondary Database (SQL Server).

• Report Server Credentials: You need to create the appropriate report server credentials on the secondary replicas that you created on the primary. The exact steps depend on what type of authentication you are using in your Reporting Services environment; Window Reporting Services service account, Windows user account, or SQL Server authentication. For more information, see Configure a Report Server Database Connection (SSRS Configuration Manager)

• Update the database connection to use the Lister DNS Name. for native mode report servers, change the Report Server Database Name in Reporting Services configuration manager. For SharePoint mode, change the Database server name for the Reporting Services service application(s).

Steps to complete disaster recovery of Report Server Databases

The following steps need to be completed after a Always On Availability Groups failover to a secondary replica:

1. Stop the instance of the SQL Agent service that was being used by the primary database engine hosting the Reporting Services databases.

2. Start SQL Agent service on the computer that is the new primary replica.

3. Stop the Report Server service.

   If the report server is in native mode, stop the report server Windows server using Reporting Services configuration manager.

   If the report server is configured for SharePoint mode, stop the Reporting Services shared service in SharePoint Central Administration.

4. Start the report server service or Reporting Services SharePoint service.

5. Verify that reports can run against the new primary replica.

Report Server Behavior When a Failover Occurs

When report server databases failover and you have updated the report server environment to use the new primary replica, there are some operational issues that result from the failover and recovery process. The impact of these issues will vary depending on the Reporting Services load at the time of failover as well as the length of time it takes for Always On Availability Groups to failover to a secondary replica and for the report server administrator to update the reporting environment to use the new primary replica.

• The execution of background processing may occur more than once due to retry logic and the inability of the report server to mark scheduled work as completed during the failover period.

• The execution of background processing that would have normally been triggered to run during the period of the failover will not occur because SQL Server Agent will not be able to write data into the report server database and this data will not be synchronized to the new primary replica.

• After the database failover completes and after the report server service is re-started, SQL Server Agent jobs will be re-created automatically. Until the SQL agent jobs are recreated, any background executions associated with SQL Server Agent jobs will not be processed. This includes Reporting Services subscriptions, schedules, an snapshots.

See Also

SQL Server Native Client Support for High Availability, Disaster Recovery AlwaysOn Availability Groups (SQL Server) Getting Started with AlwaysOn Availability Groups (SQL Server) Using Connection String Keywords with SQL Server Native Client SQL Server Native Client Support for High Availability, Disaster Recovery About Client Connection Access to Availability Replicas (SQL Server)