This article is contributed. See the original author and article here.

Hosting SSRS databases in Azure SQL Managed Instance

 

This article outlines the spectrum of options available for migrating your solutions based on the SQL Server Reporting Services (SSRS) to Microsoft cloud, with focus on the newly-introduced support for hosting SSRS databases in Azure SQL Managed Instance.

 

Modernizing at your own pace

 

In times of uncertainty, it’s important to have a flexibility to take approach that makes the most sense for your business. Some organizations slow down to operate more cost-effectively and play as safe as possible, while others lean into new opportunities that didn’t exist before.

Same goes for the appetite for modernization while migrating to Microsoft cloud: you may decide to simply lift and shift your solutions, and still benefit instantaneously from certain capabilities available in cloud only, or you can aim for migrating to cloud native SaaS and PaaS solutions that may require adopting new concepts, but also may be way more rewarding in terms of the modernization, out-of-the-box capabilities offered, and new scenarios unlocked.

 

SQL Server vs. SQL Server database engine

 

Azure SQL as a family of managed, secure, and intelligent SQL database services is a good example of flexibility offered when migrating your solutions to Azure. The entire Azure SQL family is built upon the same SQL Server database engine, so your skills and experience transfer easily to the cloud. Still, you are in control of the level of change you want to introduce, by choosing one of the available database services – SQL Server on Azure Virtual Machine, SQL Managed Instance, or SQL Database.

However, your solutions may be leveraging SQL Server capabilities beyond its database engine as a core component. Let’s name a few most used ones:

Integration Services (SSIS) – platform for building data integration and transformation solutions,

Analysis Services (SSAS) – analytical data engine with tabular and multidimensional mode,

Reporting Services (SSRS) – tools and services for creating and managing paginated reports.

 

For each of these powerful and widely adopted SQL Server capabilities there is a choice of options for migration to Microsoft cloud: you can always opt for the simple lift-and-shift approach and host them on the SQL Server on Azure Virtual Machine, resembling the layout from your on-premises environment, or you can decide to leverage equivalent cloud native offerings.

 

When it comes to hosting SQL Server Reporting Services (SSRS) in the Microsoft cloud, there are two main options:

• Moving your SSRS paginated reports to the Power Bi Premium service
• Deploying SQL Server Reporting Services virtual machine

The second option has a couple of variations, and to be able to differentiate between the variations we should note that SSRS consists of two components: report server which is a stateless engine of the SSRS, and report server databases storing metadata and temporary report results.

 

SSRS uses two databases to separate persistent data storage from temporary storage requirements. The databases are created together during the installation of SSRS and bound by name. By default, the database names are ReportServer and ReportServerTempDB, respectively.

Earlier mentioned variations pertain to where report server databases are being hosted, and we’ll explain the difference as we go through the options.

 

Moving your SSRS paginated reports to the Power Bi Premium service

 

SSRS paginated reports are optimized for printing or PDF generation, providing you with the ability to produce highly formatted, pixel-perfect layouts. Typical use case for paginated reports are operational reports like sales invoice, or profit and loss statement.

In contrast, typical Power BI report is optimized for exploration and interactivity – you can easily slice and dice data, discover relationships and patterns, in other words it’s ideal for analytical reports.

 

Obviously, both types of reports remain important and having both types in one place, sharing the infrastructure of Power BI service, including the search, monitoring and auditing capabilities provides a great benefit. You can also drill through from the Power BI report to a paginated report, embed paginated report, or share its content through a Power BI app. Check the Paginated reports in Power BI FAQ for more details.

 

To migrate your existing paginated reports in the Report Definition Language (RDL), you can use RDL Migration Tool written by Microsoft and available on GitHub. More detailed guidance on the entire migration process is provided in the Migrate SQL Server Reporting Services reports to Power BI article.

There are currently few limitations that you may need to circumvent to be able to migrate your SSRS reports to Power BI. Also, note that publishing paginated reports in Power BI requires Premium subscription.

 

Deploying SSRS on SQL Server on Azure Virtual Machine

 

If you are rather interested in a quick lift and shift of your existing SSRS solution, you can simply deploy SSRS on Azure Virtual Machine, just like on any (virtual) machine on premises. Here you have a full freedom of choice among the supported version of Reporting Services, so you can even use the same version that you are using on premises.

 

You can reuse the same Azure Virtual Machine for SQL Server database engine to store report server databases. Choosing a local instance is useful if you have sufficient system resources or if you want to save on software licenses. On the other hand, running the report server databases on another instance of SQL Server can improve performance.

 

While SQL Server on Azure Virtual Machine offers some automated manageability features like automatic backups and security patching, note that other aspects like configuring and maintaining high availability through Always On Availability Groups are not provided by the platform.

 

(New) Hosting report server databases in Azure SQL Managed Instance

 

This leads us to the variation that you may find useful if you are fan of fully managed database services, eliminating need for managing the database engine manually. With this approach, your stateless report server installed on Azure Virtual Machine connects to report server databases hosted on Azure SQL Managed Instance. High availability of databases here comes out of the box, with 99.99% availability SLA. You can leverage auto-failover groups as a disaster recovery solution for the most critical solutions, or geo-restore capability for less critical ones. You can also use point-in-time restore (PITR) functionality to recover from accidental report changes.

 

If your SSRS reports are pulling the data from databases hosted on Azure SQL Managed Instance, it’s reasonable to host report server databases on the same instance, thus minimizing the database engine footprint.

 

SQL Server 2019 Reporting Services come with native support for hosting report server databases in Azure SQL Managed Instance – just point your SSRS to the instance during the setup, or re-point the existing installation using SSRS Configuration Manager. You can also restore a backup of your report server database from on-premises SQL Server on Azure SQL Managed Instance.

 

If you’d rather keep the older supported version of SSRS that you are using on premises, you can still host report server databases on Azure SQL Managed Instance. You should just configure the instance before installing legacy version of SSRS on Azure Virtual Machine. Configuration steps are very simple and ensure that some of the commands issued during the installation process by legacy versions of SSRS, that has been eliminated in SSRS 2019, are safely ignored by SQL Managed Instance:

 

 

 

-- Turn the advanced configuration options on:
sp_configure 'show advanced options', 1 ; 
GO
RECONFIGURE; 
GO

-- Enable suppression of error messages for recovery model change:
sp_configure 'suppress recovery model errors', 1 ; 
GO
RECONFIGURE; 
GO

 

 

Visit Suppress recovery model errors server configuration option to learn more about this configuration option.

 

Conclusion

 

There are multiple options to choose among when deciding on the approach for moving your SSRS solution(s) to Microsoft cloud. Options cover the entire spectrum from Infrastructure-as-a-Service (IaaS) solutions offering completely the same experience as with on-premises SQL Server enriched with some management automation options, through leveraging Platform-as-a-Service (PaaS) solution for hosting report server databases on Azure SQL Managed Instance (newly-introduced option), all the way to Software-as-a-Service (SaaS) solution with SSRS paginated reports in Power BI service. This flexibility allows you to modernize at the pace that makes the most sense for your business.

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.