IoT with Azure SQL
IoT solutions usually include several components that spans from device communication and management to event processing and data ingestion and analysis. Deploying all these components independently on compute, network and storage based infrastructures can be a complex and time consuming task, but nothing compared to manage, monitor and operate them at significant scale.
If we look at a canonical IoT architecture, like the above, we can identify and describe roles and responsibilities of each of these components:
- Individual and edge devices, from intelligent sensors to industrial PCs aggregating multiple signals, send telemetry messages and receive commands from the cloud.
- Cloud gateway is managing bi-directional communications with devices, queueing events and messages, but usually also device provisioning and management.
- Event processing is usually performed as real-time streaming for capturing low latency events like alarm and thresholds to be plotted on a dashboard, but can also be through batch processing for large amount of data that can be stored for future analysis.
- Warm storage, plays a role in persisting low latency events and messages that need to be ingested efficiently and at a high rate, but also be query-able concurrently by a combination of point lookup and aggregated queries, mostly comparing trends and values over time (hybrid workload, or HTAP).
- Cold storage is optimized for large data volumes at reasonable cost, most likely retaining raw original messages that can be further processed and curated by Big Data and analytical engine to extract insights.
Thankfully, Azure platform offers a full portfolio of PaaS services that plug in to each other seamlessly and are covering all the needs of a modern and scalable IoT solution, as the following architectural diagram represents:
From device connectivity and management, IoT Hub provides secure and reliable communication for your Internet of Things (IoT) applications, connecting virtually any device and offering comprehensive per-device authentication options, built-in device management, predictable performance and availability.
IoT Hub, built on Azure Event Hub as event store, can manage from few 100s to million of events per second, that can be consumed through several APIs and services.
Azure Stream Analytics, as an example, is a real-time analytics service designed for mission-critical workloads to build end-to-end serverless streaming pipelines with just a few clicks. With Stream Analytics, your IoT solution can analyze and react in real time to specific event patterns or thresholds generated by your device fleet, and be used to populate dashboard and reports capturing low-latency phenomenon in the system. Stream processing results can also be persisted directly in Azure SQL’s disk-based or In-memory Optimized tables for low latency and high throughput, to be subsequently queried and analyzed. Azure Stream Analytics uses a SQL dialect, extensible with custom code, to cover the most advanced scenarios, and can scale up and down to cover the most challenging workloads.
To process the remaining vast amount of all the events generated by devices that needs to be captured, stored and analyzed, you can used another PaaS service like Azure Functions, an event-driven serverless compute platform that natively connects with IoT Hub event store and can consume batches of incoming messages and process them following your customizable logic written in one of the many programming languages supported. Azure Functions can be bound to the incoming message flow that will trigger processing on one end, and can use Azure SQL client libraries on the other end to define data shape and form before persisting what’s needed in relational or non-relational format in the database.
Azure SQL proven to be able to ingest million of events per second in this scenario, combining In-memory technologies to speed up data insertion with Columnar index structures that can help optimizing storage utilization (through up to 100x compression) and support for large aggregation and time-series analysis.
You have multiple options to scale an Azure SQL Database instance, depending on your workload and requirements, Both single databases and managed instances can be scaled up (for compute or storage, independently) or scaled out, through read scale-out replicas or database sharding.
Some of the features you may find relevant while designing IoT solutions with Azure SQL Database are:
- A single instance can scale up to 128 vCores (with M-Series hardware configuration) or 100 TB (with Hyperscale service tier). This means ingesting million of messages/sec and storing trillions of them in a single database instance, simplifying your data management operations.
- Multiple secondary replicas can be added to scale out read workloads and support 10Ks of concurrent queries on ingested data.
- Where additional scalability is required, Azure SQL Database provides Elastic Database tools to partition messages (e.g. using device or message ID sharding keys) across multiple database instances, providing linear scale for compute and storage.
- When ingesting messages from 100Ks devices, Azure SQL Database provides the ability to batch multiple requests into a single database interaction, increasing overall scalability and maximizing resource utilization.
- In-Memory technologies in Azure SQL Database let you achieve significant performance improvements with various workloads, including transactional, analytical and hybrid (HTAP). In-memory OLTP optimized tables help increasing number of transactions per second and reduce latency for scenarios like large data ingestion from IoT devices. Clustered ColumnStore indexes help reduce storage footprint through compression (up to 10 times) and improve performance for reporting and analytics queries on ingested messages.
- Azure SQL Database scales well on both relational and non-relational data structures. Multi-model databases enable you to store and work with data represented in multiple data formats such as relational data, graphs, JSON/XML documents, key-value pairs, etc and still benefit from all capabilities described before, like In-memory technologies. See more on multi-model support.
- In many IoT scenarios, historical analysis of ingested data is an important part of database workload. Temporal Tables are a feature of Azure SQL Database that allows to track and analyze full history of your data points, without the need for custom coding. By keeping data closely related to time context, stored data points can be interpreted as valid only within the specific period. This property of Temporal Tables allows for efficient time-based analysis and getting insights from data evolution.
Other services within the Azure platform can be plugged-in with minimum effort while building end-to-end IoT solutions, a great example can be Azure Logic Apps. Logic Apps can help automating critical workflows without writing a single line of code, by providing an immense set of out-of-the-box connectors to other services (like Azure SQL) and the ability to graphically design complex process that can be used in areas like data integration, management or orchestration. Things like triggering dataset refresh in PowerBI when certain events are happening at the database level become very simple and intuitive to implement thanks to Logic Apps.
Last but not least, Azure Machine Learning can be used to create and train complex ML model using datasets processed from IoT data and events, and hosted on Azure SQL or Azure Blob Storage for data scientists to use.
Who’s using this?
RXR Realty is the third-largest real estate owner in New York City, with over 25 million square feet of space across the tri-state area, including some of the most iconic addresses in Manhattan. When the COVID-19 pandemic hit, the company needed a way to integrate new safety measures for tenants after its buildings reopened for business. Working with key partners McKinsey & Co., Infosys, Rigado, and Microsoft, RXR used Microsoft Azure to create and deploy an intelligent, secure, hyperscalable solution—in just a few months.
The solution is named RxWell™—a comprehensive, public-health–based, data-driven program that merges physical and digital assets to help keep employees informed and supported during the “new abnormal” and beyond. Powered by the Internet of Things (IoT) and the intelligent edge, and firmly rooted in responsible artificial intelligence (AI) principles, RxWell combines real-time computer vision, sensors, AI, mobile apps and dashboards, and in-person service offerings. And Azure is making it all possible.
RXR Realty is using Azure SQL Database as warm storage where events flow through Azure Stream Analytics and Azure Function to power multiple dashboards and reporting applications on mobile and embedded devices on-site.
Schneider Electric, and his partner ThoughtWire, created an end-to-end solution for facilities and clinical operations management in healthcare settings. The solution uses Internet of Things (IoT) technology and Microsoft Azure cloud services to help hospitals and clinics reduce costs, minimize their carbon footprint, promote better patient experiences and outcomes, and increase staff satisfaction. The ThoughtWire application suite uses Azure Virtual Machines infrastructure as a service (IaaS) capabilities to run virtual and containerized workloads that support the ThoughtWire digital twin platform and Smart Hospital Suite in Azure. It also uses Azure SQL Database, which provides the right security controls for solution benefits like encrypting data at rest.
HB Reavis uses the power of Microsoft Azure IoT Hub, Azure SQL Database, and Stream Analytics. It captures complex IoT metrics essential for monitoring and analyzing diverse aspects of efficient workspace, such as office utilization, environmental insights (CO2, temperature, humidity), and general team dynamics and interactions. Once this data is collected, the platform converts it into actionable insights and visualizes the results on interactive Microsoft Power BI dashboards.
VERSE Technology is building an edge-to-cloud solution based on Microsoft Azure for one of the world’s largest baking companies, Grupo Bimbo. In the past year it has converted 15 factories, with many more scheduled soon. Now, with all this data being processed, analyzed, and visualized in real time, Bimbo can get an unprecedented, accurate picture of the company’s production process and begin transforming its business.
VERSE has designed a connected cloud solution for Bimbo based on Azure. Azure IoT Hub connects all the data streamed to the platform from different edge devices and sensors. Azure SQL Database is the highly scalable solution for storing RPMs, temperature, gas consumption, and more. This first phase encompasses more than 1,000 devices at the edge, with a range of communication protocols––RF, cellular, and Wi-Fi according to the needs of the location. The company plans to expand to more than 10,000 devices soon.
In this series, we’ll go dig deeper into how to build scalable and efficient IoT solutions leveraging Azure SQL, here are other articles on this subject:
0 comments