Unleash the power of Azure SQL with REST Endpoint Invocation

December 25, 2023 Tidjani Belmansour
Off
Uncategorised

Recently, Microsoft released a new feature as part of the Azure SQL Database service. In this article, we’ll explore this new capability and see what use cases it serves and how to use it.

What is Azure SQL REST Endpoint Invocation?

As the name suggests, Azure SQL REST Endpoint Invocation (yeah, it’s a very long name, that’s why I call it ASREI) is a new feature within Azure SQL that allows us to make external calls to REST APIs. By doing so, we can get data from (or send data to) external services without having to write complex integration code or update existing applications.

What use cases does it serve?

There are many use cases that could be served by this capability, including:

  • Workflow activation
  • Data enrichment
  • Data streams
  • Cache invalidation/update
  • Business process augmentation
  • Websites updates
  • Event-based integration

When I first discovered this feature, the three use cases I immediately thought of where: cache invalidation, event-based integration and live website updates.

ASREI in the context of application migration and modernization

In the above list, there’s actually one more use case that I later thought of (which actually falls under the “event-based integration” use case) is in the context of application migration and modernization.

In that scenario, I imagine migrating an application to Azure and leveraging Azure SQL for its database. Let’s call this application “App A”.

Now, this application would be part of a larger system with other applications. For the sake of simplicity, let’s consider the system to be composed of only two applications: “App A” (the one we migrated), and “App B” (a new one we’re developing).

Our business process requires App A to notify App B whenever a new transaction is performed in App A so that App B can start processing it.

What options do we have then? I can see two:

Option #1: update App A’s code to notify App B

This option can be represented as follows:

This option requires to change the code of App A. As you certainly oversee, this option presents some challenges. First, we need to still have access to the code (if the application wasn’t built in-house, this might not be the case). Second, assuming we have access to the code, we still need to have someone who understands it, so he/she knows what piece of that code to update. Third, considering that we satisfy both conditions 1 and 2, we need to ensure that App A has some automated tests. This will help us ensure that our code change doesn’t introduce regression. What I’m trying to illustrate here is that changing the code of an existing application is not always a straightforward process. It can prove being tricky and complicated if some conditions aren’t met.

Option #2: Make App B poll the App A’s database

This option doesn’t require us to change the code of the existing App A. We’ll rather introduce some new code on the App B side. This seems like a no-brainer since we’re actually developing App B (hence, the conditions we mentioned in Option #1 above should be met).

However, the only way I see is to make App B periodically poll App A’s database for new transactions. I see two problems with this approach. First, we have to figure out what is the optimal (or at least, reasonable) frequency we need to use so that polling the database doesn’t cause too much overhead on the database server. Second, this approach causes App B to be coupled with the schema of App A’s database, which is, as you certainly now, not a good software architecture practice.

This option can be represented as follows:

Now that we have ASREI, there’s a third option we can use.

Option #3: ASREI

With ASREI, it’s now very straightforward for App A to notify App B of new transactions without going through any of the above mentioned inconvenient: we don’t need to change App A’s code; We don’t need to poll the database (and figure out the optimal or reasonable frequency); and we don’t need to create coupling between App A’s database and App B.

All we need to have, on App B’s side, is a REST endpoint to invoke when a new transaction is inserted into the database. This option can be represented as follows:

See? Very straightforward, isn’t it?

How to implement it?

This new capability comes in the form of a stored procedure. Here’s its syntax:

EXEC @returnValue = sp_invoke_external_rest_endpoint
[@url=] N'url to invoke'
[, [@payload=] N'request payload (usually a json object)']
[, [@headers=] N'http headers as json array']
[, [@method=] 'GET' | 'POST' | 'PUT' | 'PATCH' | 'DELETE' | 'HEAD']
[, [@timeout=] seconds]
[, [@credential=] credential]
[, @response OUTPUT]

As you notice here, only the URL and the response object are required. The other parameters are optional.

returnValue should be of type INT. response should be of type NVARCHAR. As an example, we’ll see how App A’s database could notify App B that a new transaction has been inserted. Here’ s what the REST invocation would look like:

-- Declare variables to be used
DECLARE @returnValue INT, @response NVARCHAR(MAX), @trxNum INT, @confirmationNumber INT;

-- Get the latest transaction in the transactions table
set @trxNum = (SELECT TOP 1 transactionNumber FROM transactions ORDER BY ID DESC)

-- Notify App B of that new transaction
EXEC @returnValue = sp_invoke_external_rest_endpoint
@url= N'https://appb.contoso.com/api/newtransaction',
@payload= N'{"trxNumber":"@trxNum", "Sender":"AppA"}',
@method= 'POST',
@response= @response OUTPUT;

-- Update the transactions table with the confirmation number received from App B
set @confirmationNumber = (SELECT JSON_VALUE(@response, '$.result.confirmationNumber') AS confirmationNumber);

update transactions
set confirmationNumber = @confirmationNumber
where transactionNumber = @trxNum

Is this secure?

Yes! Security is provided out of the box, and at two levels:

  • At the database level: in order to leverage this capability, you’ll have to have the “EXECUTE ANY EXTERNAL ENDPOINT” permission on the database.
  • At the endpoint level: the invoked REST API could be authenticated using either a token in the header, a query string parameter, or a Managed Identity.

Conclusion

In this article, we’ve explored the REST Endpoint Invocation feature of Azure SQL and we’ve seen how to use it and in what scenarios we could benefit from it.

To learn more, go to the official documentation here. It’s now your turn to explore it and let me know in what ways you’re leveraging it.

Uncategorised

Comments are closed.