Azure SQL Database Edge enables you to create high-performance data storage and processing layers for Internet of Things (IoT) and IoT Edge workloads. SQL Edge provides capabilities for streaming and analyzing relational and non-relational data.
SQL Edge is available through the Azure Marketplace, as a module for Azure IoT Edge, and in two versions—Azure SQL Edge for production deployments, and Azure SQL Edge Developer for development only.
In this article, you will learn:
- What is Azure SQL Database Edge
- Azure SQL Edge features
- How to deploy Azure SQL Edge
- How to backup and restore databases in Azure SQL Edge
- How can edge processing be utilized
What Is Azure SQL Database Edge
Azure SQL Database Edge is an optimized database engine for Internet of Things (IoT) and IoT Edge workloads that require distributed storage strategies. It is also known as SQL Edge.
The SQL Edge engine enables you to create a high-performance processing and data storage layer for your IoT devices and applications. It includes features for streaming, processing, and analyzing relational and non-relational data, including time-series, graph, and JSON data.
SQL Edge is based on the same engine as Azure SQL Database. This enables it to provide T-SQL functionality and portability between your cloud resources, data centers, and IoT Edge devices.
You can access SQL Edge through the Azure Marketplace, where it is available as a module for Azure IoT Edge. You can choose from two editions that differ based on the CPU and memory resources you need and your required usage rights.
These versions are:
- Azure SQL Edge—meant for production deployments. This version provides access to Edge containers with up to eight CPU cores and 64GB of memory.
- Azure SQL Edge Developer—meant for development only. This version provides access to Edge containers with up to four CPU cores and 32GB of memory.
Azure SQL Edge Features
Two main capabilities make Azure SQL Database Edge stand out—its ability to stream data and its machine learning features.
Streaming capabilities
SQL Edge includes built-in features for streaming that you can apply to complex event processing and real-time analytics. These features are designed for resiliency, efficient bandwidth use, low-latency, and compliance.
These features are based on the same design as Azure Stream Analytics. Features provide similar capabilities to those available through the Azure Stream Analytics on IoT Edge service.
Machine learning capabilities
Azure SQL Database Edge includes built-in analytics and machine learning (ML) features based on the Open Neural Network Exchange (ONNX) runtime. This format enables you to exchange neural network and deep learning models between ML frameworks.
Using the ONNX runtime, you can flexibly develop ML models using the tools and languages of your choice. Once completed, you can then convert these models with ONNX and execute them in SQL Edge.
How to Deploy Azure SQL Edge
Below is a brief tutorial showing how to deploy SQL Edge to your cloud environment. This tutorial is adapted from a more detailed walkthrough in the Azure documentation. You can find this walkthrough here.
Prerequisites
Before you can get started deploying Azure SQL Database Edge, there are a few prerequisites you need to take care of. These include:
- Having an active Azure subscription. If you need an account, free trial accounts are available.
- Logging into the Azure portal.
- Creating an Azure IoT Hub to serve as a backend for your devices. You can create this hub through the Azure Portal from the Marketplace.
- Registering your IoT Edge Device in your hub. You can register your device through the Portal, in Visual Studio Code, or through the CLI.
- Preparing your IoT Edge device to deploy the Edge modules of your choice. You can add modules to your device through the Portal.
Deploying Azure SQL Edge
Once your prerequisites are in place, take the following steps to deploy SQL Edge to your devices.
- In theMarketplace, find the Azure SQL Edge module.
- Find the option that fits your requirements and selectCreate.
- In theTarget Devices for IoT Edge Module screen, define the following. When done, choose Create.
- Subscription—ties the module to your subscription. This should match the subscription used for your IoT Hub.
- IoT Hub—the hub where your intended device is registered. You should choose the option to “deploy to a device”.
- IoT Edge Device Name—the name of the device you want to deploy SQL Edge to.
- In the deployment modules section of theSet Modules page, click the Configure button next to the SQL Edge module.
- Navigate to theIoT Edge Custom Modules screen. In this pane, you need to define your environmental variables and set the properties and create options for your module. These include your password, SQL Server language, and collation settings.
- On the same screen, define the correctHostPort for your container. If you are deploying more than one module, you also need to update your mount option so a new source and target pair are created for your persistent volumes.
You can see an example of the JSON file defining these settings below:
{
"HostConfig": {
"Binds": [
"sqlvolume:/sqlvolume"
],
"PortBindings": {
"1433/tcp": [
{
"HostPort<": "1433"
}
]
},
"Mounts": [
{
"Type": "volume",
"Source": "sqlvolume",
"Target": "/var/opt/mssql"
}
]
},
"Env": [
"MSSQL_AGENT_ENABLED=TRUE",
"MSSQL_PID=Developer"
]
}
- Next, still in theIoT Edge Custom Modules screen, set the following and choose Save.
- Desired Status to running
- Restart Policy to always
- You should be taken to theSet modules page. Click Next and specify your module to module and module to Hub routes. Once this is done, you can click Next and then Submit to finalize your deployment.
How to Backup and Restore Databases in Azure SQL Edge
Azure SQL Database Edge is based on the most recent version of the Linux SQL Server Database Engine (learn more about running Linux workloads on Azure). This shared base makes it possible to backup and restore your SQL Edge database just like any other SQL database.
Backing up your SQL Edge deployment helps you minimize the chance of data loss and preserves versions prior to modifications. This preservation can help you rollback changes made in error and enable recovery from database failures.
Backup to local disk
To backup your database to a local disk, you can use the T-SQL BACKUP DATABASE command. Below is a quick guide how to perform a backup with this method.
- Start by creating a folder for your backups in the container you are backing up. Then, on the host where your instance is running, execute the following command. Be sure to substitute your container name where indicated.
sudo docker exec -it yourEdgeContainer mkdir /var/opt/mssql/backup
- Through Azure Data Studio or the SQL Server Management Studio (SSMS), connect to your Edge instance. Once connected, run the following command with the proper substitutions:
BACKUP DATABASE [yourDatabaseName]
TO DISK = N'/var/opt/mssql/backup/yourDatabaseBackup.bak'
WITH NOFORMAT, NOINIT, NAME = N'yourDatabaseName-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
- As long as the command is executed successfully, you should see an output similar to the following in the results section of the tool you used.
10 percent processed.
...
...
100 percent processed.
Processed 51652 pages for database 'yourDatabaseName', file 'yourDatabaseName' on file 1.
Processed 2 pages for database 'yourDatabaseName', file 'yourDatabaseName_log' on file 1.
BACKUP DATABASE successfully processed 51652 pages in 3.658 seconds (114.451 MB/sec.
Restore from a local disk
Once backups are made, you can then restore those backups when needed. You can also use backups to create mirrored databases for testing or development.
If your backup file isn’t already stored in your target container, move the file there first. You can then connect to your SQL Edge instance using the same method as you used to create the backup. Once connected, run the following command, substituting your resource and file names where indicated.
Restore FilelistOnly from disk = N'/var/opt/mssql/backup/yourDatabaseBackup.bak'
Restore Database yourDatabaseName_2
From disk = N'/var/opt/mssql/backup/yourDatabaseBackup.bak'
WITH MOVE 'yourDatabaseName' TO '/var/opt/mssql/data/yourDatabaseName_Primary_2.mdf',
MOVE 'yourDatabaseName_log' TO '/var/opt/mssql/data/yourDatabaseName_Primary_2.ldf'
How can Edge Processing be Utilized
In some cases, the edge devices have spare processing power and small local storage that can be additionally utilized. In such a situation, it may be better to use an IoT edge focused database like Raima’s Raima Database Manager (RDM) to utilize the resources even more efficiently than only using Azure SQL Database Edge.
RDM allows for the application developer to run a very small footprint database on the resource-constrained edge device. They can do some local processing of the data before sending it over to be consumed on the Azure instance. This reduces the amount of data being transferred over the network. Additionally, RDM allows for some local calculations from the application with a live set of data and data buffering/backup to occur should there be a connection interruption. Using RDM gives more flexibility to the developer in terms of what their applications can do without a dependency on maintaining a connection to an Azure instance.
Conclusion
Azure SQL Database Edge comes with a set of powerful capabilities, which you can leverage to stream and analyze IoT and edge data. SQL Edge is based on the same engine as Azure SQL Database, and provides portability across cloud resources, as well as connected data sources and devices. The brief tutorial in this article should provide you with the basic principles of deploying Azure SQL Edge, as well as backing up and restoring your data. If a data connection is unreliable or there is a need for local device processing, Raima’s RDM may be a better fit than Azure as well. For more information, you can check out the official documentation.
