Apologies to Lord Tennyson for misquoting his wonderful poem “The Eagle”!
In the technical community, we do need to be ‘eagle eyed’ because there are a lot of infrastructure changes and people will need to keep watching out for them! If you like an in-person event, please take a look at SQLRelay and the Cloud OS CommunityRelay for news and technically-oriented sessions on SQL Server 2014, Windows Server 2012 R2 and Systems Center 2012 R2.
What’s the news here? On the October 31, 2013, notification went out to current Windows Azure SQL Reporting customers that the service will be discontinued in 12 months. What was Windows Azure SQL Reporting? It was is a cloud-based reporting service for the Windows Azure Platform built on SQL Server Reporting Services technologies. As a big SSRS fan, who has presented on SSRS and Azure together, I was disappointed that the service was going. So what about the move to Windows Azure Virtual Machines for SSRS?
So, what’s the alternative? Welcome to SSRS on Windows Azure Virtual Machines
However, my disappointment was quickly dissipated when I realised that the Microsoft vision is users will have VMs with SSRS for their Azure reporting instances. My belief is that people will probably find it pretty easy to move their Windows Azure SQL Reporting solutions towards SSRS on Windows Azure VMs.
IT departments are used to VMs, and I see an increasing trend towards virtualisation amongst many of my customers already. Using a VM, you can deploy an operational reporting solution in the cloud that supports either the Native or SharePoint mode feature set.
Will I get the same features that I had before? Fortunately, 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. This means that users should see no change from their perspective, and that is a good thing.
What’s the benefits?
Performance – It was well known that Windows Azure SQL Reporting report execution was slower than SSRS running on premises. Moving to a VM makes sense, because performance of SSRS on a Windows Azure Virtual Machine corresponds to an on-premises SSRS instance. Faster reports is always good news! Side by side testing has shown that performance gains are attributed to having the report server catalog reside on a local disk in the VM.
What about my custom code? SSRS on an Azure VM supports custom code and assembly references in a report. 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.
Mobility – this was my favourite feature of Windows Azure SQL Reporting but all is not lost with the new vision. If it is in the cloud, then you can look at mobilising the SSRS report from the VM as you did previously with SSRS as a service.
Scheduled report execution and delivery – yes! See Schedules and Subscription and Delivery.
Integration with hybrid solutions – yes! You can join a Windows Azure VM to your corporate network. This is particularly useful for small to medium businesses who prefer an operational cost (OPEX) than a capital expenditure (CAPEX) cost. This means that SMEs can add capacity quickly, without making large hardware costs. You can get more information here Windows Azure Virtual Network Overview
Considering a new Reporting Solution on Windows Azure?
Here are some points to note:
A Windows Azure VM can use Windows authentication to support single sign on. The configuration depends on your setup and your requirements e.g. whether you require validation at the report server or the backend, for example.
In order to help you to get started, take a look at the table below to help you evaluate a cloud-based Azure VM reporting solution for new software development projects:
|
Step
|
Description
|
Link
|
|
1
|
Before you start, learn about the basic capabilities of a Windows Azure VM by watching the videos and clicking the Explore links on the Virtual Machine page on the WindowsAzure.com web site.
|
|
|
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.
|
|
|
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.
|
|
|
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.
|
|
|
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.
|
Existing Projects using SQL Reporting on Windows Azure?
IT teams are accustomed to VMs, so this already leverages the skills in-house in order to make the transition. Here is some guidance below to help you to move existing SQL Reporting over to Azure VMs. Here are a few take-away points:
· You will need to replace it with an alternative technology by October 2014.
· Microsoft recommend a Windows Azure VM running SSRS in Native mode.
· Choosing an SSRS VM preserves your existing investment in report design, so no real changes made to the reports themselves.
You are not charged for VMs that are turned off. This saves you money! If you only use reports at scheduled times, for example, month end reporting, you can export a report to a static format, such as PDF. You could then stop the VM when the report server is inactive.
How do you migrate to a VM? Simple! 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 Windows Azure Virtual Machines.
Other aspects of a transition will require replacement functionality or manual changes, such as replacing report server authentication, or changes in how client applications connect to a report server. At a minimum, you will need to update the endpoint used on the connection.
For more information, see SQL Server Business Intelligence in Windows Azure Virtual Machines and Use PowerShell to Create a Windows Azure VM.
SSRS Native Mode on a VM versus SQL Reporting
SQL Reporting customers who are unfamiliar with SSRS can use the following table to compare the two platforms.
|
Compare
|
SSRS Native Mode on a Windows 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 Windows Azure SQL Databases that are part of the same Windows 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.
|
More information
· Sign up for Windows Azure<!–[if mso & !supportInlineShapes & supportFields]> SHAPE \* MERGEFORMAT <![endif]–><!–[if mso & !supportInlineShapes & supportFields]> <![endif]–>
I hope that helps someone!
Kind Regards,
Jen