Getting started with Azure SQL Edge

Microsoft scaled down its flagship database, squeezing it into 500MB and running it on edge hardware.

Getting started with Azure SQL Edge
Dell Technologies

The cloud is becoming increasingly distributed, with container technologies allowing easy deployment of what had been cloud functionality to devices at the edge of the network. What began with function-as-a-service runtimes has now graduated to supporting PaaS tools and technologies, stretching the cloud platform from public hyperscale data centers down to low-cost devices running on your network.

Microsoft has been talking about “the intelligent cloud and the intelligent edge” for a long time now, with a focus on finding ways around the bandwidth crunch between data sources and data processing in cloud architectures. There’s a certain level of self-interest in this approach, with hyperscale data centers close to cheap power but a long way from metropolitan population densities and relatively cheap bandwidth. If power is cheap and data is expensive, then why not process it close to the source?

SQL Server everywhere

At the heart of much of Microsoft’s cloud platform is its SQL Server database, now grown into a family of databases that work across public and private clouds; at desktop, data center, and hyperscale; and on both Windows and Linux. That family of databases has recently been joined by a new member: Azure SQL Edge. Designed for IoT (Internet of Things) and edge gateway scenarios on Intel and ARM hardware, it’s a small, containerized database that can operate both while connected and disconnected, providing a place to preprocess and stream data that can be developed remotely and delivered and managed from Azure.

Like the rest of the SQL Server family, Azure SQL Edge is a relational database with nonrelational capabilities. You can use it for traditional table-based storage, or instead work with JSON documents, with graph content, or with time-series data. That combination ensures you can use it for many different roles, but with a focus on IoT applications. It’s built on the SQL Server engine so there’s no need to learn new skills; the same techniques and programming tools you use on larger SQL Server instances work just as well on Azure SQL Edge, with T-SQL queries and functions developed elsewhere fully supported.

Azure SQL Edge deployment options

Azure SQL Edge can be deployed in two different ways. If you’re using Azure IoT Edge to deploy and manage IoT applications, you can find it on the Azure Marketplace and deploy it directly into your instances from the Azure Portal. If you’re using it on unmanaged systems, then it’s available in a container in Docker Hub and can be downloaded and run as a stand-alone container or used as part of a Kubernetes container orchestration. This last option is particularly interesting if you’re considering working with one of the edge-focused Kubernetes implementations, such as Canonical’s MicroK8s or Rancher’s K3s.

It’s important to remember that Azure SQL Edge is a variant of the Linux SQL Server release, and its container’s base operating system is Ubuntu 18.04. To get support, any host will need to run either 18.04 or 20.04 LTS releases of Ubuntu (so you can build and test applications on any Windows 10 PC running Windows Subsystem for Linux). You can use other Linux hosts or even the Windows version of Docker, but they won’t be tested and may not have all the services and applications needed to run your database.

Once a container instance is up and running, you can connect to it using the built-in command line tools (if you’re not running on ARM64, which doesn’t currently support sqlcmd). From here you can create tables and test queries, much like any other SQL Server database. In practice, you’re more likely to work with it remotely, using your choice of management and development tools. You need to ensure that appropriate container ports are open as part of the initial configuration process to use external tools.

Much of Azure SQL Edge is designed to support traditional database operations; there’s additional support for basic stream analytics to work with real-time data from devices. You can use this to build applications that filter data before uploading to a central processing application in Azure, or as a feed into machine-learning applications that work with models developed on Azure or development PCs and exported as ONNX (Open Neural Network Exchange).

Designing database applications for the edge

You don’t need new tools to manage or develop for Azure SQL Edge. It’s designed to work with existing SQL Server development tools, so you can continue to work as usual when designing databases and building triggers or stored procedures. If you’re developing against a live instance on lab hardware, you might want to take advantage of both the remote development and SQL Server extensions for Visual Studio Code. Being able to build, test, and debug applications in a single environment will help keep development time to a minimum.

As the container is only 500MB, it’s possible to run Azure SQL Edge on smaller devices such as the 8GB Raspberry Pi 4, especially after recent firmware updates have allowed it to be booted off SSD rather than SD cards. SSD storage makes devices like the Pi faster and more reliable; relatively slow and cheap SD card storage is not designed to handle the write cycles of a modern database.

Azure SQL Edge may be a variant of SQL Server, but you shouldn’t expect to be able to bring all your existing applications to the edge, at least not without some modifications. Azure SQL Edge is designed to run only a subset of the core SQL Server database services, so you won’t have access to other parts of the platform, like Analysis or Reporting Services or most of its Machine Learning tools. In practice that shouldn’t be too much of a problem, as those features work best either on interactive systems or with the horsepower necessary for building and training models with large data sets. Using ONNX for inferencing on the edge allows you to deliver pretrained models, ready to filter data or flag exceptions for further analysis.

Although Azure SQL Edge can operate with a direct connection to Azure SQL instances in the cloud, it’s designed to function as a stand-alone database on disconnected, low-power systems. With that in mind, you should build applications that don’t rely on connected features such as stretch databases or memory-intensive tools like OLTP, as they’re not part of the Azure SQL Edge build.

You get what you pay for

Licensing is relatively simple. There are two editions: one for production, one for development. The development SKU supports as many as four cores and 32GB of memory; the production version supports as many as eight cores and 64GB of memory. Those are relatively high specifications for edge hardware, but they’re what you’d expect from hub devices at the network edge. In practice, this hardware would collate and process data from multiple edge devices. Pricing starts at $10 per device per month, with reductions for one-year and three-year reserved instances that can bring it down to $60 per device per year.

Even though the price for Azure SQL Edge is relatively low, you do need to compare it with open source alternatives that can interoperate with cloud databases. It has an advantage if you’re already building SQL Server applications as part of an IoT strategy, if you need to take some of their functionality to the edge in order to manage bandwidth costs, or if you need to operate in a partially connected mode, leaving edge hardware operating autonomously for much of the time.

Copyright © 2020 IDG Communications, Inc.

InfoWorld Technology of the Year Awards 2023. Now open for entries!