Update (2016-04-04): This article has been updated for Azure SDK 2.8 and SQL Server Developer Edition.

While developing for Windows Azure, I recently got lots of StorageExceptions reading “(500) Internal Server Error.”. This usually means that the SQL database that holds the table storage data is full. (SQL Server Express 2008 and sooner have a 4 GB limit; SQL Server Express 2012 and later have a 10 GB limit.)

Some time ago, Microsoft released its SQL Server Developer Edition for free. This edition doesn’t have a database size limit.

Here is how to use this SQL Server (or any SQL server instance) as storage for the table storage emulator:

1. Open Microsoft Azure Storage command line from the start menu.
2. cd "Storage Emulator"

3. (Re-)initialize the storage emulator (for the fully command-line reference see here):

   1. For the default SQL instance: AzureStorageEmulator.exe init -server .

   2. For a named SQL instance: AzureStorageEmulator.exe init -sqlinstance "MSSQLSERVER"

That’s it.

Note: If you use the “named SQL instance” option from above but the instance is actually the default instance, you will get this error:

Error: User-specified instance not found. Please correct this and re-run initialization.

Default instance or named instance ∞

You can run multi SQL Server instances on the same computer. Each instance has a name (e.g. MSSQLSERVER, SQLEXPRESS) and one of the instances will be the default instance. Depending on this, you need either to use option “default SQL instance” or “named SQL instance” above.

Unfortunately, I haven’t found a simple way to figure which instance is the default instance.

The one solution I found is to use Microsoft SQL Server Management Studio and try to connect to .\INSTANCENAME (e.g. .\MSSQLSERVER). If you can’t connect, than this instance is (most likely) the default instance.

How to get the name of the SQL instance ∞

The default SQL instance names are as follows:

SQL Server	MSSQLSERVER
SQL Server Express	SQLEXPRESS

You can list all instance names with the Sql Server Configuration Manager that should come with your SQL server installation.

It’ll give you something like this:

When publishing to Windows Azure, you may get the warning WAT150:

warning WAT150: The project ‘MyProject’ is dependent on the following assembly: J:\Sources\MyProject\packages\Microsoft.Data.Services.Client.5.6.0\lib\net40\Microsoft.Data.Services.Client.dll. This assembly is not in the package. To make sure that the role starts, add this assembly as a reference to the project and set the Copy Local property to true.

This warnings means that one of your projects uses an assembly that’s installed locally on your computer (e.g. using ASP.NET in a worker role) but is not installed on the Azure instance you want to run the package on. So, usually you can’t ignore this warning as the resulting package won’t run.

The usual fix is to use Nuget packages instead of globally installed assemblies. However, if this doesn’t work (like in my example above where the package clearly comes from Nuget), there’s another culprit:

The Global Assembly Cache (GAC)

Basically, if the referenced assembly is registered in the GAC with exactly the same version, then Visual Studio will always use the assembly from the GAC. Because of this, the assembly won’t be copied to the output directory and thus won’t be in the Azure package – resulting in WAT150 warnings.

This seems to be like this by design. Quoting from MSDN:

If you deploy an application that contains a reference to a custom component that is registered in the GAC, the component will not be deployed with the application, regardless of the CopyLocal setting. In previous versions of Visual Studio, you could set the CopyLocal property on a reference to ensure that the assembly was deployed. Now, you must manually add the assembly to the \Bin folder. This puts all custom code under scrutiny, reducing the risk of publishing custom code with which you are not familiar.

This statements is not entirely correct. Setting CopyLocal to true works – but only for the project directly referencing the assembly. Unfortunately, CopyLocal is not transitive, i.e. if project A references assembly X.dll (registered in the GAC; with CopyLocal set to true) and project B references project A, then X.dll will only appear in the output directory of project A but not in the output directory of project B. (It would appear in the output directory of project B if X.dll wasn’t in the GAC.)

How to fix ∞

There are a couple of ways how to fix this problem, but they all are workarounds:

• Delete the assembly from the GAC: Navigate to %WINDIR%\Microsoft.NET\assembly\GAC_MSIL and delete the assembly from here. Note though that these assemblies may still be need and some software may stop working.
• Switch to a different assembly version that’s not in the GAC.
• Reference the necessary assemblies directly in all role projects.