Skip to main content

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

Labels:

Comments

Post a Comment

Popular posts from this blog

Power Automate: SFTP action "Test connection failed"

When I added an SFTP create file action to my Power Automate flow ( https://flow.microsoft.com ) , I got the following error in the action step, within the designer: "Test connection failed" To troubleshoot the Power Automate connection, I had to: go the Power Automate portal then "Data"->"Connections"  the sftp connection was there, I clicked on the ellipsis, and entered the connection info It turns out, that screen provides more details about the connection error. In my case, it was complaining that "SSH host key finger-print xxx format is not supported. It must be in 'MD5' format". I had provided the sha fingerprint that WinScp shows. Instead, I needed to use the MD5 version of the fingerprint. To get that, I had to run in command line (I was in a folder that had openssh in it): ssh -o FingerprintHash=md5 mysftpsite.com To get the fingerprint in MD5 format. I took the string (without the "MD5:" part of the string) and put ...
Post a Comment
Read more

How to create online multiplayer HTML5 games in Contruct2

  Construct2 can use websockets to send and receive messages between games. By using socket-io , we can use a Node.js script as the server and my modification to the socket-io plugin for Construct2 to allow the games to synchronize data between them in real-time. There are two parts to this design: the Node.js server and the Construct2 clients (the games playing). The main part of building an online multiplayer HTML5 game is to plan: how the clients will communicate how often and what to communicate how much of the logic will go into the server and how much to the client. In my sample game, I chose to have each client own a player and have the server just relay messages: Use string messages in the form TypeOfMessage, Parameter1, Paremeter2, Parater3, etc to communicate. Have the clients send their player position about 16 times a second. Whenever their player shoots, the client needs to send a message immediately. Almost all of the game logic will...
9 comments
Read more

How to use Windows SSO with OpenXava

One of the nice things about the .NET web environment is the dead easy way to implement Single Sign On in your web apps through Active Directory authentication. In the Java world there are multiple alternatives to use Windows’ Single Sign On with Java based web apps. One of those alternatives is Waffle . Waffle allows your Java web app to authenticate against Active Directory groups (and users). The only caveat is that your web server needs to be running in Windows, which kind of makes sense. In this article, you will learn the steps required to have your OpenXava web application use Waffle to authenticate your Windows users. The first step is to download Waffle from their site and then copy the JAR files outlined in https://github.com/dblock/waffle/blob/master/Docs/tomcat/TomcatSingleSignOnValve.md to the OpenXava’s tomcat server. In your OpenXava project, create servlets.xml in the Web-inf, containing the following: <!-- the role name (the domain gorup) must be e...
Post a Comment
Read more