Install SSRS ReportServer Databases on Azure SQL Managed Instance


By:   |   Updated: 2020-10-29   |   Comments (1)   |   Related: More > Azure


Problem

If your company is like mine, they are migrating applications to SaaS/PaaS offerings when possible. Whether it is applications, databases or something else, these offerings limit administrative overhead for environments that are already experiencing resource constraints. One of the things that would be nice to move to the cloud is the SSRS Report Server databases.

Solution

Enter the Azure SQL Managed Instance offering. While this service is a couple years old, Microsoft continues to mature the offering and allow more applications to work on the platform.

One of the most recent is the use of Azure SQL Managed Instance to host the ReportServer databases for use with SQL Server Reporting Services (SSRS).  This might seem like a small feature addition to the product, for people like me that are on a fast track to migrate everything to Azure SQL Managed Instance this checks another box off the short list.

Pre-Requisites

  1. Azure SQL Managed Instance
  2. SQL Server Account - Used for connecting to the Azure SQL Managed Instance
  3. Azure Virtual Machine - Same Azure Subscription as Azure SQL Managed Instance

SQL Server Reporting Services Installation Steps

Download the 2019 SSRS installer here.

From your Azure Virtual Machine, launch the installer from the location you saved the download to.

Choose Install Reporting Services and click Next.

install reporting services

Choose the appropriate Edition to match your licensing.  In this example we will use the Developer Edition.  Once selected choose Next.

install reporting services

Now you will want to accept the license and click Next.

Choose Install Reporting Services Only and click Next. This will be your only option since the SQL Server Database Engine is not on the server.

install reporting services

Change the Installation Location to a path of your choice, if you would like, then click Install.

Once the installer finishes click the Configure Report Server button to open the settings.

install reporting services

When the Report Server Configuration Manager screen opens click Connect.

install reporting services

Once the main screen loads choose Database and then click Change Database to enter the connection information.

configure reporting services

Since we are building a new SSRS installation we will keep the default option selected and then click Next.

configure reporting services

Enter the database information:

  1. Server Name = Azure SQL Managed Instance
  2. Authentication Type = SQL Server Account
  3. Username = SQL Server Account login on the Azure SQL Managed Instance
  4. Password = Password that goes with your SQL Server Account
  5. Test Connection
configure reporting services

Once your connection test is successful, click Next.

On the database configuration you should leave the default of ReportServer then click Next.

Again, since we are using a SQL Server Account the account information needs to be entered:

  1. Authentication Type = SQL Server Credentials
  2. User name = SQL Server Account Login on the Azure SQL Managed Instance
  3. Password = Password that goes with your SQL Server Account
configure reporting services

Click Next to proceed to the summary screen, verify all your settings and click Next to begin the database setup.

configure reporting services

After clicking Next, you will see the installation progress.  Once complete you will see Success for each step, then click Finish.

configure reporting services

Voila, just like that we have ReportServer and ReportServerTempDB on our Azure SQL Managed Instance.

Before After
database list
database list

Now that we have the ReportServer databases deployed, setup the Reporting Services Web Server components for executing reports.

Back in the Report Server Configuration Manager screen choose Web Service URL, then click Apply. This will apply all the default settings for the Web Service.

configure reporting services

The Web Service configuration is complete when you see "The task completed successfully" under the Results section.

web service status

Now click Web Portal URL, then click Apply to accept the defaults and start the Web Portal configuration process.

configure reporting services

The Web Portal configuration is complete when you see "The task completed successfully" under the Results section.

web portal configuration

While on the Web Portal URL screen, click the URLs link to launch your web browser to your newly configured SSRS server.

web portal configuration

Just like that you have an SSRS server configured to use an Azure SQL Managed Instance PaaS server.

ssrs home

That was a simple and straightforward installation / configuration process.

Bonus Tip #1: Create an SSRS report

Open Visual Studio 2019.

Note: if you need help configuring Visual Studio for Report Server click here.

Click Create New Project.

Type Report in search box then choose Report Server Project Wizard and choose Next.

new project

Configure the new project by entering a Project Name and location then click Create.

new project

The Report Wizard will start, click Next.

The first step in the report creation will be to add a data source, enter the Name and click Edit.

new data source

Now we will enter the connection information for the data source:

  1. Server Name = Azure SQL Managed Instance
  2. Authentication = SQL Server Authentication
  3. User name = SQL Server Account Login on the Azure SQL Managed Instance
  4. Password = Password that goes with your SQL Server Account
  5. Test Connection

Once the test is successful, click Ok to save Connection.

data source

Back on the Data Source Screen choose Next.

Since we are going to do a simple report that just lists the databases on our Azure SQL Managed Instance these steps will be simple.

In the query string section enter SELECT * FROM sys.databases then click Next.

For the Report Type let us use the default of Tabular and click Next.

Choose some of your favorite columns from sys.databases and push them to the right under the Details section.

I choose some of my favorites and then clicked Next.

design table

The final step is to give the report a name.  I called my example DemoTest_MI_DatabaseList.

Once you click Finish, you will see that all the heavy lifting of report build is done for you.

Now the Report Project has a Data Source and an RDL report file.

report layout

If you want to Preview the report, in the preview window, a list of databases from the Azure SQL Managed Instance will load.

report preview

You have a working report.

Bonus Tip #2: Deploy Report to SSRS server

It is now deployment time for our new Database List report.

Right click on the SSRS_MI_DemoTest project name and choose Properties.

deploy report

The TargetServerURL will need to be changed to match name of the server running our SSRS server, then click OK.

deploy report

Now that our deployment settings are configured, click on Build > Deploy Solution.

deploy report

The project will build and deploy based on your configuration.  The Output window will show deployment successful.

deploy report

Login to your server or access it from a connected network machine at http://servername/reports.

From the Home screen we will see the SSRS_MI_DemoTest folder. Click the folder and you will be taken to the DemoTest_MI_DatabaseList report.

ssrs home ssrs home

The moment of truth is here.  Run the DemoTest_MI_DatabaseList report.

We have a list of databases from our Azure SQL Managed Instance.

sample ssrs report
Next Steps


Last Updated: 2020-10-29


get scripts

next tip button



About the author
MSSQLTips author Garry Bargsley Garry Bargsley is a SQL Server Database Administrator with over 20 years experience in the technology field. His interests and specializations are SQL, Azure, PowerShell and Automation.

View all my tips
Related Resources





Comments For This Article




Thursday, October 29, 2020 - 10:06:14 AM - Wanderlei Santos Back To Top (86715)
Maybe it's because I haven't had my coffee yet, but if we still need to install SSRS instance on the vm isn't that still IAAS? We also need a separate SQL license to install SSRS.


download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Continuous database deployments with Azure DevOps

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks














get free sql tips
agree to terms