Azure hybrid solutions with on-premise SSRS Server

For the moment, SSRS (SQL server reporting services) is currently not an Azure component. Will it be available in the near future…? I don’t know. SSRS- functionalities become available in other dashboards and reporting tools like Power BI. Today, you’ll need to work with an on-premise installed SSRS server or you’ll have to setup one in an Azure VM. But essentially, these are the same situations, a standard native SSRS server installation. When building your HYBRID cloud platform, you can integrate on-premise SSRS server or VM with SSRS in your architecture. Not an easy task to do? Well, depending on … like always.

SSRS standard reports and reporting functionalities visualize business application data stored on a lot of different places. But they are accessible by defining the correct data source in your report solution. So far, no issues using data stored in Azure services. Some other SSRS features are a little complicated to tackle. Today I want to show you one method to use for the integration of PDF generation, defined in data driven subscriptions on your current reporting server installation.

Case definition:

• On-premise SSRS 2017 installation
• Existing data-driven subscription for generating PDF documents.
• All generated PDF’s must be stored in Azure blob-storage as the archive
• For some files, also send to a mail destination

Case purpose:

• Use as much as possible from the existing processes.
• Business functionality untouched
• Generate new PDF on request (continued to store it in the archive and mail it directly)

Case solution:

A high-level overview of the necessary things to do when you want to achieve this scenario.

A short explanation of this, giving the step by step executed processing parts:

1. Press button ‘print invoice’ – reopen current report/invoice with flag ‘button pressed’ on report server portal
2. Report opens, and datasets are re-executed à procedure ‘Print Invoice’ on report server
   1. Insert print request data in application database by executing procedure ‘Print Invoice’ on azure
      1. Add print invoice request in a table on azure database
   2. Start subscription ‘Mail On Request’ by adding trigger to SSRS events database on report server
   3. Start subscription ‘Print On Request’ by adding trigger to SSRS events database on report server
3. SSRS subscription event ‘Mail On Request’ is triggered and PDF is sent with PDF attachment using Database Mail on report server
4. SSRS subscription event ‘Print On Request’ is triggered and PDF is created on File-Share$ on report server – record inserted in SQL File-Table ‘Printed Invoices’
5. Trigger on SQL file-table ‘printed Invoices’ is fired
   1.  starts SQL-agent-job ‘flag print request’ on report server (executes as ‘SSRS execution account’ needed because switch security context not allowed to azure)
6. SQL job-step executes stored procedure ‘Print Invoice Flag Request Done’ on report server
   1. Store pdf to Azure Blob by executing SQL procedure ‘Load Invoice To Azure Blob’ on report server
      1. Executes AZCopy statement using xp_cmdshell on report server
   2. Flag print request by using procedure ‘update Print Invoice Request’ on report server
      1. Update tale ‘Print Invoice Request’ on azure
   3. Delete PDF from file-hare$ on report server

A lot of  security parts where needed to set it up, but there are a lot of components involved too. Be aware that you can/may not always switch the security context at any time in the process. (specific to Azure)

Conclusion:

I know it is possible to do this with the current available services and architecture models, but I must admit that the complexity increases. Nevertheless, it will not be blocking you for continuing your Azure migration.

Creating some additional Power-BI dashboards can assist you on maintaining this implementation.