Azure

SQL Server Error Code 4815 Bulk Insert into Azure SQL Database

by

If you receive error code 4815 while doing a Bulk Insert into an Azure SQL Database (including SqlBulkCopy()), it’s likely you are trying to insert a string that is too long into a (n)varchar(x) column.

The unhelpful error message does not contain any mention of overflow, or the column name! Posting in the hope it will save someone some time.

Azure SQL DB Storage Bottleneck

by

If you are using Azure SQL Databases, you should definitely read this post by Brent Ozar: There’s a bottleneck in Azure SQL DB storage throughput. The bottom line:
the transaction log throughput currently appears to bottleneck at 16 cores!

The bit where he compares AWS costs/relative performance is also an eye opener:

  • 8 cores, 1,991 per month: 64 minutes</li><li><strong>16 cores,3,555 per month: 32 minutes (and interestingly, it’s the same speed with zone redundancy enabled)
  • 80 cores, 18,299 per month: 32 minutes</strong></li><li>Just for reference: 8-core AWS EC2 i3.2xl VM,1,424 per month with SQL Server Standard Edition licensing: 2 minutes (and I don’t put that in to tout AWS, I just happen to have most of my lab VMs there, so it was a quick comparison)

Reducing Azure Functions Cold Start Time

by

You can host a serverless function in Azure in two different modes: Consumption plan and Azure App Service plan. The Consumption plan automatically allocates compute power when your code is running. Your app is scaled out when needed to handle load, and scaled down when code is not running. You don’t have to pay for idle VMs or reserve capacity in advance.

The consumption plan is likely the one you opted to use. You get 1 million free executions and 400,000 GB-s of resource consumption per month and pay-as-you-go pricing beyond that. It can also scale massively. The downside of using the consumption plan is that if there is no activity after five minutes (the default), your code will be unloaded by the Azure Functions host. The next time your function is invoked, your function will be loaded from scratch. I’ve experienced start up times ranging from a few seconds to several minutes.

With version 1.x of Azure functions you can raise the unload functiontimeout to 10 minutes in the host.json settings file.

If you want to keep your functions in memory, you either need to be calling them more frequently than the function timeout setting, or you could create another function in the same app. service plan that gets called periodically using a timer trigger:


using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;

namespace SQLFrontlineLoaderFunctionApp
{
    public static class KeepAliveTimerFunction
    {
        // Timer trigger is in UTC timezone 
        [FunctionName("KeepAliveTimerFunction")]
        public static void Run([TimerTrigger("0 */9 * * * *")]TimerInfo myTimer, TraceWriter log)
        {
            log.Info($"Alive: {DateTime.Now}");
        }
    }
}

This empty function will be called every 9 minutes (“0 */9 * * * *”). You could also keep your functions warm during a certain period during the day. This timer trigger cron expression would run every 9 minutes between 4am and 10am (UTC time zone): TimerTrigger(“0 */9 4-10 * * *”)]

Refs.

Azure Queues: Retry Poison Messages

by

Currently, the Microsoft Azure Storage Explorer doesn’t have the ability to move messages from one Azure queue to another. During development, I sometimes need to retry messages that are in the poison message queue. A poison message is a message that has exceeded the maximum number of delivery attempts to the application. This happens when a queue-based application cannot process a message because of errors.

It’s not difficult to write, but thought it might save someone a few minutes, so I’m posting it here. You’ll need to add a NuGet package reference to Microsoft.NET.Sdk.Functions


using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Queue;

void Main()
{
    const string queuename = "MyQueueName";

    string storageAccountString = "xxxxxx";

    RetryPoisonMesssages(storageAccountString, queuename);
}

private static int RetryPoisonMesssages(string storageAccountString, string queuename)
{
    CloudQueue targetqueue = GetCloudQueueRef(storageAccountString, queuename);
    CloudQueue poisonqueue = GetCloudQueueRef(storageAccountString, queuename + "-poison");

    int count = 0;
    while (true)
    {
        var msg = poisonqueue.GetMessage();
        if (msg == null)
            break;

        poisonqueue.DeleteMessage(msg);
        targetqueue.AddMessage(msg);
        count++;
    }

    return count;
}

private static CloudQueue GetCloudQueueRef(string storageAccountString, string queuename)
{
    CloudStorageAccount storageAccount = CloudStorageAccount.Parse(storageAccountString);
    CloudQueueClient queueClient = storageAccount.CreateCloudQueueClient();
    CloudQueue queue = queueClient.GetQueueReference(queuename);

    return queue;
}