Configure a Native Mode Report Server Scale-Out Deployment
Applies to: ✔️ SQL Server Reporting Services Enterprise Edition (2017 and later) ✔️ Power BI Report Server
Reporting Services native mode supports a scale-out deployment model that allows you to run multiple report server instances that share a single report server database. Scale-out deployments are used to increase scalability of report servers to handle more concurrent users and larger report execution loads. It can also be used to dedicate specific servers to process interactive or scheduled reports.
For Power BI Report Server, you need to configure client affinity (sometimes called sticky sessions or persistence) on the load balancer for any scale-out environment to ensure proper performance and consistent Power BI (PBIX) report functionality.
For SQL Server 2016 Reporting Services and earlier, SharePoint mode report servers utilize the SharePoint products infrastructure for scale-out. SharePoint mode scale-out is performed by adding more SharePoint mode report servers to the SharePoint farm. For information on scale-out in SharePoint mode, see Add an Additional Report Server to a Farm (SSRS Scale-out).
Reporting Services integration with SharePoint is no longer available after SQL Server 2016.
A scale-out deployment is used in the following scenarios:
As a prerequisite for load balancing multiple report servers in a server cluster. Before you can load balance multiple report servers, you must first configure them to share the same report server database.
To segment report server applications on different computers, by using one server for interactive report processing and a second server for scheduled report processing. In this scenario, each server instance processes different types of requests for the same report server content stored in the shared report server database.
Scale-out deployments consist of:
Two or more report server instances sharing a single report server database.
Optionally, a network load-balanced (NLB) cluster to spread interactive user load across the report server instances.
When deploying Reporting Services on an NLB cluster, you need to ensure the NLB virtual server name is used in the configuration of report server URLs and that servers are configured to share the same view state.
Reporting Services does not participate in Microsoft Cluster Services clusters. However, you can create the report server database on a Database Engine instance that is part of a failover cluster.
To plan, install, and configure a scale-out deployment, follow these steps:
Review Install SQL Server from the Installation Wizard (Setup) for instructions on how to install report server instances.
If you are planning to host the scale-out deployment on a network load balanced (NLB) cluster, you should configure the NLB cluster before you configure the scale-out deployment. For more information, see Configure a Report Server on a Network Load Balancing Cluster.
Review the procedures in this topic for instructions on how to share a report server database and join report servers to a scale-out.
The procedures explain how to configure a two-node report server scale-out deployment. Repeat the steps described in this topic to add additional report server nodes to the deployment.
Use Setup to install each report server instance that will be joined to the scale-out deployment.
To avoid database compatibility errors when connecting the server instances to the shared database, be sure that all instances are the same version. For example, if you create the report server database using a SQL Server 2016 report server instance, all other instances in the same deployment must also be SQL Server 2016.
Use the Report Server Configuration Manager to connect each report server to the shared database. You can only connect to and configure one report server at a time.
Use the Reporting Services Configuration tool to complete the scale-out by joining new report server instances to the first report server instance already connected to the report server database.
Use SQL Server Reporting Services Enterprise Edition. See SQL Server Reporting Services features supported by editions for details.
To install a SQL Server instance to host the report server databases
Install a SQL Server instance on a computer that will host the report server databases. At a minimum, install SQL Server Database Engine and Reporting Services.
If necessary, enable the report server for remote connections. Some versions of SQL Server do not enable remote TCP/IP and Named Pipes connections by default. To confirm whether remote connections are allowed, use SQL Server Configuration Manager and view the network configuration settings of the target instance. If the remote instance is also a named instance, verify that the SQL Server Browser service is enabled and running on the target server. SQL Server Browser provides the port number that is used to connect to the named instance.
The service accounts used for the Reporting Services instance are important when dealing with a scale-out deployment. You should do one of the following when deploying your Reporting Services instances.
Option 1: All of the Reporting Services instances should be configured with the same domain user account for the service account.
Option 2: Each individual service account, domain account or not, need to be granted dbadmin permissions within the SQL Server database instance that is hosting the ReportServer catalog database.
If you have configured a different configuration than either of the above options, you may encounter intermittent failures of modifying tasks with SQL Agent. This will show up as an error in both the Reporting Services log and on the web portal when editing a report subscription.
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
The issue will be intermittent is that only the server who created the SQL Agent task will have rights to view, delete or edit the item. If you don't do one of the above options, the operations will only succeed when the load balancer sends all of your requests for that subscription to the server that created the SQL Agent task.
To install the first report server instance
Install the first report server instance that is part of the deployment. When you install Reporting Services, choose the Install but do not configure server option on the Report Server Installation Options page.
Start the Reporting Services Configuration tool.
Configure the Report Server Web service URL, Web Portal URL, and the report server database. For more information, see Configure a Report Server (Reporting Services Native Mode)
Verify that the report server is operational. For more information, see Verify a Reporting Services Installation
To install and configure the second report server instance
Run Setup to install a second instance of Reporting Services on a different computer or as a named instance on the same computer. When you install Reporting Services, choose the Install but do not configure server option on the Report Server Installation Options page.
Start the Reporting Services Configuration tool and connect to the new instance you just installed.
Connect the report server to the same database you used for the first report server instance:
Select Database to open the Database page.
Select Change Database.
Select Choose an existing report server database.
Type the server name of the SQL Server Database Engine instance that hosts the report server database you want to use. This must be the same server that you connected to in the previous set of the instructions.
Select Test Connection, and then select Next.
In Report Server Database, select the database you created for the first report server, and then select Next. The default name is ReportServer. Do not select ReportServerTempDB; it is used only for storing temporary data when processing reports. If the database list is empty, repeat the previous four steps to establish a connection to the server.
In the Credentials page, select the type of account and credentials that the report server will use to connect to the report server database. You can use the same credentials as the first report server instance or different credentials. Select Next.
Select Summary and then select Finish.
Configure the Report Server Web service URL. Do not test the URL yet. It will not resolve until the report server is joined to the scale-out deployment.
Configure the Web Portal URL. Do not test the URL yet or try to verify the deployment. The report server will be unavailable until the report server is joined to the scale-out deployment.
To join the second report server instance to the scale-out deployment
Open the Reporting Services Configuration tool, and reconnect to the first report server instance. The first report server is already initialized for reversible encryption operations, so it can be used to join additional report server instances to the scale-out deployment.
Click Scale-out Deployment to open the Scale-out Deployment page. You should see two entries, one for each report server instance that is connected to the report server database. The first report server instance should be joined. The second report server should be "Waiting to join". If you do not see similar entries for your deployment, verify you are connected to the first report server that is already configured and initialized to use the report server database.
On the Scale-out Deployment page, select the report server instance that is waiting to join the deployment, and select Add Server.
Issue: When you attempt to join a Reporting Services report server instance to the scale-out deployment, you may experience error messages similar to 'Access Denied'.
Workaround: Back up the Reporting Services encryption key from the first Reporting Services instance and restore the key to the second Reporting Services report server. Then try to join the second server to the Reporting Services scale-out deployment.
You should now be able to verify that both report server instances are operational. To verify the second instance, you can use the Reporting Services Configuration tool to connect to the report server and click the Web Service URL or the Web Portal URL.
If you plan to run the report servers in a load-balanced server cluster, additional configuration is required. For more information, see Configure a Report Server on a Network Load Balancing Cluster.
Configure a Service Account
Configure a URL
Create a Native Mode Report Server Database
Configure Report Server URLs
Configure a Report Server Database Connection
Add and Remove Encryption Keys for Scale-Out Deployment
Manage a Reporting Services Native Mode Report Server
More questions? Try asking the Reporting Services forum