Is SSRS on Azure happening? Well as always in Azure it depends… If you mean that ‘SQL Service Reporting Services’ is going to be provided on Azure, the answer is NO as SSRS is a product name as well as a part of SQL Server. But Reporting on Azure is definitely something, first announced in July that SSRS Reports is planned to be supported in Power BI and now… it’s amongst us… it’s called Paginated Reports and it’s now available in public preview (November 7th 2018).
As more and more clients were asking to go to the cloud, there was always something holding them back. At first SSIS wasn’t possible in the cloud, now we have Datafactory (V2), Ok great!
But what if the client has a large and widely developed On Prem SQL environment, including SSIS, what will be one hell of a job getting that up to speed? Here is where Managed Instance comes in mind.
Managed Instance to the rescue
Managed instance in basically your On Prem SQL machine fully available on the Azure environment including SQL Agent, cross database querying and in combination with SSIS IR, you’ll be able to work your SSIS Catalog from the Management Studio as if it was never lifted into the clouds. All of this is also pretty new to Azure but gives back some possibilities that were lost in the current PaaS solutions.
Fantastic, but we still have one more elephant in the room, our static reports… Until now the solution we had in mind for this customer was to bring everything into Azure with Managed Instance and spin up a small Azure VM with Reporting Services to comply with the demand. But let’s take a look at the facts.
You’ll need to pay for the Managed Instance, a full blown SQL machine in fact, this doesn’t come in cheap because you pay for it just to have it exist, no matter if you are using it or not. The Reporting VM would also most likely need a SQL License and you need to pay for the uptime of the VM as well.
In a nutshell, this wouldn’t be very cost effective for the client. In this case the client is better of keeping an Azure VM with the full blown SQL functionalities.
A game Changer
But what Microsoft has announced now is in fact a game changer!
Now we can combine a Managed Instance with cloud reporting, no more need for that small VM with its own license, this is worth investigating.
In the public preview now available, you’ll need to have a Power BI Premium. But there are plans to bring this functionality to Power BI Embedded as well, for PRO there isn’t really clear information.
What’s currently available on the preview:
- Create highly formatted, print-ready reports with headers, footers, and a myriad of layout options.
- Connect directly to data sources like Azure SQL Database, Microsoft SQL Server (via gateway), and SQL Server Analysis Services (via gateway).
- Generate documents in formats like Word, PDF, Excel, etc.
- Preview and print reports from your browser across different page sizes and layouts.
- Show different report views using parameters.
- Share reports with other users across the organization.
- View reports in the Power BI mobile Apps
This all is already great news but until now it’s missing some key features that are available in SSRS:
- Scheduling and sending reports using Email (including attachments) or store on disk
- Select data from data sources then the Microsoft sources s.a. Oracle, MySQL, etc.
- Drill though functionalities
- Sub reports
- Shared data sources
- Shared datasets
Conclusion: It’s without doubt something to keep in mind for helping clients get fully emerged into the Azure cloud environment with SQL Server. For now, the downside is that some basic functionalities are not supported yet and therefore my opinion is to wait it out. But for me this definitely is the holy grail in the making and I’m sure this would convince many others to move towards Azure in combination with the managed instance scenario to keep on making the best data driven decisions!
If you have a Power BI Premium at your disposal give it a try: https://docs.microsoft.com/en-us/business-applications-release-notes/october18/intelligence-platform/power-bi-service/rdl-reports