If we look at a canonical IoT architecture, like the above, we can identify and describe roles and responsibilities of each of these components:<\/p>\n
- \n
- Individual and edge\u00a0devices<\/strong>, from intelligent sensors to industrial PCs aggregating multiple signals, send telemetry messages and receive commands from the cloud.<\/li>\n
- Cloud gateway<\/strong>\u00a0is managing bi-directional communications with devices, queueing events and messages, but usually also device provisioning and management.<\/li>\n
- Event processing is usually performed as real-time\u00a0streaming<\/strong>\u00a0for capturing low latency events like alarm and thresholds to be plotted on a dashboard, but can also be through\u00a0batch processing<\/strong>\u00a0for large amount of data that can be stored for future analysis.<\/li>\n
- Warm storage<\/strong>, 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).<\/li>\n
- Cold storage<\/strong>\u00a0is 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.<\/li>\n<\/ul>\n
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:<\/p>\n
<\/span><\/span><\/p>\n
From device connectivity and management,\u00a0IoT Hub<\/a>\u00a0provides 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.<\/p>\n
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.<\/p>\n
Azure Stream Analytics<\/a>, 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\u00a0Azure SQL<\/a>\u2019s\u00a0disk-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.<\/p>\n
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\u00a0Azure Functions<\/a>, 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\u00a0Azure SQL<\/a>\u00a0client libraries on the other end to define data shape and form before persisting what\u2019s needed in relational or non-relational format in the database.<\/p>\n
Azure SQL proven to be able to\u00a0ingest million of events per second<\/a>\u00a0in 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.<\/p>\n
You have multiple options to\u00a0scale<\/a>\u00a0an 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.<\/p>\n
Some of the features you may find relevant while designing IoT solutions with Azure SQL Database are:<\/p>\n
- \n
- 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.<\/li>\n
- Multiple secondary replicas can be added to scale out read workloads and support 10Ks of concurrent queries on ingested data.<\/li>\n
- Where additional scalability is required, Azure SQL Database provides\u00a0Elastic Database tools<\/a>\u00a0to partition messages (e.g. using device or message ID sharding keys) across multiple database instances, providing linear scale for compute and storage.<\/li>\n
- When ingesting messages from 100Ks devices, Azure SQL Database provides the ability to\u00a0batch<\/a>\u00a0multiple requests into a single database interaction, increasing overall scalability and maximizing resource utilization.<\/li>\n
- In-Memory technologies<\/a>\u00a0in 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.<\/li>\n
- 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\u00a0multi-model<\/a>\u00a0support.<\/li>\n
- In many IoT scenarios, historical analysis of ingested data is an important part of database workload.\u00a0Temporal Tables<\/a>\u00a0are 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.<\/li>\n<\/ul>\n
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\u00a0Azure Logic Apps<\/a>. 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.<\/p>\n
Last but not least,\u00a0Azure Machine Learning<\/a>\u00a0can 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.<\/p>\n
Who\u2019s using this?<\/h2>\n
RXR Realty<\/a>\u00a0is 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\u2014in just a few months.<\/p>\n
The solution is named RxWell\u2122\u2014a comprehensive, public-health\u2013based, data-driven program that merges physical and digital assets to help keep employees informed and supported during the \u201cnew abnormal\u201d 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.<\/p>\n
<\/span><\/span><\/p>\n
RXR Realty is using Azure SQL Database as warm storage where events flow through Azure Stream Analytics and Azure Function to power multiple\u00a0dashboards and reporting applications<\/a>\u00a0on mobile and embedded devices on-site.<\/p>\n
Schneider Electric<\/a>, 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\u00a0Azure SQL Database<\/a>, which provides the right security controls for solution benefits like encrypting data at rest.<\/p>\n
HB Reavis<\/a>\u00a0uses the power of Microsoft Azure IoT Hub,\u00a0Azure SQL Database<\/a>, 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.<\/p>\n
VERSE Technology<\/a>\u00a0is building an edge-to-cloud solution based on Microsoft Azure for one of the world\u2019s 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\u2019s production process and begin transforming its business.<\/p>\n
<\/span><\/span><\/p>\n
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.\u00a0Azure SQL Database<\/a>\u00a0is 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\u2013\u2013RF, cellular, and Wi-Fi according to the needs of the location. The company plans to expand to more than 10,000 devices soon.<\/p>\n
In this series, we\u2019ll go dig deeper into how to build scalable and efficient IoT solutions leveraging Azure SQL, here are other articles on this subject:<\/p>\n
- \n
- Ingest millions of events per second on Azure SQL leveraging Shock Absorber pattern<\/a><\/li>\n
- Optimize storage usage for large volumes of IoT data with Azure SQL<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":32624,"featured_media":81,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,472],"tags":[469,470,471],"class_list":["post-528","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-iot","tag-azuresqldb","tag-iot","tag-workload"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/528","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/32624"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=528"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/528\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/81"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=528"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=528"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=528"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Optimize storage usage for large volumes of IoT data with Azure SQL<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
- When ingesting messages from 100Ks devices, Azure SQL Database provides the ability to\u00a0batch<\/a>\u00a0multiple requests into a single database interaction, increasing overall scalability and maximizing resource utilization.<\/li>\n
- Cloud gateway<\/strong>\u00a0is managing bi-directional communications with devices, queueing events and messages, but usually also device provisioning and management.<\/li>\n