Creating Azure Tables From Script

When working with the Windows Azure Storage service, you must create the tables before you can use them; in essence, defining the 'schema' of your Azure storage service.

On his blog, Steve Marx writes:

"Probably the best solution is to have separate initialization code that creates your tables.  This is analogous to the pattern of having CREATE TABLE commands scripted in T-SQL which you run once to set up the database."

I can only agree, but if you are relying on the CreateTablesFromModel method from the StorageClient data access API (from the Azure SDK), how can you do that?

The first thing to realize is that since you can connect to your Azure Storage Service from anywhere, the script does not need to execute in the cloud. You can just as well run this script from your local development machine, as long as you can connect to your storage account.

You could obviously write a little utility that references StorageClient and your custom TableStorageDataServiceContext.

Another, in my opinion, better option for such a one-off script is a PowerShell script. Here's my first take on such a script:

 $address = ""
 $account = <accountName>
 $accessKey = <accessKey>
 $storageClientPath = <storageClientPath>
 $contextPath = <contextPath>
 $ai = New-Object Microsoft.Samples.ServiceHosting.StorageClient.StorageAccountInfo($address, $null, $account, $accessKey)
 $dataContext = New-Object <customContext>($ai)
 $t = $dataContext.GetType()
 $ts = [Microsoft.Samples.ServiceHosting.StorageClient.TableStorage]::Create($ai)
 $ts.ListTables() | % { $ts.DeleteTable($_) }
 [Microsoft.Samples.ServiceHosting.StorageClient.TableStorage]::CreateTablesFromModel($t, $ai)

First of all, you will need to fill in the values that I've enclosed in angle brackets (remember quotes):

  • accountName is the name of your Windows Azure Storage account
  • accessKey is your Windows Azure Storage access key
  • storageClientPath is the full path to the StorageClient assembly, e.g. "C:\Program Files\Windows Azure SDK\v1.0\Samples\StorageClient\Lib\bin\Debug\StorageClient.dll"
  • contextPath is the full path to the assembly that contains your custom TableStorageDataServiceContext
  • customContext is the fully qualified type name of your custom context (see the update comment below)

This script does the following:

  1. It loads the StorageClient and your custom assembly into memory so types from those assemblies can be consumed by the script.
  2. It loops through all the tables currently in storage and deletes them (delete the penultimate line if you don't want it to do this)!
  3. It creates the tables from your data model.

Currently, the script has one limitation: Deleting a table using the StorageClient API only marks the table for deletion, so the operation returns much to soon. This means that if you are trying to recreate a table by the same name, a conflict will occur, and the table will not be created. You can work around this limitation by waiting a little while and then run the script again.

Update: In the original version of this post, I accidentally forgot to remove the specific type name of the custom context class that I'd used to create the script. Since you will not have access to that class, it doesn't make sense, so I replaced it with the customContext placeholder.

In case you were wondering, the original line of code was:

$dataContext = New-Object Microsoft.Dynamics.Mobile.Server.AzureDataAccess.MobileServerDataServiceContext($ai)