Making sense of SQL Azure reporting

  • Article

In order to understand why you might want use the new Reporting Services in SQL Azure you need to understand where it fits and in order to do that you need to know a little bit about how reporting services works, and even what it is if you’re new SQL Server but have perhaps heard of Azure.

The on premise traditional Reporting Services included in SQL Server is a web service which consumes a special xml file with an .rdl  (report definition language) extension to render a report from any data source you have connectivity to from that web service.  The important bit data doesn’t have to be in SQL Server, it could be in Oracle , Terradata, Excel, in xml etc. However SQL Server is used in two ways to support reporting services:

  • The report definition files and associated metadata about permissions, data sources, etc, are stored in a SQL Server database (called ReportServicer by default) associated with the service.
  • A second temporary database (called ReportingServicesTempdb) is used to store snapshots of reports and to do aggregations, sorts etc. on complicated reports.

When a user wishes to run a report the following occurs:

  • the reporting service will show a list of reports that user can choose from in a web portal which can be the default report manager, SharePoint, your own application.
  • the user selects the report
  • The report is run which means that it will execute a query against the various data sources defined in the report.  Optionally this may require the user to enter parameters defined in the report to filter data first.
  • the report is rendered back to the user from where they can elect to save it off in various formats such as excel.

Applying this to SQL Azure Reporting Services:

  • If your source data is in SQL Azure already then running a report in SQL Azure makes a lot of sense as the source data doesn’t have to go anywhere and the only traffic will be down to the end user when they run it.  However if you decide to pull every row out of your 50gb database in a report, and save it to Excel (don’t get me started on saving to Excel!)  it will take time no matter how fast the back end service can render it.
  • If your source data is elsewhere you need to understand that the queries driving the report are going to execute where the source data is and then the results have to be uploaded to reporting services where they are aggregated, calculations are run and the output rendered.  The same is true the other way around – if you are running reporting services locally on your own server and you wish to run a report on SQL Azure then the source data will be pulled down to your local server after the query has executed on the source.

This is probably all stating the obvious, but this behaviour should drive how you decide to use SQL Azure and whether reporting in SQL Azure is right for you. As for actually using it, it is currently in beta which you can sign up for here