Free Microsoft Azure SQL Tools for Cloud Application Development

Introduction to Microsoft SQL Azure

The relation database (RDBMS) provided in Microsoft Azure is known as SQL Azure. The SQL Azure database can be easily integrated with your local SQL Server and tools provided in Microsoft Visual Studio 2008 and 2010. An Azure Developer can use T-SQL scripts for querying data as they presently do for any on-premises SQL database. It's a highly available and scalable service thatcan be obtained by registering to the SQL.Azure.com site. Microsoft Azure doesn't provide any Off-premises SQL Azure development tools or Management studio for developers. You need to develop a local database and migrate it to SQL Azure during production deployment.

Top SQL Azure On-Premises Development Tools

Microsoft and other third party vendors have developed a few free SQL Azure development tools for integration of your SQL Azure Database with local on premises database. Out of these tools, Microsoft Sync Framework, the SQL Azure Migration Wizard and Gem Query tools are the most popular and well accepted by the Azure Development Community. In this article we will be discussing these on-premises Free SQL Azure development tools and how developers can use these tools in their daily workflow.

Microsoft Sync Framework

This framework can be synchronized with any kind of data through the cloud and is most popular within the Microsoft Cloud development community. The Sync Framework SQL Server Azure tool can be synchronized with Microsoft SQL Azure off-premises Cloud database. This tool has a wizard that provisions and configures a local SQL Sever database and SQL Azure database for synchronization with one another.

To use this tool, first you need to install Microsoft Sync Framework 4.0, freely downloadable from the Microsoft site. The Microsoft Sync Framework 2.1 Software Development Kit (SDK) is available in the Microsoft Download Center . Secondly, as stated earlier, you must have SQL Azure registration for accessing Microsoft Cloud Platform database. For that Sign Up with your Windows Live ID.

Thirdly, SQL Server 2008 R2 needs to be installed on your local machine. Click here to get SQL Server 2008 R2. Once all these installations are done click the "Start" button, then "All Programs," "Microsoft Sync Framework," and "SQL Azure Data Sync Tool for SQL Server." You will see a wizard, which will help you configure the local SQL Server instance with the Microsoft Cloud SQL Azure database.

SQL Azure Data Sync Initial Wizard Screens
Figure 1

Once you click the "Next" button, it will ask you about the database credentials. Provide database credentials, for example see the following screen-shot. An Azure Developer must remember SQL Azure firewall configuration /settings can affect the database connectivity.

SQL Azure Data Sync: SQL Azure Database Credentials
Figure 2

Again click the "Next" button on the wizard and enter your local database credentials.

SQL Azure Data Sync: SQL Server Local Database and Server Name
Figure 3

Once you click the next button, a list of tables will be shown. Now you need to select specific/all tables to add them in SQL Azure database and make them synchronized with your local development database.

SQL Azure Data Sync: Table list and conflict resolution policy settings
Figure 4

In the conflict resolution policy, you need to select one option from this dropdown; this determines which version of data will be propagated if both the local and SQL Azure database have been updated since last synchronization.

Once you click the "Process" button, the schema for the entire selected table will be created in SQL Azure but the tables will have no data until the synchronization JOB updates these tables from local the database.

Once you click "Next", the wizard will ask you for Sync order of tables. Use the "Move Up" and "Move Down" button for ordering data insertion process.Clicking "Next" you will move to the Summary screen.

Synchronization defined in the previous step must be given a proper name that is unique in your local database. Use the radio button to specify whether the selected SQL Azure database is a 1GB or 10GB type.

SQL Azure Data Sync:  Summary Screen for Providing Synchronization Job Name
Figure 5

To verify whether the Job has been created in your local SQL Server, open SQL Server Management Studio and Click on SQL Server Agent. You will find your Sync JOB under the JOBS folder. You can execute the JOB anytime for synchronizing your local and SQL Azure database.

SQL Azure Migration Wizard

For SQL Azure Database Administrators Microsoft introduced another tool named SQL Azure Migration Wizard, Short name SQLAzureMW. You can use this tool to migrate all of your on-premises database objects either in SQL Server 2005 or 2008. Microsoft SQL Server 2005 database objects such as stored procedures andT-SQL's have lots of compatibility issues with SQL Server 2008. SQLAzureMW will take care of all of these issues, fixing them where possible and notifying the Azure Developer of the issues.To Run SQLAzureMW from your local box you need to install SQL Server 2008 R2 and .NET Framework 3.3 with SP1.

The installer package (Zip file) and source code of this tool is available in the Codeplex at SQL Azure Migration Wizard v3.5.7.

This tool has 3 components/files:

  1. NotSupportedByAzureFile.Config ' Is the configuration file for SQLAzureMW. This configuration file has five major and a few minor sections named DefaultMessage, Table, View, Index, GeneralTSQL etc. The SQLAzureMW tool migrates a SQL 2000/2005 database to a SQL Azure database, where these configurations are read and appropriate actions are taken.
  2. SQLAzureMW.exe ' This is the SQLAzureMW application; tool, you’’ll be prompted with the following options:

    SQLAzureMW Tool Selection Process Screen
    Figure 6

    • A. Analysis only - You can either specify a SQL Server local database instance or a TSQL (data definition or manipulation language SQL code) text file. The third option, "SQL Profiler Trace File," will give flexibility to developers to test compatibility issues in scenarios where your code might have dynamic SQL statements.
    • B. Analysis and Migrate - You can analyze your local SQL Server 2000/2005 database and migrate to SQL Azure. For this category also you can either specify a local SQL database or a TSQL file. Since most of the Azure developers use this option, we will discuss it in detail.
    • C. Migrate without Analysis - This option is for Migrating your SQL Azure database to a SQL Azure database with a different name. This feature is not fully available as of now.
    • D. Run TSQL without Analyzing - The last option is to run the TSQL file you have generated from SQL Server Management Studio, containing DDL and DML statements without proper analysis.
    Once you click next, the credential provider window will prompt you to enter the SQL Server name and other details. You can either select Windows Credentials or can provide SQL Server credentials.

    SQLAzureMW Tool SQL Server Connection Screen
    Figure 7

    Once you are connected with your local SQL Server, the entire database list that exists on the specific server will be displayed; select the appropriate database and click "Next". On the next Window all of the scriptable objects under your database will be listed; you can select a specific database object type or manually select objects from the list. Clicking the "Advance" button will open a properties window where you can modify all scripting option settings.

    Once you're done selecting database objects, click next to continue to move to the summary screen of database objects and settings. You will be prompted for conformation; just press the "Yes" button to continue. SQLAzureMW will then generate the script for the selected objects. The Summary Screen has two tabs; generated TSQL will be displayed in the "SQL Script" tab and commented messages starting with "--~" will be displayed in the "Result Summary" tab.

    SQLAzureMW Tool Result Summary Screen
    Figure 8

    Once you generate the TSQL script, the next step is to run this script in the SQL Azure database. Click "Next" to move to the SQL Azure Connection Screen. Here your need to provide the SQL Azure connection information (obtained SQL Azure license).

    SQLAzureMW Tool SQL Azure Connection
    Figure 9

    After your SQL Azure connection information has been entered, press the "Test Connection" button for to verify that your connection information is correct and to list all of the databases that you have in SQL Azure. Select an existing database where you to have TSQL generated in the previous step and click the "Next" button to execute the script. After successful execution of the script the SQLAzureMW tool will generate a result; if any errors occur during the migration, the SQL Azure Response window will show the error messages in Red. If you can't see any red messages in the output that means the migration was successful.

  3. SQLAzureMW.exe.config ' is the configuration file for SQLAzureMW.exe. It has 6 important tags and the most important are local and SQL Azure Server configuration and credential settings. You can also specify your NotSupportedByAzureFile.Config file path.

Gem Query for the SQL Azure Developer

Microguru, a third party vendor, has developed another Free SQL Azure development tool named Gem Query, which can be freely downloaded from Softpedia. You must install Microsoft .NET Framework 3.5 SP1 to use this tool. This tool supports both DDL and DML SQL queries and you can execute on top of your SQL Azure database. Using this tool you can directly connect with your SQL Azure database, developers don't need to maintain any local databases for synchronization.

Conclusion

SQL Azure was launched on October 2008 in Microsoft Community Technology Preview (CTP) and is still in its early stages. To learn more about SQL Azure development, see Microsoft SQL Azure.

Related Articles





Comments

  • Incorrect

    Posted by david.leibowitz on 05/11/2010 04:41pm

    In your first paragraph you state " Microsoft Azure doesn't provide any Off-premises SQL Azure development tools or Management studio for developers. You need to develop a local database and migrate it to SQL Azure durin production deployment. " That's not correct at all. You are not required to develop locally and then migrate. You can, in fact, do all of your dev directly in the cloud...and with free tools from Microsoft. SQL Server 2008 R2 Express (Advanced) includes SQL Server Management Studio (SSMS) which is used to directly connect to, create, and query databases in SQL Azure.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • This paper introduces IBM Java on the IBM PowerLinux 7R2 server and describes IBM's implementation of the Java platform, which includes IBM's Java Virtual Machine and development toolkit.

  • As mobile devices have pushed their way into the enterprise, they have brought cloud apps along with them. This app explosion means account passwords are multiplying, which exposes corporate data and leads to help desk calls from frustrated users. This paper will discover how IT can improve user productivity, gain visibility and control over SaaS and mobile apps, and stop password sprawl. Download this white paper to learn: How you can leverage your existing AD to manage app access. Key capabilities to …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds