Disappearing local storage account

Because I like to have my development environment up to date, I recently installed the newest update to Azure Storage Explorer, which is marked with version 1.4.0. Some time later I opened it because I wanted to check some Table Storage entities stored locally and this is what I noticed:

For some reason, I lost access to my local account! This was the time when I got really anxious - nothing frustrates me more than updates, which break something. After experimenting a little bit, I found, that it is possible to restore access manually - by connecting to the local emulator:

This was something new as each previous installation of Azure Storage Emulator preserved my local settings. I believe, that it could be caused by the new feature, which allows you to configure ports, which Storage Emulator currently uses(as opposed to previously hardcoded 10000, 10001, 10002). Nonetheless, make sure, that with the newest update you have configured it manually, so you will not have to figure out for an hour what have happened.

Analyzing Table Storage bindings locally in Azure Functions

In one of my current projects I heavily rely on Table Storage bindings used in many of my functions. In fact I have several dozen API functions, which are the base of the whole system. Because codebase grows each day, I needed a tool, which will allow me easily validate whether I query Table Storage correctly - that means I follow some basic principles like:

  • using both PartitionKey and RowKey in a query
  • if RowKey is unavailable - using PartitionKey so I won't have to read the whole table
  • using $top whenever possible so I won't load the whole partition
  • using query projection - leveraging $select for selecting only a subset of columns in a row

In fact I knew two ways of doing that:

  1. Checking logs of Storage Emulator, what I described in this blog post. The disadvantage of that solution is that is logs nearly each and every request so it is hard to find a particular one you're interested in
  2. Using SQL Server Profiler to check what kind of queries are materialized 

As you can see above logs from Storage Emulator are quite detailed, yet painful to work with

I needed a tool, which would combine features of both solutions.

Reading SQL Server Profiler

The idea was to somehow read what SQL Server Profiler outputs when queries are sent to Storage Emulator. Fortunately it is really simple using following classes:

  • SqlConnectionInfo
  • TraceServer

Both are easily accessible in SQL Server directory:

  • C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
  • C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfoExtended.dll

There is however a small gotcha. Since SQL Server Profiler is a 32-bit application, you cannot use above classes in 64-bit one. Additionally those assemblies are SQL Server version sensitive - locally I have an instance of SQL Server 2017, if you have other version, you'd have to change the path to point to the correct one.

Does it work?

After some initial testing it seems it works. Let's assume you have following code:

/
[FunctionName("DeviceList")]
public static Task<HttpResponseMessage> DeviceList(
	[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "device")] HttpRequestMessage req,
	[Table(TableName, Connection = Constants.TableStorageConnectionName)] IQueryable<DeviceEntity> devices,
	[Table(Firmware.Firmware.TableName, Connection = Constants.TableStorageConnectionName)] IQueryable<Firmware.Firmware.FirmwareEntity> firmware,
	[Table(CounterType.CounterType.TableName, Connection = Constants.TableStorageConnectionName)] IQueryable<CounterType.CounterType.CounterTypeEntity> counterTypes,
	[Table(Location.Location.TableName, Connection = Constants.TableStorageConnectionName)] IQueryable<Location.Location.LocationEntity> locations,
	[Identity] UserIdentity identity,
	TraceWriter log)
{
	if (identity.IsAuthenticated() == false) return identity.CreateUnauthorizedResponse();

	var firmwareVersionsCached = firmware.Take(50).ToList();
	var counterTypesCached = counterTypes.Take(50).ToList();
	var locationsCached = locations.Take(50).ToList();

	var query = devices.Where(_ => _.PartitionKey != "device").Take(100).ToList().Select(_ => new
	{
		Id = _.RowKey,
		Name = _.Name,
		SerialNumber = _.SerialNumber,
		Firmware = firmwareVersionsCached.First(f => f.RowKey == _.FirmwareId.ToString()).Version,
		CounterType = counterTypesCached.First(ct => ct.RowKey == _.CounterTypeId.ToString()).Name,
		Location = locationsCached.First(l => l.RowKey == _.LocationId.ToString()).Name
	});

	var response = req.CreateResponse(HttpStatusCode.OK,
		query);

	return Task.FromResult(response);
}

 

Here you can find a part of diagnostic logs from executing above function:

You can find the whole project on GitHub: https://github.com/kamil-mrzyglod/StorageEmulatorTracer. After some time spent with this tools I found planty of issues in my code like:

  • not using PartitionKey
  • reading the same table twice
  • materializing all rows from a table when I needed only a subset

I guess I will even more flaws in the next days.