June 2011 – Page 2 – SQLXpertise

Publishing Reports to SQL Azure Reporting Services

In the previous post we saw How to provision and configure SQL Azure Reporting Services account and How to create reports using SQL Azure data for SQL Azure Reporting Services

In this post we will see How to deploy or publish the report to SQL Azure Reporting Services

Open the Report project in SQL Server 2008 R2 Business Intelligence Studio and Right click on Project Name in Solution Explorer

In Project Properties, Specify the “Target Report Folder” and the “Target Server URL”, Target Server URL is the url you provisioned in SQL Azure Reporting Services account and make sure Target Server version is set to “SQL Server 2008 R2”. Click Apply and Ok to save the changes

Right the Project Name again on Solution explorer and click on “Deploy” to publish the report to SQL Azure Reporting Services

For connecting to SQL Azure Reporting Service, it will prompt for user name and password, Please specify the SQL Azure Reporting Services user name and password and click “Ok” to proceed

In Project Build Output, you can verify the status of the Deployment

Once successfully deployed, open Internet Explorer or any compatible browser and open

https://SERVERNAME.ctp.reporting.database.windows.net/ReportServer

Replace SERVERNAME with your SQL Azure Reporting Services server name, enter user name and password and click “Sign In”

After successful login, you will be able to see the list of Report folders, click on the target folder we deployed

Now you will be able to see the list of reports available in the target folder, click on the Target Report we deployed to test it.

Since I haven’t saved the SQL Azure database user name and password, it will prompt for the credentials to connect to database, after specifying required credentials, click “View Report”

The Report will be successfully generated based on availability of data. You can export this Report or Print the report.

Now we have successfully deployed the report to SQL Azure Reporting services and preview the reports from SQL Azure Reporting Service Web

Hope you all find this information useful !!!

Creating Reports using SQL Azure Data for SQL Azure Reporting services

In the previous post we saw How to provision and configure SQL Azure Reporting Services account.

In this article, we will see to How to create a report for SQL Azure Reporting Services using data from SQL Azure Database

Click on Start –> All Programs –> Microsoft SQL Server 2008 R2 and open SQL Server Business Intelligence Development Studio

Click on File –> New Project and select Report Server Project Wizard

Click “Next” on Report Wizard to proceed

Specify the name of the Data source and select Type as “Microsoft SQL Azure” and then click “Edit” to define connection string

Specify the name of the SQL Azure database server, specify username (Please enter username@servername) and then password and then Select the database .

Click “Advanced” and specify Application Name and set Encrypt to True, click “Ok” to proceed

Click on Test Connection to make sure the connection works and then click “Ok” to proceed

After connection test is suceeded, click “ok” to proceed and then the defined connection string will be displayed, you can manually verify this as well. Click “Next” to proceed

In Query Designer, you can select the tables and fields you want to display in report, You can also click on “Edit as Text”, if you want to use the query which you already created

After defining the query, click “Run Query” to test and make sure that they query works fine and returns data. After the query runs successfully, click “Ok” to proceed

You can review the query in this screen, if you want to make any further changes, click on “Query Builder…” button to go to query designer window, or else, click “Next” to proceed

Select the report type as “Tabular”, based on your requirement, you can change your report type. Once selected, click “Next” to proceed

Select the Available Fields and move to Displayed Fields, Group Fields and Details based on your report requirement and click “Next” to proceed

Select Report Table Layout and specify whether to display Subtotal or enable Drilldown and then click “Next” to proceed

Select your preferred Report Style and click “Next” to proceed

Enter the Report Name and then click “Finish” to complete

Once the report is generated and saved, it will be previewed automatically

Now you have successfully created a SSRS Report using SQL Azure data for SQL Azure Reporting services.

In the next post, we will see how to deploy this report to SQL Azure Reporting Services and preview the reports in SQL Azure Reporting Web

Migrating MySQL Databases to SQL Azure using SSMA–Part 3

In the previous posts, we saw how to download and install SQL Server Migration Assistant for MySQL v5.0 and configure utility database, and how to create a new migration project, add MySQL databases and SQL Server databases for conversion, analyze and prepare migration report.

In this post we will how to synchronize the MySQL schema to SQL Azure database and then migrate data

To convert the MySQL schema to SQL Azure schema, select the source database “ClassicModels” in MySQL Metadata Explorer and the right click and click on “Convert Schema”

Once the schema is converted, we will get the messages in output window similar to this (Messages will vary based on your database), if the message returns any error we need to fix it before migration

Once the schema conversion is complete, to migrate the schema, Right click on Target database in SQL Azure Metadata explorer and click on “Synchronize with Database”

Specify the password for the target server and click “Connect”

The schema migration preview window will display the list of objects being migrated, Click “Ok” to proceed

Migration process will be displayed in Output window, Once complete the output window will display the below message

Now we have successfully migrated the schema, To migrate the data, right click on Source Database and click “Migrate Data”

Specify the MySQL Server credentials and click “Connect”

Specify SQL Azure Target server credentials and click “Connect” to proceed

The data migrations process will prepare the data and start uploading the data to SQL Azure, the process details will be displayed in output window and the following message and below Data Migration Report will be displayed once the data migration completes successfully.

You can save the Data Migration report as CSV file for your reference.

You can connect to SQL Azure server using Project Houston or SQL Server Management Studio and query the data

Now we have successfully migrated the database schema and data from MySQL database to SQL Azure

Hope you all find this information useful !!!

Migrating MySQL Databases to SQL Azure using SSMA–Part 2

In the last post, we saw how to download and install SQL Server Migration Assistant for MySQL v5.0 and configure utility database, In this post we will see how to create a new migration project, add MySQL databases and SQL Server databases for conversion, analyze and prepare migration report.

Start the SSMA for MySQL (32 bit) by clicking on Start Menu –> All Programs –> Microsoft SQL Server Migration Assistant for MySQL 5.0

Create a New Project from File Menu

Specify the Name of Migration project, select the location where you want to save the migration project and Target to SQL Azure (Other supported targets are SQL Server 2005, 2008 / 2008 R2, “Denali”)

Click on “Connect to MySQL” to connect to Source MySQL Server

Specify the Server name, User name and Password, If required change the other parameters based on your MySQL Configuration and click “Connect”

Once Connected, MySQL Metadata Explorer will be populated with the list of MySQL Databases and UDFs available

For today’ demo, we will migrate “ClassisModels” database, you can expand the objects node to view the list of objects available in the database, You can select the list of object you want to migrate

Now click on “Connect to SQL Azure” on tool bar to connect to destination SQL Azure server and the specify the server name, user name and password and click “Browse”

Click “Yes” to proceed, The context menu will display the list of databases available and an option to create new database

Let us create New Database named “ClassicModels_DB” by clicking on “Create New Database”

Specify the name of the database and then select Database size and click “Create” to create the new database

Once the database is created, you will be able to see the above message, Click “Ok” to proceed. The new database name will be populated in database combo automatically

Click “Connect” to proceed

Once connected, SQL Azure database will be loaded in SQL Azure Metadata Explorer

Schema Mapping

Now let us modify the target schema, By default the Target schema will be set as Source database name, we can change it to our existing schema ex. dbo

Select the source schema and click “Modify”

Click on “…” button to browse and select the existing Schema, once selected, click “Select” to proceed

and then click “Ok” update the target the schema for selected source schema

Now we have successfully mapped the schema, let us map the types

Type Mapping

Type mapping window display the source type and destination type , if you want to change to change an existing mapping you can select a type and click “Edit”

Select the new Target Type and specify the length if required and click “Ok” to proceed

You can also add a new type mapping by clicking on “Add”

Character Set Mapping

In Character Set mapping window, you can view and review the character set mapped between Source and Destination database, you can make changes by selecting the Target Charset Type combo and then click “Apply”

SQL Modes

In SQL Modes window, you can select what effective SQL Modes will be used during migration

You can make the preferred changes and click “Apply”

Now we have successfully created a new MySQL migration project, connected source and destination databases and configured the migration properties.

Let us now Analyze the migration and see the reports

Right click on the Source database name “Classicmodels” and click on “Create Report”

Once the report is created, it will be displayed automatically

If there is any errors in analysis, the object will be marked in Red, and also the messages will be displayed in bottom pane.

Based on the error message, we can fix the issue and then migrate.

In the next post, we will how to synchronize the MySQL schema to SQL Azure database and then migrate data.

Migrating MySQL Databases to SQL Azure using SSMA–Part 1

Microsoft SQL Server Migration Assistant (SSMA) for MySQL is a tool to automate migration from MySQL database to SQL Server or SQL Azure.

SSMA for MySQL v 5.0 is freely available from Microsoft, you can download it from the following link http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ac017a90-3b53-49b6-92a7-0f91623ef530

It also has free support. SSMA automates the migration process by doing schema analysis, preparing migration report and migrating data.

SSMA for MySQL v 5.0 supports migration from MySQL 4.1 and higher to SQL Server 2005 / 2008 / 2008 R2 and SQL Azure

To install and run this we need MySQL ODBC Connection v 5.1, which you can download from the following link http://www.mysql.com/downloads/connector/odbc/

Let us see how to install the SSMA for MySQL v 5.0

Once downloaded the setup from the above URL, extract the zip file and run “SSMA for MySQL 5.0”