Microsoft SQL Azure Enterprise Application Development

Build enterprise-ready applications and projects with SQL Azure

• Develop large scale enterprise applications using Microsoft SQL Azure
• Understand how to use the various third party programs such as DB Artisan, RedGate, ToadSoft etc developed for SQL Azure
• Master the exhaustive Data migration and Data Synchronization aspects of SQL Azure.
• Includes SQL Azure projects in incubation and more recent developments including all 2010 updates

SSIS and SSRS are not presently supported on SQL Azure. However, this is one of the future enhancements that will be implemented. While they are not supported on Windows Azure platform, they can be used to carry out both data integration and data reporting activities.

Moving a MySQL database to SQL Azure database

Realizing the growing importance of MySQL and PHP from the LAMP stack, Microsoft has started providing programs to interact with and leverage these programs. For example, the SSMA described previously and third-party language hook ups to Windows Azure are just the beginning. For small businesses who are now using MySQL and who might be contemplating to move to SQL Azure, migration of data becomes important. In the following section, we develop a SQL Server Integration Services package, which when executed transfers a table from MySQL to SQL Azure.

Creating the package

The package consists of a dataflow task that extracts table data from MySQL (source) and transfers it to SQL Azure (destination). The dataflow task consists of an ADO.NET Source connecting to MySQL and an ADO.NET Destination connecting to SQL Azure. In the next section, the method for creating the two connections is explained.

Creating the source and destination connections

In order to create the package we need a connection to MySQL and a connection to SQL Azure. We use the ADO.NET Source and ADO.NET Destination for the flow of the data.

In order to create an ADO.NET Source connection to MySQL we need to create an ODBC DSN as we will be using the .NET ODBC Data Provider. Details of creating an ODBC DSN for the version of MySQL are described here: http://www.packtpub.com/article/mysql-linked-server-on-sql-server-2008. Configuring a Connection Manager for MySQL is described here: http://www.packtpub.com/article/mysql-data-transfer-using-sql-server-integration-servicesssis.

The Connection Manager for SQL Azure Destination uses a .NET SQLClient Data Provider and this is described here (when SQL Azure was in CTP but no change is required for the RTM): . The authentication information needs to be substituted for the current SQL Azure database.

Note that these procedures are not repeated step-by-step as they are described in great detail in the referenced links. However some key features of the configuration details are presented here:

• The ODBC DSN created is shown here with the details:

  

• The settings used for the MySQL Connection Manager are the following:Provider: .NET ProvidersOdbc Data Provider
  Data Source Specification
  Use user or system data source name: MySqlData
  Login Information: root
  Password: <root password>
• The settings for the SQL Azure are the following:Provider: .Net ProvidersSQLClient Data Provider
  Server name: xxxxxxx.database.windows.net
  Log on to the server
  Use SQL Server authentication
  User name: mysorian
  Password: ********
  Connect to a database
  Select or enter database name: Bluesky (if authentication is correct, it should appear in the drop-down)

Creating the package

We begin with the source connection and after configuring the Connection Manager, by editing the source as shown in the following screenshot. You may notice that the SQL command is used rather than the name of the table. It was found however, that choosing the name of the table results in an error. Probably a bug, and as a workaround we use the SQL command. With this you can preview the data and verify it.

After verifying the data from the source, drag-and-drop the green dangling line from the source to the ADO.NET Destination component connected to SQL Azure. Double-clicking the destination component brings up the ADO.NET Destination Editor with the following details:

Connection manager: XXXXXXXXX.database.windows.net.Bluesky.mysorian2
Use a table or view: “dbo”.”AzureEmployees”
Use Bulk Insert when possible: checked

There will be a warning message at the bottom of screen: Map the columns on the Mappings page.

The ADO.NET Destination Editor window comes up with a list of tables or views displaying one of the tables. We will be creating a new table. Clicking New… button for the field Use a table or view brings up the Create Table window with a default create table statement with all the columns from the source table and a default table name, ADO.NET Destination. Modify the create table statement as follows:

CREATE TABLE fromMySql(
   "Id" int Primary Key Clustered,
   "Month" nvarchar(11),
   "Temperature" float,
   "RecordHigh" float

When you click on OK in this screen you will have completed the configuration of the destination. There are several things you can add to make troubleshooting easier by adding Data Viewers, error handling, and so on. These are omitted here but best practices require that these should be in place when you design packages.

The completed destination component should display the following details:

Connection manager: XXXXXXX.database.windows.net.Bluesky.mysorian2
Use a table or view: fromMySql
Use Bulk Insert when possible: Checked

The columns from the source are all mapped to the columns of the destination, which can be verified in the Mappings page, as shown in the following screenshot:

When the source and destination are completely configured as described here you can build the project from the main menu.

When you execute the project, the program starts running and after a while both the components turn yellow and then go green indicating that the package has executed successfully. The rows (number) that are written to the destination also appear in the designer.

You may now log on to SQL Azure in SSMS and verify that the table fromMySql2 has been created and that 12 rows of data from MySQL’s data have been written into it.