Sql-server – SSRS on 2008 R2 and its Database on 2016

SSRS Planning a Deployment would be a good article to read through.

You can read specifically on the SSRS databases here. As it states:

A report server is a stateless server that uses the SQL Server Database Engine to store metadata and object definitions. A Reporting Services installation uses two databases to separate persistent data storage from temporary storage requirements. The databases are created together and bound by name. By default, the database names are reportserver and reportservertempdb, respectively.

You general, with large reporting requirements, will run SSRS databases on one server (shared SQL Server instance) and then the SSRS components on another. Keep in mind though how licensing works, you will have two licensed SQL Server servers with this type of setup. I normally see the report server databases reside on the same server as a data warehouse. I have not seen any issue with the SSRS databases being on a shared instance with the data warehouse. As it states in the article they are only storing information for SSRS (execution log, schedules, report definitions, etc.).

The "meat" of what SSRS does resides with the SSRS component services. SSRS processes the data the report is requesting and then also works out rendering it, all of this is resource intensive if there is a high load. It will depend on what your specific needs are if it justifies a standalone server. I generally will see SSRS installed on the web server with the application that uses the reports.

To determine if I need a standalone report server I would probably consider things like number of reports (many small or many large), the frequency (internal report server for monthly reporting or serving up user reports for external web application). You may also look at the report design or standard used by the developers, if the reports do a high amount of data processing on the report side, versus letting the database engine do it.

You can use SSRS 2016 and fetch data from another databases.

When you defines the connection string of data sources, you can use whatever of your installed drivers.

According to MS Docs:

Built-in data extensions

Default data extensions in Reporting Services include the following types of data connections:

Microsoft SQL Server
Microsoft SQL Server Analysis Services
Microsoft SharePoint List
Azure SQL Database
Microsoft SQL Server Parallel Data Warehouse
OLE DB
Oracle
SAP NetWeaver BI
Hyperion Essbase
Teradata
XML
ODBC

Microsoft BI Semantic Model for Power View: On a SharePoint site that has been configured for a Power Pivot gallery and Power View, this data source type is available. This data source type is used only for Power View presentations. For more information, see Building the Perfect BI Semantic Tabular Models for Power View.