OakLeaf Systems: PASS Summit: SQL Azure Reporting Services Preview and Management Portal Walkthrough

Saturday, October 22, 2011

PASS Summit: SQL Azure Reporting Services Preview and Management Portal Walkthrough - Part 1

Quentin Clark, who’s corporate vice president of the Database Systems Group in Microsoft SQL Server organization, reported during his 10/13/2011 PASS keynote current public availability of SQL Azure Reporting Services and SQL Azure Data Sync Services CTPs in an upgraded Management Portal, as described in my Quentin Clark at PASS Summit: SQL Azure Reporting Services and Data Sync CTPs Available from Azure Portal post of 10/14/2011.

My PASS Summit: SQL Azure Sync Services Preview and Management Portal Demo of 10/19/2011 provided a walkthrough of the new Sync Services Preview. This post, which is based on the SQL Azure Reporting Samples TechNet Wiki article of 2/1/2011 (revised 10/11/2011) and was updated on 10/22/2011, provides similar treatment of the SQL Azure Reporting Services Preview.

A Service Release, promised by the end of 2011, will implement release versions of these and the other new SQL Azure features described in my Quentin Clark at PASS Summit: 150 GB Max. Database Size and Live Federation Scaleout for SQL Azure post.

This post covers:

Obtaining a Windows Azure Platform Subscription
Creating a SQL Azure Web server instance
Downloading the files for and creating the AdventureWorksLTAZ2208R2 SQL Azure database
Opening a SQL Azure database in SQL Server Management Studio 2008 R2 Express
Opening a SQL Azure database in the Windows Azure Management Portal’s Web-based Database manager
Downloading and opening the SQL Server Reporting Preview Report Samples in Business Intelligence Design Studio (BIDS)
Previewing a Report Sample in Business Intelligence Design Studio (BIDS)

Part 2 covers:

Setting up a SQL Azure Reporting Services Preview (SQLAzRSP) server
Testing the SQLAzRSP server in a browser
Deploying a sample report from BIDS to the SQL Reporting Services Preview Server
Viewing the sample report in a browser
Exporting and printing the sample report from a browser
Displaying the report in a ReportViewer control in a Windows Azure application.

Part 3 covers:

Creating a Hosted Service and Storage Account for Your Subscription
Downloading and Installing the SQL Azure Tools for Visual Studio 2010 SP1
Downloading and Opening the SQLAzureReportingPreviewCodeSamples in VS 2010
Displaying the report in a ReportViewer control in a local ASP.NET application
Working Around the Global.config <app settings> Failure
Deleting an Unneeded Web Role and Specifying an Extra-Small Instance
Creating Visual Studio Management Credentials
Deploying the project to a Windows Azure Web role

Setting up a Windows Azure Platform Subscription and SQL Azure Server

1. Testing SQL Azure Reporting Services Preview requires a Windows Azure Platform subscription with at least one SQL Azure Web server, a local SQL Server 2008 R2 [Express] SP1 instance to provide the correct Bulk Copy Process (BCP) version and Reporting Services, and a sample database (AdventureWorksLTAZ2008R2) for this example) installed to SQL Azure.

Note: If you don’t have a subscription with an unused database benefit, you can obtain a Windows Azure free trial with 90-days free use of a 1-GB SQL Azure Web database here.

If you’re a Microsoft Partner, you can sign up for Microsoft Cloud Essentials program here. Internal Use Rights for the Windows Azure program include the following monthly quotas:

Windows Azure Platform

750 hours of extra-small-compute instance

25 hours of small-compute instance

20 gigabytes of storage

250,000 storage transactions

Microsoft SQL Azure

1 gigabyte SQL Azure Web Edition database

Windows Azure AppFabric

100,000 access control transactions

2 service bus connections

Data Transfer

25 gigabytes in

25 gigabytes out

2. To create a new subscription, choose one of the Windows Azure Platform Offers, which ultimately opens the Windows Azure Platform Management Portal with your new, automatically named subscription active.

3. To create a new SQL Azure server for a subscription, click the Database button in the left navigation pane, select the subscription in the navigation pane and click the Server group’s Create button to display the first Create a New Server dialog. Open the Region list and select the region closest to the majority of your users:

4. Click Next to display the second dialog. Type an Administrator Login name and a complex password:

Note: A password must be at least 8 characters long and contain at least three of the four following classes:

Latin upper case letter (A - Z)

Latin lower case letter (a - z)

Base 10 digit (0 - 9)

Non-alphabetic characters, such as: exclamation point (!), dollar sign ($), number sign (#), or percent sign (%)

Further, it cannot contain an three consecutive characters of the Adminstrative Login name. For more information, see the SQL Server 2008 R2 Password Policy topic.

Confirm the password.

5. Click Next to open the third (firewall configuation) dialog. The default firewall configuration disallows all connections to the server. Mark the Allow other Windows Azure Services to access this server to add a MicrosoftServices rule:

6. Click Add to open the Add Firewall Rule dialog. Type the range of IP addresses to accommodate your organization’s users:

You can add as many different ranges as you need now or later in the Portal’s Server Information pane.

Note: You will usually see the IP address of your computer when you open the Add Firewall Rule dialog.

7. Click OK and Finish to create the server.

The subscription used for this walkthrough is named OakLeaf Cloud Essentials and its unique SQL Azure server name is ef12kdf9cd, as shown in the Windows Azure Platform Management Portal.

Access to the server is restricted to one of OakLeaf Systems’ four dedicated IP addresses by a firewall rule. Therefore, publication of the server name doesn’t significantly compromise its security.

8. Previewing the sample SQL Azure Reporting Services reports (*.rdl files) with SQL Server 2008 R2 Business Intelligence Development Studio requires installing SQL Server 2008 R2 SP1 Express with Advanced Services (SQLEXPRADV_x64_ENU.exe for 64-bit systems or SQLEXPRADV_x86_ENU.exe for 32-bit systems), which also installs SQL Server Management Studio (SSMS) 2008 R2 Express SP1. You can download and install either version from the Microsoft® SQL Server® 2008 R2 SP1 - Express Edition page.

9. Download AdventureWorks2008R2AZ.zip (11 MB) from CodePlex and extract its contents:

10. Open ReadMe.htm for instructions on how to deploy AdventureWorksLTAZ2208R2 to your SQL Azure server. For this example, the command-line expression to run from the LT subfolder is

buildawltaz.cmd tcp:ef12kdf9cd.database.windows.net RogerJ@ef12kdf9cd Password

The buildawltaz.cmd script creates the AdventureWorksLTAZ2208R2 database and uses the Bulk Copy Process (BCP) to create and populate its 12 tables, the first 6 of which are shown here:

11. Launch SSMS 2008 R2 SP1 and provide your credentials to connect to your server:

12. Click connect to open SSMS and expand the Databases node:

AdventureWorksLTAZ2008R2 contains a subset of SQL Azure-compatible tables from AdventureWorks2008R2’s Sales schema.

13. The SQL Azure Management Portal offers a lightweight database manager, formerly code-named “Project Houston.” To launch the database manager, select the database under the appropriate server node and click the Database group’s manage button to open a connect page:

14. Type your credentials and click the Log On button to open the Home page and click the Tables button to display table names, size and row count.

The database manager lets you add, drop and alter tables, as well as create or drop views and stored procedures.

Downloading and Installing the SQL Server Reporting Preview Report Samples

15. Download the SQL Azure Reporting Preview Report Samples (SQLAzureReportingPreviewReportSamples.zip, 185K) released 2/1/2011, updated on 10/11/2011) from the MSDN Archive’s SQL Azure Reporting Report Server Project and extract the files to a \My Documents\Documents\Visual Studio 2008\Projects\AdventureWorks 2008R2 Sample Reports folder:

16. Right-click the AdventureWorks 2008R2 Sample Reports folder node, choose Properties, and clear the Read-Only check box:

Click OK to modify the file attributes and display the Confirm Attribute Changes dialog:

Accept the default Apply Changes to This Folder, Subfolders and Files option, and click OK.

17. Navigate to the …\SQLAzureReportingReportSamples folder, and open and read the Readme.txt file, which contains the following information:

There are two varieties of reports in this .sln.

Stand-alone reports. The names of these reports do not include "AWLTAz_2008R2".

Reports that use AdventureWorksLT as a data soruce. The names of these reports include "AWLTAz_2008R2". These reports require access to the AdventureWorksLT database, which you can download and install from AdventureWorks2008R2 for SQL Azure: http://go.microsoft.com/fwlink/?LinkId=207677.

To preview reports:

Open SQL Server 2008 R2 Business Intelligence Development Studio, CU4 or above, in admin mode, and open SQLAzureReportingReportSamples.sln.

Edit shared data source "AnySQLAzureDatabase" to point to any SQL Azure database to which you have at least r/o access. Update the credentials.

Edit shared data source "SQLAzure_AdventureWorksLT" to point to your SQL Azure AdventureWorksLT database. Note: To preview data from your computer, you must add your IP address to the firewall exceptions to the SQL Azure database.

Verify that you can preview each report.

To deploy reports:

In the project properties, set TargetServerURL to the URL of your SQL Azure report server. For example: https://<servername>.reporting.windows.net/ReportServer.

Deploy the project. You will be prompted for report server credentials. Use credentials that have permission to deploy reports. To deploy reports, the credentials you use must be assigned at least the Publisher role. Your report server administrator can provide the necessary credentials.

To view published reports from the report server:

In a browser, enter the report server url: https://<servername>.reporting.windows.net/ReportServer.

Sign in to the SQL Azure Reporting portal with your credentials. To view a report you need only read permission on the report server. Your administrator can provide the credentials needed for you to view the report.

Click the name of the report to run it.

For more information, see SQL Azure Reporting samples (http://go.microsoft.com/fwlink/?LinkId=207630).

18. Choose Start, All Programs, Microsoft SQL Server 2008 R2, SQL Server Business Intelligence Developer Studio to launch BIDS:

19. In BIDS, choose File, Open, Project/Solution, navigate to the folder to which you extracted the sample files, select SQLAzureReportingReportSamples.sln:

20. Click Open to display the list of Shared Data Sources and Reports in Solution Explorer. Double-click one of the report (*.rdl) files with an AWLTAz_2008R2 suffix to display it in the Design pane and its Data Sets and Data Sources in the left Report Data pane:

21. Double-click the SQLAzure_AdventureWorksLT.rds node under Shared Data Sources to open it for editing:

22. Click Edit to open the Connection Properties dialog. Replace [servername] with your subscription’s server name, select the Use SQL Server Authentication, type your user name with an @servername suffix, type your password, mark the Save My Password checkbox, accept the default database name:

23. Click Test Connection, acknowledge the Test Connection Succeeded message, and click OK to return to the Shared Data Source Properties page. Click OK to save your changes.

24. Click the Preview tab to run the report against the SQL Azure database and display the Sales by Sales Person part of the report: