I’ve written previously about SQL Azure, Microsoft’s “database in the cloud” offering.

Whilst your SQL Azure account page will allow you to create and drop databases, it doesn’t (as yet) allow you to do much else. So how do you create tables, views and other objects? Well, if you’re migrating an existing database, you can use the SQL Azure Migration Wizard. If it’s a new database, you may choose to create it locally then use the SQL Azure Migration Wizard to migrate it for you. However, you can save yourself time by using SQL Server Management Studio to connect to your SQL Azure database directly.

Before you start, use your SQL Azure account page to create a new empty database. Go to the “Firewall Settings” tab and create a new firewall rule to allow access to your server from your external IP address:

When the rule has been added, switch back to the “Databases” tab, select your database and click the “Connection Strings” button to get the connection string, as you’ll need it shortly.

Next, fire up SQL Server Management Studio. When prompted to connect to a server, cancel out of the dialog and instead click the “New Query” button on the toolbar. When the connection dialog prompt appears, you’ll need to do the following:

1. For the server name, use yourservername.database.windows.net
2. For Authentication, choose SQL Server Authentication
3. For the Login name, enter the username as it appears in the connection string – this will be in the form username@server.
4. For the password, enter the password for the user account – for security reasons, this is not shown in the connection string.

So far, your server connection dialog will look something like this:

Next, click the “Options” button. This will expand the dialog box and switch to the “Connection Properties” tab. In the “Connect to database” text box, enter your database name and click “Connect”:

You should now be connected to your SQL Azure database. What you’ll notice immediately is that the server and database do not show up in the Object Explorer. This will supposedly be fixed in SQL Server 2008 R2 which is due to be released in May 2010.

You can now use the Query Editor window to issue CREATE TABLE and CREATE PROCEDURE statements. Note that there are some syntactical differences in the Transact-SQL statements you can use with SQL Azure. For more information, check out the SQL Azure Database Transact-SQL Reference pages on MSDN.

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!