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

Powershell script for converting JPG to TIFF

The following Powershell script will convert a batch of JPEG files to TIFF format: #This Code is released under MIT license [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") $files_folder = 'C:\path-where-your-jpg-files-are\' $pdfs = get-childitem $files_folder -recurse | where {$_.Extension -match "jpg"} foreach($pdf in $pdfs) { $picture = [System.Drawing.Bitmap]::FromFile( $pdf.FullName ) $tiff = $pdf.FullName.replace('.PDF','').replace('.pdf','').replace('.jpg','').replace('.JPG','') + '.tiff' $picture.Save($tiff) }
Post a Comment
Read more

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

Alert if file missing using Powershell

The following Powershell script can be used to send an email alert when a file is missing from a folder or it is the same file from a previous check: $path_mask = "yourfile_*.txt" $previous_file_store = "lastfileread.txt" $script_name = "File Check" ###### Functions ########## Function EMailLog($subject, $message) {    $emailTo = "juanito@yourserver.com"    $emailFrom = "alert@yourserver.com"    $smtpserver="smtp.yourserver.com"       $smtp=new-object Net.Mail.SmtpClient($smtpServer)    $smtp.Send($emailFrom, $emailTo, $subject, $message) } Try {    #get files that match the mask    $curr_file = dir $path_mask |  select name    if ($curr_file.count -gt 0)    {        #file found        #check if the file is different from the previous file read        $previous_file = Get-Content $previous_file_store        $curr_file_name = $curr_file.Item(0).Name        if ($
Post a Comment
Read more