CODEWITHSUNDEEP
sql-serverazureazure-sql-database
Jaxidian
Jaxidian

Reputation: 13511

Asynchronous Triggers in Azure SQL Database

I'm looking to implement some "Asynchronous Triggers" in Azure SQL Database. There was this other question that asked this same question with pretty much the same needs as mine but for SQL Server 2005/2008. The answer was to use the Service Broker. And it's a great answer that would serve my needs perfectly if it was supported in Azure SQL Databases, but it's not.

My specific need is that we have a fairly small set of inputs selected and stored by a user. A couple of those inputs are identifications of specific algorithms and some aggregate-level data all into a single record of a single table. Once saved, we want a trigger to execute those selected algorithms and process the aggregate-level data to break it down into tens of thousands of records into a few different tables. This 2-8 seconds to process, depending on the algorithms. (I'm sure I could optimize this a bit more but I don't think I can get this faster than 2-5 seconds just because of the logic that must be built into it.)

I am not interested in installing SQL Server inside a VM in Azure - I specifically want to continue using Azure SQL Database for many reasons I'm not going to get into in this post.

So my question is: Is there a good/obvious way to do this in Azure SQL Database alone? I can't think of one. The most obvious options that I can see are either not inside Azure SQL Database or are non-starters:

  1. Use real triggers and not asynchronous triggers, but that's a problem because it takes many seconds for these triggers to process as it crunches numbers based on the stored inputs.
  2. Use a poor-man's queueing system in the database (i.e. a new table that is treated as a queue and insert records into it as messages) and poll that from an external/outside source (Functions or Web Jobs or something). I'd really like to avoid this because of the added complexity and effort. But frankly, this is what I'm leaning towards if I can't get a better idea from the smart people here!

Thanks for the help!

(I am posting this here and not on DBA.StackExchange because this is more of an architectural problem than a database problem. You may disagree but because my current best option involves non-database development and the above question I referenced that was almost perfect for me was also located here, I chose to post here instead of there.)

Upvotes: 3

Views: 3294

Answers (2)

Cameron Stillion
Cameron Stillion

Reputation: 333

You can use LogicApps. It has a SQL connector that implements an asynchronous trigger...

https://azure.microsoft.com/en-us/services/logic-apps/

Upvotes: 0

Dan Rediske
Dan Rediske

Reputation: 852

As far as I know, it's not possible to do directly in Azure SQL Database, but there are a few options:

As @gotqn mentioned in a comment, you can use Azure Automation/Runbooks; applied to Azure SQL specifically.

You can also check out database jobs.

Upvotes: 1

Related Questions