Author: Shubhi Jain, Associate Data Engineer
A Server based reporting service from Microsoft works on SQL server on which it is hosted. This SQL Server can be either local or remote.
Supported SQL Server Data Engine on which SSRS can be hosted are:
- Azure SQL Managed Instance
- SQL Server 2019
- SQL Server 2017
- SQL Server 2016(13.X)
- SQL Server 2014(12.X)
- SQL Server 2012(11.X)
In this topic we will look at how we can use Azure SQL Managed Instance as host for SSRS
Pre-requisites
- Azure SQL Managed Instance
- SQL Server Account for connecting to Azure SQL Managed Instance
- Azure SQL Virtual Machine
Install SSRS on Azure SQL Virtual Machine
Go to SQL Server Installation Centre under installation click on Install SQL Server Reporting Services.
A link to get the SSRS installer will open download it from there and install SSRS by following the steps.
Once the installation has been done open Report Server Configuration Manager from start menu and click on connect.
Main window of Report Server Configuration will open. In the left pane click on Database and then click on Change Database
Select Create a new Report server and click next.
Enter the database information of Azure Managed Instance with SQL Authentication click on test connection once the connection is successfully tested click on next.
Enter name of the database and click next
Since we are using SQL Server Account so it will ask for credentials. Enter the details and click next
It will enter to the summary page where all the details which we have entered will appear review it click next.
After clicking next you will see installation in progress once it is done click on finish.
ReportServer and ReportServerTempDB these 2 databases will be made on the Azure SQL Managed Instance. Connect with SSMS and check it.
Now when ReportServer database has been deployed successfully in Report Server configuration manager got to Web Service URL all the default values has been filled for you click on apply to link the URL services for the reports.
Now go to Web Portal URLs and click on apply by accepting all the defaults.
Now the SSRS has been set up to work with Azure SQL Managed Instance. Install SSDT tools like Microsoft Visual Studio to make reports and deploy them on the Web URL which we have configured above.
Also we can use Power BI Integration to develop reports for the databases through SSRS.
