3

We have a table called Guest in an Azure SQL database. We also have a campaign management tool sitting behind an API on the providers cloud.

When a record is created, updated or deleted in the Guest table, we would like to call the API in order to update the campaign management tool with the latest information about the Guest.

Our initial idea was to hook up a database trigger to a C# .NET Azure Function, however, it looks like this is only supported in Cosmos DB.

We would prefer not to have an application running on a scheduled task that periodically checks for changes in the database and sends these changes to the API.

We have also been reading about creating CLR stored procedures but it looks like these are not supported in Azure SQL databases.

Looking forward to hearing ideas & suggestions.

Alasdair Stark
  • 1,227
  • 11
  • 32
  • We won't be deciding until later in the week but I've marked your answer as accepted since I haven't seen any better suggestions. – Alasdair Stark Nov 06 '18 at 02:08

3 Answers3

3

I can think of a few ways to accomplish this.

[Unfortunately CLR is no longer supported in SQL Azure.]

One way is:

  1. Turn Change Data Capture on, on your Guest table.
  2. Create a server-less Azure Function that has a timer trigger. This function would use the CDC to determine what had changed in your table, and call your vendor API accordingly.

The server-less function is relatively lightweight compared to "an application running on a scheduled task".

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 1
    I hadn't thought of using an Azure Fucntion with a timer trigger. Could be a nice compromise to get some of the benefits of Functions. I'll set this as the accepted answer if there aren't any better suggestions. – Alasdair Stark Oct 30 '18 at 03:20
0

You can also use Azure Logic Apps for this case.

There are some predefined trigger which helps to trigger

  • When an item is created

  • When an item is modified

Then using the Action to call your API

Refer here

This will be the simplest way that you can achieve your usecase.

Jayendran
  • 9,638
  • 8
  • 60
  • 103
  • Azure Logic Apps - BizTalk for Azure! These may suit the OP's use case, but I suspect there won't be enough fine grained control? – Mitch Wheat Oct 30 '18 at 05:43
  • @MitchWheat I agree with you. The design of the Logic app is itself not intended to have grained control. Since the logic app is a lightweight component more like a drag and drop tasks. We have very few controls like retry and error logics. I just want to make sure that all available option for OP's use case.(in terms of simplest way to achieve) – Jayendran Oct 30 '18 at 06:02
0

You will have to migrate to Azure SQL Managed instances which supports CLR and Broker. https://learn.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-transact-sql-information#clr

Anass Kartit
  • 2,017
  • 15
  • 22
  • This is a borderline [link-only answer](//meta.stackexchange.com/q/8231). You should expand your answer to include as much information here, and use the link only for reference. – Blue Feb 04 '19 at 23:02