A lot has changed in the two years since the first release of this book. What at one time was in CTP (Community Technology Preview) and still being fleshed out has seen the light of day. Not long after we released the first version of this book, SQL Reporting, formally known as SQL Azure Reporting Services, hit the market in Beta form.
By the time this book hits the shelves, Windows Azure SQL Reporting will have been officially released. This chapter will first provide an overview of SQL Reporting by looking at its architecture and how fits into the Windows Azure SQL Database picture to support a wide variety of reporting needs. We will then spend the rest of the chapter looking at how to work with SQL Reporting, including how to deploy reports and access those reports from your application. This chapter will also look at managing SQL Reporting security via different roles, as well as discuss some best practices for securing SQL Reporting.
We’ll also spend some time in this chapter looking at the differences between on-premises SQL Server Reporting Services and SQL Reporting, including the security model as well as the different data sources for reports. This chapter assumes that you’re familiar with SQL Server Reporting Services and how to create and work with reports and subreports. Plenty of great books on SSRS are available if you need an introduction.
Let’s get started.
SQL Reporting Overview
When Microsoft set out to create a reporting service for the SQL Database, their goal was to provide many of the great reporting capabilities that currently exist in SQL Server Reporting Services and to ensure that the same cloud benefits that all of the Azure services benefit from (such as elasticity and high availability) were extended to SQL Reporting, without making developers learn a whole reporting structure. With SQL Reporting, they succeeded. SQL Reporting is a cloud-based reporting platform based on the tried-and-true SQL Server Reporting Services. Simply stated, SQL Reporting is SQL Server Reporting Server running as a highly available cloud service.
This is good news because it means several things. First, you don’t need to install your own Reporting Services instance, and you don’t need to maintain those instances (such as applying updates). Second, as part of the SQL Database platform, SQL Reporting can take advantage of the benefits of the Azure platform, providing high availability and scalability of your reporting services. Third, you can build reports using the same familiar tools that you currently use for building on-premises reports. Lastly, developers can deliver reports as an integrated part of a Windows Azure- based solution.
Think about this for a minute. In an on-premises environment, how long does it typically take to provision a new SQL Server Reporting Services server? Even if you have an extra server lying around, to get that server added to your SQL Server environment would typically take several hours. What if you could provision a new report server in a matter of minutes? Now you can do that with SQL Reporting, while enjoying the benefits of the Azure platform (letting Microsoft manage the physical administration).
With SQL Reporting you can quickly and easily provision and deploy reporting solutions while taking advantage of the enterprise-class availability, scalability, and security of the Windows Azure platform.
CHAPTER 6 ■ SQL REPoRTing
Architecture
The architecture of SQL Reporting Services has load balancing and high availability automatically built in. Figure 6-1
illustrates the different components of the SQL reporting architecture which we’ll discuss.
Figure 6-1. SQL Reporting Services Architecture
Each SQL Reporting report is accessed via the service endpoint and referencing a specific report. Notice that this isn’t any different than accessing your on-premises report server, except that in this case you are accessing a service endpoint instead of a physical machine.
The request is routed through the load balancer, which routes the request through two more application tiers in the architecture:
Reporting Service Gateway •
Reporting Service Nodes •
Gateway
The Reporting Services Gateway handles all of the intelligent metadata routing. For example, the Gateway implements Smart Routing, which means that as requests for a report come in, each request is processed and sent to the best available report server to handle that request. This allows for increased security and control over the availability of the system. For example, if a report (request) takes down a specific node, the Gateway can intelligently stop routing requests to that specific Reporting Services node.
Nodes
The nodes are the actual reporting servers. They are described as multi-tenant, meaning that each node has its own catalog and tempDBs (from SQL Database). The nodes are built on top of SQL Database; thus the data tier that is used
It is important to note that the services that the Gateway provides are very similar to the way the SQL Database intelligent metadata routing tier operates; in fact, some of the design patterns have been replicated in the SQL Reporting architecture.