Configure Azure Storage Emulator SQL Server Instance

If you're using Windows Azure Storage, you are almost certainly going to be running the storage emulator during development, instead of working directly against your storage account up in the cloud. This emulator (which comes in the Windows Azure SDK - see the "other" category), allows you to test locally against local instances of Blob, Queue and Table services.

As per the Overview of Running a Windows Azure Application with Storage Emulator reference, the emulator needs a local SQL Server instance. By default, it's configured to run against a SQL Server Express 2005 or 2008 database.

If you want to point it at a different instance of SQL Server, for example a shared development database server, you can do this using the DSInit command line tool. I originally came across this MSDN on How to Configure SQL Server for the Storage Emulator, which led me to try the following in the Windows Azure Command Prompt:

DSInit /sqlInstance:MyDevServerName\MyInstanceName

This tried to create the storage database, but failed with the following:

Creating database DevelopmentStorageDb20110816...
Cannot create database 'DevelopmentStorageDb20110816' : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

One or more initialization actions have failed. Resolve these errors before attempting to run the storage emulator again. These errors can occur if SQL Server was installed by someone other than the current user. Please refer to http://go.microsoft.com/fwlink/?LinkID=205140 for more details.

The correct way when trying to use a different database server instead of the local machine, is to use the SERVER switch instead: 

DSInit /SERVER:MyDevServerName\MyInstanceName

See the full DSInit Command-Line Tool reference.

When you then run the Storage Emulator, it will target that database server/instance. You can easily cleardown/reset that database by right clicking the Windows Azure Emulator icon in the taskbar, select "Show Storage Emulator UI" and the click "Reset". NB. Just to be clear, this will delete everything in your local storage emulator database.

An added "gotcha" to watch out for, if you have the storage account connection stored in a web/app.config and want to specify to use the local emulated instance, you need to use

UseDevelopmentStorage=true

exactly as it appears here. If like me, you initially give an ending semi-colon, you will get a FormatException stating with the error: "Invalid account string".