Skip to content

reporting-services-with-always-on-availability-groups-sql-server.md

Latest commit

 

History

History
181 lines (107 loc) · 19.8 KB

reporting-services-with-always-on-availability-groups-sql-server.md

File metadata and controls

181 lines (107 loc) · 19.8 KB
title Reporting Services with availability groups
description Learn about configuring SQL Server Reporting Services (SSRS) with Always On availability groups.
ms.custom seo-lt-2019
ms.date 05/17/2016
ms.prod sql
ms.reviewer
ms.technology high-availability
ms.topic conceptual
helpviewer_keywords
Reporting Services, AlwaysOn Availability Groups
Availability Groups [SQL Server], interoperability
ms.assetid edeb5c75-fb13-467e-873a-ab3aad88ab72
author MashaMSFT
ms.author mathoma
manager erikre

Reporting Services with Always On Availability Groups (SQL Server)

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]

This topic contains information about configuring [!INCLUDEssRSnoversion] to work with [!INCLUDEssHADR] (AG) in [!INCLUDEssCurrent]. The three scenarios for using [!INCLUDEssRSnoversion] and [!INCLUDEssHADR] 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 [!INCLUDEssHADR] with [!INCLUDEssRSnoversion] 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 [!INCLUDEssHADR], see Always On FAQ for SQL Server 2012 (https://msdn.microsoft.com/sqlserver/gg508768).

Requirements for using Reporting Services and Always On Availability Groups

[!INCLUDEssCurrent] [!INCLUDEssRSnoversion] and Power BI Report Server uses the .Net framework 4.0 and supports [!INCLUDEssHADR] connection string properties for use with data sources.

To use [!INCLUDEssHADR] with [!INCLUDEssRSnoversion] 2014, and earlier, 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 [!INCLUDEssHADR] properties in the [!INCLUDEssRSnoversion] 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 [!INCLUDEssRSnoversion] 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 Always On features from SQL Server 2012 to the .NET Framework 3.5 SP1.

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

Note

[!INCLUDEssRSnoversion] configuration files such as RSreportserver.config are not supported as part of [!INCLUDEssHADR] functionality. If you manually make changes to a configuration file on one of the report servers, you will need to manually update the replicas.

Report Data Sources and Availability Groups

The behavior of [!INCLUDEssRSnoversion] data sources based on [!INCLUDEssHADR] can vary depending on how your administrator has configured the AG environment.

To utilize [!INCLUDEssHADR] 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 Always On 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 [!INCLUDEssRSnoversion] 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 = AdventureWorks2016; ApplicationIntent=ReadOnly

The Test Connection button in [!INCLUDEssRSnoversion] 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 the [!INCLUDEssRSWebPortal-Non-Markdown] 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: [!INCLUDEssRBnoversion] or [!INCLUDEssBIDevStudioFull] when you are creating new reports. See the 'Report Design' section in this topic or more information.

Additional Resources:

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 Always On Availability Groups (SQL Server).

When using a read-only secondary as a [!INCLUDEssRSnoversion] 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 [!INCLUDEssRBnoversion] or a report project in [!INCLUDEssBIDevStudioFull], a user can configure a report data source connection string to contain new connection properties provided by [!INCLUDEssHADR]. Support for the new connection properties depends on where a user previews the report.

  • Local preview: [!INCLUDEssRBnoversion] and [!INCLUDEssBIDevStudioFull] use the .Net framework 4.0 and support [!INCLUDEssHADR] connection string properties.

  • Remote or server mode preview: If after publishing reports to the report server or using preview in [!INCLUDEssRBnoversion], 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 [!INCLUDEssHADR] has not been installed on the report server.

Error message: "Keyword not supported 'applicationintent'"

Report Server Databases and Availability Groups

Reporting Services and Power BI Report Server offers limited support for using [!INCLUDEssHADR] with report server databases. The report server databases can be configured in AG to be part of a replica; however [!INCLUDEssRSnoversion] 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 [!INCLUDEssHADR] failover.

Note

When planning failover and disaster recovery for the report server databases, it is advised you always backup a copy of the report server encryption key.

Differences between SharePoint Native Mode

This section summarizes the differences between how SharePoint mode and Native mode report servers interact with [!INCLUDEssHADR].

A SharePoint report server creates 3 databases for each [!INCLUDEssRSnoversion] 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 [!INCLUDEssRSnoversion] 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 [!INCLUDEssHADR], see Configure and manage SQL Server availability groups for SharePoint Server (https://go.microsoft.com/fwlink/?LinkId=245165).

Note

SharePoint mode report servers use a synchronization process between the [!INCLUDEssRSnoversion] service application databases and the SharePoint content databases. It is important to maintain the report server databases and content databases together. You should consider configuring them in the same availability groups so they failover and recover as a set. Consider the following scenario:

  • You restore or failover to a copy of the content database that has not received the same recent updates that the report server database has received.
  • The [!INCLUDEssRSnoversion] synchronization process will detect differences between the list of items in the content database and the report server databases.
  • The synchronization process will delete or update items in the content database.

Prepare Report Server Databases for Availability Groups

The following are the basic steps of preparing and adding the report server databases to an [!INCLUDEssHADR]:

  • 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 Always On 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 [!INCLUDEssRSnoversion] environment; Window [!INCLUDEssRSnoversion] 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 Listener DNS Name. for native mode report servers, change the Report Server Database Name in [!INCLUDEssRSnoversion] configuration manager. For SharePoint mode, change the Database server name for the [!INCLUDEssRSnoversion] service application(s).

Steps to complete disaster recovery of Report Server Databases

The following steps need to be completed after a [!INCLUDEssHADR] 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 [!INCLUDEssRSnoversion] 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 [!INCLUDEssRSnoversion] configuration manager.

    If the report server is configured for SharePoint mode, stop the [!INCLUDEssRSnoversion] shared service in SharePoint Central Administration.

  4. Start the report server service or [!INCLUDEssRSnoversion] 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 [!INCLUDEssRSnoversion] load at the time of failover as well as the length of time it takes for [!INCLUDEssHADR] 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 [!INCLUDEssRSnoversion] subscriptions, schedules, an snapshots.

See Also

SQL Server Native Client Support for High Availability, Disaster Recovery
Always On Availability Groups (SQL Server)
Getting Started with Always On 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)