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
configure the local SQL Server instance with the Microsoft Cloud SQL Azure database.
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.
Again click the “Next” button on the wizard and enter your local database credentials.
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.
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.
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,
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:
- NotSupportedByAzureFile.Config ‘ Is the configuration file for
SQLAzureMW. This configuration file has five major and a few minor sections named
DefaultMessage, Table, View, Index,
The SQLAzureMW tool migrates a SQL 2000/2005 database to a SQL Azure database, where these configurations are read and appropriate actions are taken.
SQLAzureMW.exe‘ This is the
tool, you’’ll be prompted with the following options:
- 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.
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.
SQLAzureMWwill 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.
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).
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
SQLAzureMWtool 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.
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
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.
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.