SSRS connection pooling between SSRS app and ReportServer database instance
We have an interesting issue. To keep things simple, we have a dedicated database server for the SSRS ReportServer database (SQL Server 2017) and two servers running the SSRS app. No other databases reside on this instance. A while back there were reports that reports would run slowly and eventually error out. Kibana logging showed that we were getting "max pool size was reached" errors.
We initially thought these were referring to the Max Pool Size set in the connection string of the datasource, but the error suggests its actually between the SSRS app code and the SSRS database instance housing the ReportServer database.
Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
We built an agent job on the SSRS database server to capture the number of user connections and this error correlates with high user connections on the SSRS database.
We attempted to reproduce the issue in a development environment by running more than 500 reports at the same time, but we did not note the connections increasing on the SSRS database server. The development setup is different than our production setup however.
Our assumption is that the SSRS application is using the default value for the Max Pool Size between the application and the SSRS database. But we haven't found a way to change this in RSReportServer.config. We assumed that the Max Pool Size setting could be set within the encrypted connection string in the DSN, but we haven't found any documentation that supports that.
Question: Is it possible to change the Max Pool Size between SSRS app and SSRS database? What are some possible solutions? Do we need to scale horizontally?