''Alexa, How Do You Create a Skill for SQL Clone?''

SQL Clone is a database provisioning tool from Redgate. It creates a data image, which is a copy of a database at a particular point in time, and then from that image it can create multiple, lightweight, database clones (a few tens of MB each) for deployment to the development and test servers. Each developer’s clone can be queried and updated independently, with any changes stored locally in a differencing file.

This opens up the interesting possibility that a DBA can use SQL Clone to provide a curated set of data images, from which developers can create database clones, on demand.

But we all know that developers are inherently lazy, so how far could we take this self-service database cloning idea? To celebrate the launch of SQL Clone v1, we decided to see if we could create an Alexa skill that would allow a developer, armed with an Amazon Echo, to voice activate the creation of a new database clone, in between sips of coffee.

You can see our solution in action on YouTube, and this blog explains in detail how we did it.

Overview

We created a custom Alexa skill and added it to an Echo device. When the skill is activated, Alexa parses the command and triggers an AWS Lambda function. This Lambda function adds a message containing the database name to an AWS SQS queue. A polling application, running on a local machine, polls the SQS queue for messages. When it finds a message that contains a valid database name, it triggers a ‘clone’ operation. This creates a clone from a data image that has the same database name, and deploys it to a SQL Server instance. To do all of this, the polling app uses a C# library distributed with the SQL Clone PowerShell cmdlets.

Required components

If you want to set up something similar, you’ll need an Amazon Echo, a machine running SQL Clone, a machine running the polling app, and an Amazon account logged into these services:

• Amazon Alexa
• Amazon Developer Portal
• Amazon Web Services

You’ll need a credit card for AWS but it’s unlikely that you’ll exceed the free tier.

Creating the Workflow

It’s actually easiest to configure the components in reverse order, so we’ll start by configuring SQL Clone and finish up with configuring the Echo.

Setting SQL Clone and the PowerShell cmdlets

You’ll need SQL Clone installed and running, so download the fully-functional free trial.

Once it’s up, you need to download and install the PowerShell client to get the C# libraries to trigger SQL Clone.

In the SQL Clone server’s web app, go to Settings > PowerShell > Download cmdlets and install SqlClonePowershellClient.msi.

By default, this installer will add the necessary libraries to:

C:\Program Files (x86)\Red Gate\SQL Clone PowerShell Client\RedGate.SqlClone.Powershell

You’ll need to add these libraries as references in the polling app.

Creating the Polling App

The polling app is a simple console application created in Visual Studio that polls an SQS queue continuously, in a loop, looking for messages and, when it finds one, it calls the SQL Clone API to trigger a clone operation. Polling a queue isn’t the most elegant solution but by making outgoing requests you avoid having to deal with firewall issues.

You’ll need to add to the app the libraries obtained in the previous section as references so that it can call SQL Clone’s API. They can then be referenced in the app like this:

using RedGate.SqlClone.Client.Api;

The polling app doesn’t have to be running on the same machine as SQL Clone, as long as you can access the SQL Clone server’s web app from that machine, you should be fine (by default this is http://<machinename>:14145).

Since the application needs to interrogate an AWS SQS queue for messages, you’ll need the AWS SDK for .Net (you can find it on Nuget as AWSSDK). The library can be referenced in the app like this:

using Amazon.SQS;
using Amazon.SQS.Model;

You will need to create a AWSSecretKey and AWSAccessKey in order to connect to your SQS Queue. We added ours to an App.config file in the solution:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="AWSAccessKey" value=""/>
    <add key="AWSSecretKey" value=""/>
    <add key="AWSRegion" value="us-west-2"/>
  </appSettings>
</configuration>

Listing 1

See the AWS Developer Blog for more details.

Before you can access the AWS SQS queue you’ll need create one in the AWS portal. Once you’ve done this, you can copy its URL to use in the polling app.

Processing Messages From SQS

The polling app checks the queue for messages. If there aren’t any, it sleeps for five seconds before trying again. If there is a message, it checks that the body contains a valid database name, starts the clone operation by calling CreateClone() and then deletes the message from the queue.

Since Alexa interprets database names from your voice, it won’t get spaces and capitalization right, so we have a dictionary to translate spoken database names to actual ones.

private const string AmazonSQSURL = "https://sqs..amazonaws.com";
private const string CloneSQSURL = "https://sqs..amazonaws.com//";
private static readonly Dictionary<string, string=""> MessageDatabaseNameToImageName = new Dictionary<string, string="">
{
  { "adventure works", "AdventureWorks"},
  { "stack overflow", "StackOverflow"}
};

private static void Main()
{
  var sqsClient = new AmazonSQSClient(new AmazonSQSConfig { ServiceURL = AmazonSQSURL });
  var receiveMessageRequest = new ReceiveMessageRequest { QueueUrl = CloneSQSURL };

  while (true)
  {
    Thread.Sleep(5000);

    var receiveMessageResponse = sqsClient.ReceiveMessage(receiveMessageRequest);

    if (receiveMessageResponse.Messages.Count == 0)
    {
      continue;
    }

    foreach (var message in receiveMessageResponse.Messages.ToList())
    {
      var messageDatabaseName = message.Body.Replace("\"", "");
      Log($"Processing message: {message.MessageId}");
      Log($"Message body: {messageDatabaseName}");

      string imageName;
      if (MessageDatabaseNameToImageName.TryGetValue(messageDatabaseName, out imageName))
      {
        var cloneResult = CreateClone(imageName).Result;
        Log(cloneResult);
      }
      else
      {
        Log($"Unable to find database name {messageDatabaseName} to clone.");
      }

      Log($"Deleting message {message.MessageId} from Amazon SQS");
      sqsClient.DeleteMessage(new DeleteMessageRequest
      {
        QueueUrl = CloneSQSURL,
        ReceiptHandle = message.ReceiptHandle
      });
    }
  }
}

Listing 2

Creating a Clone Using the SQL Clone API

Before the polling app can call SQL Clone’s API, it needs to establish a connection to the SQL Clone management service, identified by the URL used to access the server’s web app.
The app queries the available images and SQL Server instances to which we can deploy clones. AgentName is the host name of the machine on which SQL Clone agent is installed.

private const string AgentName = "";
private static Uri ManagementService => new Uri("http://:14145/");
private static ISqlCloneClient SqlCloneClient { get; } = new SqlCloneClient();

public static async Task CreateClone(string databaseName)
{
  await SqlCloneClient.Connect(ManagementService, CancellationToken.None);

  var images = await SqlCloneClient.GetImages(CancellationToken.None);
  var image = images.FirstOrDefault(x => x.Name == databaseName);

  if (image == null)
  {
    return $"Unable to create clone. Image {databaseName} not found";
  }

  var sqlServerInstances = await SqlCloneClient.GetSqlServerInstances(CancellationToken.None);

  var sqlServerInstance = sqlServerInstances.FirstOrDefault(x => x.Server == AgentName);
  if (sqlServerInstance == null)
  {
    return "Unable to find agent";
  }
  await SqlCloneClient.CreateClone(
    image,
    CreateCloneName(databaseName),
    sqlServerInstance,
    CancellationToken.None
  );
  return "Clone created";
}

Listing 3

Creating an AWS Lambda Function

When the user asks Alexa to create a copy of a database, it triggers the Lambda function, which then needs to identify the database name in the request, and pass this as a message to the SQS queue.

The easiest way to get the Alexa SDK into an AWS Lambda function is to start from the alexa-skill-kit-sdk-factskill blueprint from the Create a Lambda function page in AWS. This blueprint is a readymade function for receiving and responding to an Alexa skill.

Once you’ve created the Lambda function, grab the ARN (Amazon Resource Name) so you can reference it later from when configuring the Alexa skill.

Receiving the Request From Alexa

We modified the factskill code to send messages to the SQS queue, when triggered by an Alexa command.

exports.handler = (event, context, callback) => {
  const alexa = Alexa.handler(event, context);
  alexa.APP_ID = APP_ID;
  alexa.resources = languageStrings;
  alexa.registerHandlers(handlers);

  if(event.request.intent && event.request.intent.name === 'CloneIntent'){
    var databaseName = event.request.intent.slots.DatabaseName.value;

    var validNames = ['adventure works', 'stack overflow'];

    state.isValidDatabaseName = validNames.indexOf(databaseName) > -1;
    if (state.isValidDatabaseName){
      state.databaseName = databaseName;
      ExecuteCreateClone(alexa, databaseName);
      return;
    }
  }

  alexa.execute();
}

Listing 4

The event object is the package that the function will receive from Alexa. The event.request.intent property contains information about the Alexa command (which we’ll go into later), in this case it’s a CloneIntent action.

validNames is a list of image names that are ready to be cloned. If the request contains a DatabaseName that matches a name in the list of images, then the function calls ExecuteCreateClone().

Adding Messages to the AWS SQS Queue

Using the URL for the SQS queue that we created, we send it a message containing the database name. The alexa.execute(); call must happen inside the callback otherwise the function can exit before the message has finished sending. For the request to be accepted by Alexa, all code paths must call alexa.execute();.

const QUEUE_URL = 'https://sqs..amazonaws.com//';
const AWS = require('aws-sdk');
const sqs = new AWS.SQS();

function ExecuteCreateClone(alexa, databaseName){
  var params = {
    MessageBody: JSON.stringify(databaseName),
    QueueUrl: QUEUE_URL
  };
  sqs.sendMessage(params, function(err,data){
    if(err) {
      console.log('ExecuteCreateClone sqs.sendMessage callback error:',"Fail Send Message" + err);
    }
    // only execute alexa after message is sent to the queue.
    // if you don't do this the message is never sent.
    alexa.execute();
  });
}

Listing 5

User Feedback Via Alexa

User feedback is really important for voice interfaces (especially during development) so we have different responses depending on how the function executed. As these strings are read out by Alexa it helps to spell things phonetically (eg, SQL => sequel).

const handlers = {
  ...
  'CreateClone': function () {
    if (!state.isValidDatabaseName){
      this.emit(':tellWithCard', "Sorry, I don't recognise that database name.");
      return;
    }

    this.emit(':tellWithCard', "sequel clone has started cloning your database, " + state.databaseName, this.t('SKILL_NAME'));
  },
  ...
};

Listing 6

Creating a Custom Alexa Skill

You can create custom Alexa skills in the developer portal at Amazon. The skill’s information differs by language, so make sure you’ve defined the skill using the same language that your Echo device uses.

When creating the Skill information, the Name property is the name that your custom skill will appears as (in our case, SQLClone), whereas the Invocation Name is the command that will activate your device (in our case, sequel clone).

The Interaction Model section defines how users can interact with your skill. You can incorporate multiple intents, each of which is an action that can be performed by a skill.

Here’s the intent schema that our interaction model used:

{
  "intents": [
  {
    "intent": "CloneIntent",
    "slots": [
    {
      "name": "DatabaseName",
      "type": "LIST_OF_DATABASES"
    }
    ]
  },
  {
    "intent": "AMAZON.HelpIntent"
  },
  {
    "intent": "AMAZON.StopIntent"
  },
  {
    "intent": "AMAZON.CancelIntent"
  }
  ]
}

Listing 7

We called the intent that initiates a clone operation CloneIntent (this was referenced in the Lambda function – see Listing 4).

When creating a database clone using SQL Clone, the user needs to specify a database name, which can only be from a known list of possible values. Custom slot types are useful here, as you can define expected values of these arguments in advance to assist Alexa in parsing the voice command. As our database names don’t correspond to real English words, we found breaking them down was more effective.

Here’s our DatabaseName slot definition:

LIST_OF_DATABASES:
•  stack over flow
•  adventure works

In order for Alexa to have a better chance of successfully parsing a request, we define multiple variants of sample utterances. These utterances are the phrases that will trigger intents. The {DatabaseName} parameter corresponds to the slot definition.

CloneIntent create a copy of the {DatabaseName} database
CloneIntent create a copy of {DatabaseName} database

Before you can start testing your skill, you’ll need to add to your skill’s Configuration page the ARN of the Lamdba function that we created earlier.

When you’ve published your custom skill, you can add it to your Echo device. The easiest way to do this is through the Amazon Alexa portal.

Summary

That’s it! Once you’ve added the skill to your device and started up the polling app, a developer will be able to deploy a new database clone with just a voice command.