Low-code / No-code API – Part 1: Azure SQL Serverless

Link to Part 0: https://programmium.wordpress.com/2020/07/21/developing-low-code-no-code-api-part-0/

Link to webinar: https://tiny.cc/nocode-azure

Persistent Store – B2B API

In the previous post, the high level architecture diagram showed that we’re going to use Azure SQL Serverless as the persistent storage (aka: the database) for the API. Azure SQL Serverless is relatively new offering form Azure SQL related products. This is a part of Azure SQL “Single database instances” and available under “General purpose tier”. As the name implies, this is another serverless computer tier offering in Azure.

Auto-pause and Auto-Resume

Having said that Azure SQL Serverless is a “severless compute-tier”, the storage is not serverless and storage shall be billed separately however with the help of two parameters called Auto-pause, you shall configure the database compute to when to pause after a period of waiting time, in which both the number of sessions and the CPU workload are zero. This period is called “Autopause delay“. Once the database is set to pause mode the billing shall be stopped for the compute until the database compute is resume.

There are a few triggers that trigger the database computer to resume, some of them are: Authentication, Threat detection, Auditing, Data masking, Query, Database copying, SQL data sync etc.

The latency to autoresume and autopause a serverless database is generally order of 1 minute to autoresume and 1-10 minutes to autopause.

Auto-scaling

The Azure SQL Serverless database is parameterized by a compute auto-scaling range as well. At the time of writing, this supports up to 40 vCores of GEN5 compute hardware and 120 GB of maximum RAM

Cache reclamation is triggered when PU or active cache utilization is low, however it does depend on several other factors as well. The SQL cache grows as data is fetched from disk in the same way and with the same speed as for provisioned databases. When the database is busy, the cache is allowed to grow unconstrained up to the max memory limit.

Storage Capacity & QUERIES

Data can be stored up to 3TB. Rest of the functionalities are as same as the traditional Azure SQL DB instances.

Cost Estimation

Azure portal provides a very helpful meter widget to estimate the cost

In addition to Azure SQL Serverless, Azure Cosmos DB also now supports a serverless pricing model. Get more information on it at: https://azure.microsoft.com/en-us/blog/build-apps-of-any-size-or-scale-with-azure-cosmos-db/

TASKS API

Tasks API would use Azure SQL Serverless as the database. Its important to create the table once the Azure SQL Serverless resource is created. Azure SQL Studio shall be used to execute queries, its similar to SSMS but light weight. The following SQL query shall be executed to create the table in the database instance.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tasks](
	[TaskId] [int] IDENTITY(1,1) NOT NULL,
	[Title] [varchar](255) NOT NULL,
	[Description] [varchar](255) NULL,
	[Status] [varchar](255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tasks] ADD PRIMARY KEY CLUSTERED 
(
	[TaskId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

Lets talk about the Logicapp in the next post.

Happy Coding.

2 thoughts on “Low-code / No-code API – Part 1: Azure SQL Serverless

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s