It’s not uncommon for SQL Azure servers and databases to suffer from “transient failures”. In application logs I have seen these occur during scale up/down events, periods where my application’s performance has been temporarily impacted (but its throughput has not changed), which I assume has been some load balancing going on in the background and when network connectivity has been a bit flakey.
Now I’m using The Polly Project which builds on the concepts of TOPAZ but has been thoroughly re-engineered with lots of extensibility, an active community and modern codebase. Inspired by Ben Hyrman and several other developers I have built a minimalist wrapper for the Dapper Async methods which detects transient errors using the same approach as the Entity Framework Core library.
I did think about retry functionality for async methods which returned object/dynamic but have only implemented strongly typed ones for the initial version.
[HttpGet]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> Get()
{
IEnumerable<Model.StockItemListDtoV1> response = null;
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.QueryWithRetryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]", commandType: CommandType.Text);
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving list of StockItems");
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok(response);
}
I have struggled to get reproduceable transient failures without pausing execution in the Visual Studio debugger and tinkering with variables or scaling up/down my databases (limit to how often this can be done) or unplugging the network cable at the wrong time.
This code was written to solve a problem I had debugging and testing an application which processed data from sensors attached to The Things Network(TTN) and I figured others might find it useful.
As part of my series of TTN projects I wanted to verify that the data from a number of LoRaWAN sensors connected to TTN was reasonable and complete. I’m familiar with Microsoft SQL Server so I built a .Net Core console application which uses the TTN Message Queue Telemetry Transport(MQTT) Data API (so it can run alongside my existing TTN integration) to receive messages from the all devices in a TTN application and store them in a database for post processing.
The console application uses MQTTNet to connect to TTN MQTT Data API. It subscribes to an application device uplink topic, then uses a combination of Stackoverflow Dapper with Microsoft SQL Server tables and stored procedures to store the device data points. I re-generated the classes I had used in my other projects, added any obvious missing fields and fine tuned the data types by delving into the TTN V2GO code.
The core of the application is in the MQTTNet application message received handler.
private static void MqttClient_ApplicationMessageReceived(MqttApplicationMessageReceivedEventArgs e)
{
PayloadUplinkV2 payload;
log.InfoFormat($"Receive Start Topic:{e.ApplicationMessage.Topic}");
string connectionString = configuration.GetSection("TTNDatabase").Value;
try
{
payload = JsonConvert.DeserializeObject<PayloadUplinkV2>(e.ApplicationMessage.ConvertPayloadToString());
}
catch (Exception ex)
{
log.Error("DeserializeObject failed", ex);
return;
}
try
{
if (payload.PayloadFields != null)
{
var parameters = new DynamicParameters();
EnumerateChildren(parameters, payload.PayloadFields);
log.Debug($"Parameters:{parameters.ParameterNames.Aggregate((i, j) => i + ',' + j)}");
foreach (string storedProcedure in storedProcedureMappings.Keys)
{
if (Enumerable.SequenceEqual(parameters.ParameterNames, storedProcedureMappings[storedProcedure].Split(',', StringSplitOptions.RemoveEmptyEntries), StringComparer.InvariantCultureIgnoreCase))
{
log.Info($"Payload fields processing with:{storedProcedure}");
using (SqlConnection db = new SqlConnection(connectionString))
{
parameters.Add("@ReceivedAtUtc", payload.Metadata.ReceivedAtUtc);
parameters.Add("@DeviceID", payload.DeviceId);
parameters.Add("@DeviceEui", payload.DeviceEui);
parameters.Add("@ApplicationID", payload.ApplicationId);
parameters.Add("@IsConfirmed", payload.IsConfirmed);
parameters.Add("@IsRetry", payload.IsRetry);
parameters.Add("@Port", payload.Port);
db.Execute(sql: storedProcedure, param: parameters, commandType: CommandType.StoredProcedure);
}
}
}
}
else
{
foreach (string storedProcedure in storedProcedureMappings.Keys)
{
if (string.Compare(storedProcedureMappings[storedProcedure], "payload_raw", true) == 0)
{
log.Info($"Payload raw processing with:{storedProcedure}");
using (SqlConnection db = new SqlConnection(connectionString))
{
var parameters = new DynamicParameters();
parameters.Add("@ReceivedAtUtc", payload.Metadata.ReceivedAtUtc);
parameters.Add("@DeviceID", payload.DeviceId);
parameters.Add("@DeviceEui", payload.DeviceEui);
parameters.Add("@ApplicationID", payload.ApplicationId);
parameters.Add("@IsConfirmed", payload.IsConfirmed);
parameters.Add("@IsRetry", payload.IsRetry);
parameters.Add("@Port", payload.Port);
parameters.Add("@Payload", payload.PayloadRaw);
db.Execute(sql: storedProcedure, param: parameters, commandType: CommandType.StoredProcedure);
}
}
}
}
}
catch (Exception ex)
{
log.Error("Message processing failed", ex);
}
}
For messages with payload fields the code attempts to match the list of field names (there maybe more than one match) with the parameter list for stored procedures in the AppSettings.json file. The Enumerable.SequenceEqual uses a case insensitive comparison but order is important. I did consider sorting the two lists of parameters but wasn’t certain the added complexity was worth it.
I created a database table to store the temperature and humidity values.
CREATE TABLE [dbo].[EnvironmentalSensorReport](
[WeatherSensorReportUID] [UNIQUEIDENTIFIER] NOT NULL,
[ReceivedAtUtC] [DATETIME] NOT NULL,
[DeviceID] [NVARCHAR](32) NOT NULL,
[DeviceEui] [NVARCHAR](32) NOT NULL,
[ApplicationID] [NVARCHAR](32) NOT NULL,
[IsConfirmed] [BIT] NOT NULL,
[IsRetry] [BIT] NOT NULL,
[Port] [SMALLINT] NOT NULL,
[Temperature] [FLOAT] NOT NULL,
[Humidity] [FLOAT] NOT NULL,
CONSTRAINT [PK_EnvironmentalSensorReport] PRIMARY KEY CLUSTERED
(
[WeatherSensorReportUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EnvironmentalSensorReport] ADD CONSTRAINT [DF_EnvironmentalSensorReport_EnvironmentalSensorReporttUID] DEFAULT (NEWID()) FOR [WeatherSensorReportUID]
GO
The stored procedure must have the parameters @ReceivedAtUtc, @DeviceID, @DeviceEui, @ApplicationID, @IsRetry, @IsConfirmed and @Port. In this example the payload specific fields generated by the Cayenne Low Power Protocol(LPP) decoder are @Temperature_0 and @relative_humidity_0
CREATE PROCEDURE [dbo].[EnvironmentalSensorProcess]
@ReceivedAtUtc AS DATETIME,
@DeviceID AS NVARCHAR(32),
@DeviceEui AS NVARCHAR(32),
@ApplicationID AS NVARCHAR(32),
@IsRetry AS BIT,
@IsConfirmed AS BIT,
@Port AS SMALLINT,
@Temperature_0 AS FLOAT,
@relative_humidity_0 AS FLOAT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[EnvironmentalSensorReport]
([PositionReportUID]
.[ReceivedAtUtc]
,[DeviceID]
,[DeviceEui]
,[ApplicationID]
,[IsConfirmed]
,[IsRetry]
,[Port]
,Temperature
,Humidity)
VALUES
(
@ReceivedAtUtc,
@DeviceID,
@DeviceEui,
@ApplicationID,
@IsConfirmed,
@IsRetry,
@port,
@Temperature_0,
@relative_humidity_0)
END
Environmental sensor data displayed in SQL Server Management Studio(SSMS)
To store more complex nest payload fields (e.g. latitude, longitude and altitude values), I flattened the the hierarchy.
private static void EnumerateChildren(DynamicParameters parameters, JToken token, string prefix ="")
{
if (token is JProperty)
if (token.First is JValue)
{
JProperty property = (JProperty)token;
parameters.Add($"@{prefix}{property.Name}", property.Value.ToString());
}
else
{
JProperty property = (JProperty)token;
prefix += property.Name;
}
foreach (JToken token2 in token.Children())
{
EnumerateChildren(parameters,token2, prefix);
}
}
Unpacked LPP payload from GPS tracker displayed in TTN application data viewFlattened location, acceleration and rotation information
CREATE TABLE [dbo].[PositionReport](
[PositionReportUID] [UNIQUEIDENTIFIER] NOT NULL,
[ReceivedAtUtC] [DATETIME] NOT NULL,
[DeviceID] [NVARCHAR](32) NOT NULL,
[DeviceEui] [NVARCHAR](32) NOT NULL,
[ApplicationID] [NVARCHAR](32) NOT NULL,
[IsConfirmed] [BIT] NOT NULL,
[IsRetry] [BIT] NOT NULL,
[Port] [SMALLINT] NOT NULL,
[Latitude] [FLOAT] NOT NULL,
[Longitude] [FLOAT] NOT NULL,
[Altitude] [FLOAT] NOT NULL,
CONSTRAINT [PK_PositionReport] PRIMARY KEY CLUSTERED
(
[PositionReportUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I created a database table to store values of only the fields I cared about.
CREATE PROCEDURE [dbo].[PositionReportProcess]
@ReceivedAtUtc AS DATETIME,
@DeviceID AS NVARCHAR(32),
@DeviceEui AS NVARCHAR(32),
@ApplicationID AS NVARCHAR(32),
@IsRetry AS Bit,
@IsConfirmed AS BIT,
@Port AS SMALLINT,
@accelerometer_3x AS FLOAT,
@accelerometer_3y AS FLOAT,
@accelerometer_3z AS FLOAT,
@analog_in_8 AS FLOAT,
@analog_in_9 AS FLOAT,
@analog_in_10 AS FLOAT,
@analog_in_11 AS FLOAT,
@gps_1Latitude AS FLOAT,
@gps_1Longitude AS FLOAT,
@gps_1Altitude AS FLOAT,
@gyrometer_5x AS FLOAT,
@gyrometer_5y AS FLOAT,
@gyrometer_5z AS FLOAT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[PositionReport]
([PositionReportUID]
.[ReceivedAtUtc]
,[DeviceID]
,[DeviceEui]
,[ApplicationID]
,[IsConfirmed]
,[IsRetry]
,[Port]
,Latitude
,Longitude
,Altitude)
VALUES
(
@ReceivedAtUtc,
@DeviceID,
@DeviceEui,
@ApplicationID,
@IsConfirmed,
@IsRetry,
@port,
@gps_1Latitude,
@gps_1Longitude,
@gps_1Altitude)
END
The stored procedure for storing the GPS tracker payload has to have parameters matching each payload field but some of the fields are not used.
Location data displayed in SQL Server Management Studio(SSMS)
For uplink messages with no payload fields the message processor looks for a stored procedure with a single parameter called “payload_raw”.(there maybe more than one match)
CREATE TABLE [dbo].[PayloadReport](
[PayloadReportUID] [UNIQUEIDENTIFIER] NOT NULL,
[ReceivedAtUtC] [DATETIME] NOT NULL,
[DeviceID] [NVARCHAR](32) NOT NULL,
[DeviceEui] [NVARCHAR](32) NOT NULL,
[ApplicationID] [NVARCHAR](32) NOT NULL,
[IsConfirmed] [BIT] NOT NULL,
[IsRetry] [BIT] NOT NULL,
[Port] [SMALLINT] NOT NULL,
[Payload] [NVARCHAR](128) NOT NULL,
CONSTRAINT [PK_PayloadReport] PRIMARY KEY CLUSTERED
(
[PayloadReportUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PayloadReport] ADD CONSTRAINT [DF_PayloadReport_PositionReportUID] DEFAULT (NEWID()) FOR [PayloadReportUID]
GO
ALTER PROCEDURE [dbo].[PayloadRawProcess]
@ReceivedAtUtc AS DATETIME,
@DeviceID AS NVARCHAR(32),
@DeviceEui AS NVARCHAR(32),
@ApplicationID AS NVARCHAR(32),
@IsRetry AS Bit,
@IsConfirmed AS BIT,
@Port AS SMALLINT,
@Payload AS NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[PayloadReport]
([PositionReportUID]
.[ReceivedAtUtc]
,[DeviceID]
,[DeviceEui]
,[ApplicationID]
,[IsConfirmed]
,[IsRetry]
,[Port]
,[Payload])
VALUES(@ReceivedAtUtc,
@DeviceID,
@DeviceEui,
@ApplicationID,
@IsConfirmed,
@IsRetry,
@port,
@Payload)
END
Raw payload data displayed in SQL Server Management Studio(SSMS)
Initially the application just used Console.Writeline for logging, then I added Log4Net because it would be useful to persist information about failures and so I could copy n paste parameter lists to the appSettings.json file.
I have used the Enterprise library Blocks (which in different forms have been around since 2005) in quite a few projects. Individually the components are pretty good (not always best of breed) but they are well integrated and when used in the way which they were intended to be used work well.
I have just upgraded a client application to Visual Studio 2015 + .Net 4.5 + Enterprise Library V6 and some of the steps were not immediately obvious so hopefully this saves someone else some time. I have sample code for Azure Cloud Service Web and Worker roles.
For both web and worker roles I added the Azure Diagnostics listener to the listener config section of the enterprise library logging settings.
For the worker role I configured the exception and logging blocks in the worker role startup
public override bool OnStart()
{
// Set the maximum number of concurrent connections
ServicePointManager.DefaultConnectionLimit = 12;
...
LogWriterFactory logWriterFactory = new LogWriterFactory();
LogWriter logWriter = logWriterFactory.Create();
Logger.SetLogWriter(logWriter);
...
return result;
}
Then in the webrole webapi2 API controllers you can use embedded SQL or call stored procedures with retries. (This sample code uses the Northwind database and default retry configuration)
public IEnumerable&amp;lt;ProductDto&amp;gt; Get()
{
var products = new List&amp;lt;ProductDto&amp;gt;();
WebApiApplication.exManager.Process(() =&amp;gt;
{
Database db = new DatabaseProviderFactory().Create(&amp;quot;NorthwindInstance&amp;quot;);
RetryPolicy retry = new RetryPolicy&amp;lt;SqlDatabaseTransientErrorDetectionStrategy&amp;gt;(RetryStrategy.DefaultExponential);
var productAccessor = db.CreateSqlStringAccessor(
&amp;quot;SELECT [ProductID],[ProductName],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[Discontinued] FROM Products&amp;quot;,
MapBuilder&amp;lt;ProductDto&amp;gt;
.MapAllProperties()
.Map(p =&amp;gt; p.ID).ToColumn(&amp;quot;ProductID&amp;quot;)
.Map(p =&amp;gt; p.Name).ToColumn(&amp;quot;ProductName&amp;quot;)
.Map(p =&amp;gt; p.QuantityPerUnit).ToColumn(&amp;quot;QuantityPerUnit&amp;quot;)
.Map(p =&amp;gt; p.UnitPrice).ToColumn(&amp;quot;UnitPrice&amp;quot;)
.Map(p =&amp;gt; p.UnitsInStock).ToColumn(&amp;quot;UnitsInStock&amp;quot;)
.Map(p =&amp;gt; p.Discontinued).ToColumn(&amp;quot;Discontinued&amp;quot;)
.Build());
products = retry.ExecuteAction(() =&amp;gt;
{
return productAccessor.Execute().ToList();
});
}, &amp;quot;ProductService&amp;quot;);
return products;
}
public ProductDto Get(int id)
{
ProductDto productDto = null;
WebApiApplication.exManager.Process(() =&amp;gt;
{
Database db = new DatabaseProviderFactory().Create(&amp;quot;NorthwindInstance&amp;quot;);
var productAccessor = db.CreateSqlStringAccessor(
&amp;quot;SELECT [ProductID],[ProductName],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[Discontinued] FROM Products WHERE [ProductID]=@ProductID&amp;quot;,
new ProdductGetByProductIdParameterMapper(db),
MapBuilder&amp;amp;lt;ProductDto&amp;amp;gt;
.MapAllProperties()
.Map(p =&amp;gt; p.ID).ToColumn(&amp;quot;ProductID&amp;quot;)
.Map(p =&amp;gt; p.Name).ToColumn(&amp;quot;ProductName&amp;quot;)
.Map(p =&amp;gt; p.QuantityPerUnit).ToColumn(&amp;quot;QuantityPerUnit&amp;quot;)
.Map(p =&amp;gt; p.UnitPrice).ToColumn(&amp;quot;UnitPrice&amp;quot;)
.Map(p =&amp;gt; p.UnitsInStock).ToColumn(&amp;quot;UnitsInStock&amp;quot;)
.Map(p =&amp;gt; p.Discontinued).ToColumn(&amp;quot;Discontinued&amp;quot;)
.Build());
productDto = productAccessor.Execute(id).SingleOrDefault();
}, &amp;quot;ProductService&amp;quot;);
return productDto;
}
