When developing applications that utilise Windows Azure storage, you will more than likely want to develop locally before deploying to the cloud.

Luckily, the Windows Azure SDK includes an offline storage simulator, called the “development storage” service. Development storage simulates the Table, Blob and Queue storage services in the cloud. The development storage service uses a database contained in a local SQL Server instance. The SDK will install SQL Server Express for you if you don’t already have it.

If you have a different version of SQL Server, or a different instance name from the default, you can create the database manually by calling the “dsInit” utility from the Windows Azure SDK command prompt.

You must supply the name of the SQL Server instance you want to use via the “/sqlinstance” argument e.g. dsInit /sqlinstance:INSTANCENAME

This will produce the following dialog:

Note: you can also specify the “/forcecreate” argument to overwrite an existing database and reset it to it’s initial state.

More information about Development Storage and the dsInit utility can be found on this MSDN page.

If you want to migrate an existing SQL Server database to a SQL Azure database in the cloud, you will find the SQL Azure Migration Wizard very useful indeed.

The SQL Azure Migration Wizard is an open-source project hosted on CodePlex, so you could, if you wanted to, download the source code and  add your own features.

I can’t believe that Microsoft themselves didn’t think to include something like this in the Azure SDK. Without it, I would have found the process of creating a SQL Azure database a lot more time-consuming and error-prone.

As it is, the wizard will analyze your database, warning you of potential incompatibilities with SQL Azure features, then generate a script that will create your SQL Azure database for you, complete with data. I tried it on the Northwind database, and had a version running in the cloud in minutes.

It’s worth pointing out a couple of minor ‘gotchas’ I came across whilst using it:

1. Your SQL Azure user name must be in the form user@servername where ‘servername’ is the dynamically-generated name of your SQL Azure server instance.
2. The wizard is not yet sophisticated enough to work out dependencies between objects and create them in the right order. I had an instance where the creation of a view failed because the table had not yet been created. The table creation statement was a few lines further down in the script.

Despite these minor points, it’s a great time-saver and best of all, it’s free!

SQL Azure is a relational database service that operates in “the cloud”. It is based on Microsoft SQL Server and is hosted on servers in Microsoft’s data centres.

Is SQL Azure the same as SQL Server? Well, yes and no. It is similar in that you have a virtual SQL Server instance which operates very much like a physical instance, but which is administered differently. For example, whilst you can create and manage databases, users and logins, you have no control over the physical environment such as the location of data files and logs.

Features Not Currently Supported

SQL Azure does not yet support all of the features found in SQL Server, though it has been hinted that more features will gradually be added in future. At the moment, SQL Azure does not contain:

• Analysis Services
• Reporting Services
• Service Broker
• Database Mirroring

Note: Replication is not supported, either, but since SQL Azure automatically replicates and load balances in order to provide a high-availability service, this seems fairly irrelevant.

Other Differences

SQL Azure supports most of the data types you would find in SQL Server 2008, but not all. For example, the new spatial and geometry types are not supported. A full list can be found in this MSDN article. Other features that are currently missing include:

• Common-language runtime
• Multiple active result sets (MARS)
• Linked servers/distributed queries

Despite the differences, there are certainly enough features to cater for the vast majority of line-of-business applications.

A more complete overview can be found on this MSDN page.