Expose SQL Logic as APIs

HTTP-based APIs have become a very popular way of exposing functionality to external entities outside an organization. For example, in order to support certain features of their products, some Vendors require integrating with your data real-time by calling HTTP APIs to get the information they need. Or maybe you have a very tech-savy customer that would like to get their data via APIs your organization exposes.

Typically, a Development Team spins up those APIs using programming languages and platforms such as .NET and Java. But what if your organization only has Microsoft SQL developers? In this series of posts, I would like to propose that they are able to encapsulate the needed logic into T-SQL stored procedures and/or views without the need of the work of a typical development team. This is great for those “multiple hats” DBAs who might not be strong enough in regular languages, but with good enough skills to do some basic managing in Azure.

Here is an overview of the process:

1. Design/Develop your database to address the use case. Follow Domain Driven Design guidelines in SQL as if it were a regular programming language by using separate schemas for public and private objects and keeping logic and db limited to one Bounded Context.
2. Publish your database to a SQL server that can be accessed by Azure. This could be an Azure SQL db, managed instance, Azure VM or an on-prem SQL db (if you have Network connectivity between Azure and your on-prem data center).
3. Build the HTTP-triggered Logic app. Logic Apps can be built using a graphical interface, no need for code except for some formulas. This logic app will need to call your SQL server. There are many safe ways to authenticate the Logic App with your SQL server. The logic app will take care of transforming the data from the Stored Procedures/Views
4. Expose the Logic App via Azure APIM. This product will also allow you to add extra features to your API such as rate-limiting, subscription keys, analytics without code except for some XML and JSON (JSON for defining your OpenAPI schema).
5. Implement security in layers , by:
   1. Limiting what can access your SQL server (the logic app)
   2. Limiting what can access your Logic App (the APIM)
   3. Setting up OAuth Client Credentials for your consumers by using Azure AD and APIM token validation policies.

The Azure offerings mentioned in the above list have either a free or reduced cost pricing option, making this approach inexpensive. In future posts we will dig deeper into each of the items in the list. For a video series please see: https://youtube.com/playlist?list=PLVObt25IiZ6gxbnlGMIDNa_iquKiBjUmo