Saturday, October 22, 2011

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

image_thumb29Quentin 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.

imageMy 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.

image3. 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:

imageimage

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

image

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:

image

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

image

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.

image

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:

image

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:

image

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

image 

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

image

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

image13. 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:

image

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.

image

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:

image

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

image

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

image

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:

  1. In the project properties, set TargetServerURL to the URL of your SQL Azure report server. For example: https://<servername>.reporting.windows.net/ReportServer.
  2. 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:

  1. In a browser, enter the report server url: https://<servername>.reporting.windows.net/ReportServer.
  2. 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.
  3. 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:

image

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

image

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:

image

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

image

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:

image

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:

image

25. Scroll down to display the Top Stores part of the report:

image

26. Continue with Part 2.

0 comments: