SALES: 1-800-867-1380

SQL Reporting

Updated: May 9, 2014

Windows Azure SQL Reporting is a cloud-based reporting service for the Platform built on SQL Server Reporting Services technologies.

ImportantImportant
SQL Reporting service is available to current subscribers, but should not be used for new software development projects. The service will be discontinued on October 31, 2014. See this FAQ for details.

An alternative to SQL Reporting is to use one or more instances of SQL Server Reporting Services (SSRS) running on Azure Virtual Machines (VM). Using a VM, you can deploy an operational reporting solution in the cloud that supports either the Native or SharePoint mode feature set. A VM with SQL Server 2008 R2 or 2012 supports all Reporting Services features, including all supported data sources, customization and extensibility, and scheduled report execution and delivery.

In this topic:

Customers who have transitioned from SQL Reporting to SSRS VMs have done so for the following reasons:

Extensible report server for customized reporting

SSRS on a VM supports custom code and assembly references in a report. If business requirements for a report include unique complex function evaluation or proprietary visual controls, you can provide these capabilities in code embedded in a report file or in an assembly added to the report server. Similarly, developers can replace or supplement report server operations by adding custom extensions. See Custom Code and Assembly References in Expressions and Reporting Services Extensions for details.

Scheduled report execution and delivery

In addition to on-demand reporting, SSRS on a VM supports scheduled report processing so that you can retrieve data on a schedule, allowing you to control query execution on a remote database and the timing of data transfer on your network. Scheduled reports can be delivered in various output formats, to destinations other than a report server, such as email or a file share, where the report is saved as PDF, Excel, or MHTML. See Schedules and Subscription and Delivery.

Integration with hybrid solutions

You can join an Azure VM to your corporate network, adding capacity quickly, without the burden of hardware procurement and provisioning. Joining an Azure VM to your domain requires a virtual network and dedicated VPN routing device. See Azure Virtual Network Overview for more information.

Whether you build an entire network in the cloud, or join VMs to an existing on-premises network, an Azure VM can use Windows authentication to support single sign on. Depending on how you configure reports, you might want to use Windows identities to authenticate to a report server or backend database. Identity delegation is further enabled when all VMs are joined to a virtual network that includes a domain controller and an Active Directory domain, and services are configured for Kerberos constrained delegation. Doing so enables the same Windows identity to flow from a client application, to the report server, and finally to the backend database to retrieve permissioned data that is relevant to the original requestor.

Faster performance

Customers who have performed side-by-side testing experience better performance using SSRS on a VM. Performance gains are attributed to having the report server catalog reside on the local disk of the VM. The gain was more apparent on report servers handling larger workloads.

noteNote
A known issue of SQL Reporting is that report execution is slower than SSRS running on premises. In contrast, performance of SSRS on a VM is similar to the performance you might expect from an on-premises SSRS instance.

If you have a new software development project that requires operational reporting, we recommend that you evaluate an Azure VM running SSRS. The Azure Virtual Machine gallery provides images that contain pre-installed SQL Server editions that include the database engine and SSRS. The SQL Server editions in the gallery have the same features found in an on-premises report server installation. Alternatively, you can use a Windows PowerShell script and installation media for SQL Server editions you procure separately.

The following table provides an approach you can use to evaluate a cloud-based VM reporting solution for new software development projects:

 

Step Description Link

1

Before you start, learn about the basic capabilities of an Azure VM by watching the videos and clicking the Explore links on the Virtual Machine page on the WindowsAzure.com web site.

Azure Virtual Machines

2

Compare licensing costs between a predefined image and Windows Server VM running a licensed copy of SQL Server that you purchase and install separately on the VM. Depending on which SQL Server features you require, you might find it more cost-effective to purchase a Windows VM and SQL Server (Enterprise, Standard or Web edition) separately. In that case, you might want create a .vhd in-house using the installation media of the licensed copy of SQL Server, and then attach the disk to your Windows VM.

As alternative to SQL Reporting, you can use the Standard edition of SQL Server, but you might choose other editions depending on the feature requirements and workloads.

Compare licensing fees using the pricing calculator

3

Choose the report server mode and features that best satisfy business requirements. The report server mode will determine which authentication subsystems and authorization models are available. While Native mode is closest to SQL Reporting, SharePoint mode provides out-of-box support for claims authentication, multi-tenancy, and load balancing.

Note that claims identity cannot be flowed to most backend data sources that exist outside of the SharePoint environment, so if you use claims, realize that stored credentials of a single user identity will most likely be required for backend data access.

Compare features between report server modes

Authenticate to a report server (see also Microsoft BI Authentication and Identity Delegation)

4

Confirm your decisions about deployment, provisioning, report server mode, and features through proof-of-concept testing. Proof-of-concept testing includes building and publishing simple reports that allow you to validate connections from client applications so that you can test configuration, authentication, and authorization behaviors. During preliminary testing, retrieve enough data in each report to understand the expected latency for data retrieval and rendering, especially if you are testing a hybrid solution that combines cloud and on-premises services.

Create a VM

Connect to a VM

Publishing Data Sources and Reports

Authorize users

5

Finally, evaluation should include a review of high availability and scalable architectures that might be necessary to support a large volume of users or report executions.

Deploy SQL Server Business Intelligence in Azure Virtual Machines

If you currently use SQL Reporting in an existing software solution, you will need to replace it with an alternative technology by October 2014. We recommend an Azure VM running SSRS in Native mode. Native mode SSRS provides equivalent report execution and rendering capability, but also adds scheduled report execution, customizations, and subscription delivery that SQL Reporting does not provide. If your objective is to replace SQL Reporting, the Standard edition offers comparable functionality.

Choosing an SSRS VM preserves your existing investment in report design. Because the report engine is identical between the two platforms, reports that run in SQL Reporting will also run on an SSRS VM. A VM approach changes the billing model. You are not charged for VMs that are turned off, so if you only need report execution at certain times, you can export a report to a static format, such as PDF, and then stop the VM after the reports are generated.

To migrate reports to a VM, you can deploy a report server project to SSRS on a VM, setting the target server to the VM endpoint. For instructions on how to configure SSRS, set endpoints, configure the firewall, and publish and test reports, see SQL Server Business Intelligence in Azure Virtual Machines.

noteNote
When using a gallery image, SSRS is installed but not configured. You must configure the server and create a new report server database for SSRS on a VM. You cannot detach or copy an existing report server database running in an Azure data center.

Although reports run as before, other aspects of a transition will require replacement functionality or manual changes. For instance, you will need to replace report server authentication. Also, moving from an online service to a VM will introduce changes in how client applications connect to a report server. At a minimum, you will need to update the endpoint used on the connection.

Report server authentication

SQL Reporting uses a proprietary cookie-based authentication module for authenticating user identity. Authentication is built-in, with no separate service to configure and maintain. A VM running SSRS does not have the custom authentication module used by SQL Reporting. As such, moving to the VM platform will require that you choose one of the authentication subsystems supported by SSRS: Windows authentication or Forms-based authentication.

Replacing the built-in authentication module is an important design decision. Forms-based authentication module is the nearest match to SQL Reporting authentication, but implementing Windows authentication offers greater opportunity for single sign on and identity delegation, should you develop reports using data platforms that support Kerberos constrained delegation.

As you evaluate a VM approach, one strategy to consider is deploying additional VMs to serve as domain controllers, providing Windows authentication and identity delegation to other VMs and servers on the same virtual network. For an overview of how to build a BI network in the cloud, using VMs as domain controllers, see the "Overall Environment" section in Deploy SQL Server Business Intelligence in Azure Virtual Machines. For a review of supported authentication types, see Authentication with the Report Server.

Report Server Connectivity

Establishing connectivity between development tools, management tools, and other application components in your custom solution is an essential step towards validating a VM as an alternative to SQL Reporting. Follow the instructions in this topic to configure and use SSRS on a VM: SQL Server Business Intelligence in Azure Virtual Machines.

Database Connectivity

In SQL Reporting, the only supported data platform is Azure SQL Database. Reports that run on a Standard or Enterprise editions of SSRS on a VM can continue to retrieve data from SQL Database. Within reports, data source connections can continue to specify the credentials of a database user on SQL Database. Similarly, connection strings require no modification as a result of content migration.

Passwords, however, will need to be updated once reports are published to a new report server. Because passwords cannot be extracted from SQL Reporting, you must re-enter the database user password for the embedded and shared data sources used to retrieve data from SQL Database.

Finally, remember to add rules to the SQL Database firewall to allow connections from the report server. This article provides the steps: SQL Server Business Intelligence in Azure Virtual Machines.

Testing connections

As an administrator on the VM and report server, you should be able to deploy a report project and verify connections to SQL Database as soon as the VM is configured and the SQL Database firewall is configured to accept connections from the VM.

We recommend the steps provided in the "Validation" section in Use PowerShell to Create an Azure VM.

SQL Reporting customers who are unfamiliar with SSRS can use the following table to compare the two platforms.

 

Compare SSRS Native Mode on an Azure VM SQL Reporting

Features

No feature restrictions for Reporting Services instances on a VM, except for features that vary by report server mode or by SQL Server edition. On a VM, reports can retrieve data from any supported data source. See Data Sources Supported by SSRS for details. For feature comparison by mode or edition, see Reporting Services Report Server and Features by Edition SQL Server 2012.

SQL Reporting is limited to un-federated Azure SQL Databases that are part of the same Azure subscription. On-demand report execution and rendering is supported, but scheduling and subscription delivery is not available.

Billing model

Billing is based on the compute resources required to support a VM in the data center.

Microsoft recommends Medium or Large VMs for SQL Server BI server applications, depending on report volume and number of SQL Server features you plan to use. For operational reporting, you will need both Reporting Services and a Database Engine instance for the report server database.

Different rates apply depending on the size of the VM, as VM size determines how much CPU, memory, and disk storage are allocated. See Pricing Details for SQL Server for more information.

Note that you are not charged for VMs that are turned off, so if you only use reports at certain times, you can export a report to a static format, such as PDF, and then stop the VM when the report server is inactive.

Billing is based on the number of report executions rather than compute resources. If additional capacity is required, an additional instance is added dynamically in the background. Your bill goes up incrementally, in response to the higher number of report executions.

Authentication and Authorization

Users can authenticate to SSRS on VM using Windows authentication or Forms authentication. Support for commonly used authentication subsystems allows for greater software integration opportunities and supports identity delegation across multiple applications.

For database platforms that support Windows authentication, you can take advantage of identity delegation to flow a user identity from the calling application, to the report server, to the backend database. See Authenticate to a report server and Microsoft BI Authentication and Identity Delegation for more information.

A report server on a VM uses a role-based authorization model. See Granting Permissions on a Native Mode Report Server.

SQL Reporting has a proprietary report server authentication subsystem, limited to defining report user identities used for sign in and role assignments. User identity cannot be deleted to other server applications.

SQL Reporting uses Native mode Reporting Services roles.

Software integration and architecture

Reporting Services is a middle tier service that sits between backend data sources and front-end clients, such as a browser or custom web page hosting a report. When evaluating Reporting Services on a VM as your operational reporting solution, your design should position Reporting Services as a middle tier service accordingly.

Architecturally and programmatically, a report server VM is equivalent to an on-premises server. Parity between cloud and on premises architecture is best achieved when other applications, such as backend data sources or front-end applications providing embedded reports, also run within the same Cloud service as the report server VM. In most cases, an end-to-end solution designed to run on-premises can be duplicated using a collection of VMs in a Cloud service. See Developer's Guide (Reporting Services) for more information about SSRS programmability.

In SQL Reporting, report access is primarily through the HTTP endpoint for URL access, or the SOAP management endpoint, often using the ReportViewer control embedded in a form or web page.

Note that on SQL Reporting, the ASP.NET MVC Web Application templates do not support the ReportViewer control.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft