Azure SQL Edge is a lightweight, edge-based database that enables real-time data processing and analytics at the edge of the network. It's designed for IoT, industrial automation, and other edge computing scenarios.
Azure SQL Edge supports a wide range of data types, including JSON, XML, and spatial data, making it a versatile choice for various applications.
For deployment, Azure SQL Edge can be run on a variety of devices, including ARM-based systems, and is available on popular operating systems like Windows, Linux, and Docker.
Azure SQL Edge also supports a range of security features, including encryption at rest and in transit, and authentication with Azure Active Directory.
Deployment and Management
Azure SQL Edge supports two deployment modes: connected deployment through Azure IoT Edge and disconnected deployment. Azure SQL Edge is available as a module for Azure IoT Edge, making it easy to deploy and manage.
You can deploy Azure SQL Edge as a standalone container or on a Kubernetes cluster by pulling the container images from Docker Hub. This flexibility allows you to choose the deployment method that best suits your needs.
To deploy Azure SQL Edge, you'll need to meet a few prerequisites, including having an active Azure subscription, logging into the Azure portal, and creating an Azure IoT Hub to serve as a backend for your devices.
You'll also need to register your IoT Edge Device in your hub and prepare your device to deploy the Edge modules of your choice.
Here are the steps to deploy Azure SQL Edge:
1. Subscription: ties the module to your subscription, which should match the subscription used for your IoT Hub.
2. IoT Hub: choose the option to "deploy to a device".
3. IoT Edge Device Name: the name of the device you want to deploy SQL Edge to.
When configuring the SQL Edge module, you'll need to define your environmental variables and set the properties and create options for your module. This includes your password, SQL Server language, and collation settings.
Here's an example of the configuration settings:
```json
{
"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"
]
}
```
After configuring the SQL Edge module, you'll need to specify your module to module and module to Hub routes, and then submit your deployment.
Editions and Features
Azure SQL Edge is available in two different editions or software plans: Azure SQL Edge Developer and Azure SQL Edge. These editions have identical feature sets, but differ in terms of their usage rights and the amount of CPU/memory they support.
The Azure SQL Edge Developer edition is for development only, and each container is limited to a maximum of 4 CPU cores and 32 GB of memory. This edition is perfect for testing and developing applications before moving to production.
Azure SQL Edge is designed for production, and each container is limited to a maximum of 8 CPU cores and 64 GB of memory. This edition provides the necessary resources for running applications in a production environment.
Azure SQL Edge includes built-in features for streaming that you can apply to complex event processing and real-time analytics. These features provide similar capabilities to those available through the Azure Stream Analytics on IoT Edge service.
Azure SQL Edge also includes machine learning features based on the Open Neural Network Exchange (ONNX) runtime. This enables you to develop ML models using the tools and languages of your choice, and then convert them for execution in SQL Edge.
Azure SQL Edge supports a subset of the features supported in SQL Server 2022 on Linux, in addition to some features that are currently not supported or available in SQL Server 2022 on Linux.
Features
Azure SQL Edge is a powerful tool that offers a range of features to support real-time data streaming and machine learning capabilities.
One of its key features is SQL streaming, which is based on the same engine that powers Azure Stream Analytics, providing real-time data streaming capabilities.
The T-SQL function call DATE_BUCKET is also supported, making it ideal for Time-Series data analytics.
Machine learning capabilities are included through the ONNX runtime, which enables flexible development of ML models using various tools and languages.
Here are the new features supported by Azure SQL Edge:
Azure SQL Edge's streaming capabilities are designed for resiliency, efficient bandwidth use, low-latency, and compliance, making it ideal for complex event processing and real-time analytics.
Unsupported Features
Azure SQL Edge has its limitations, and one area where it falls short is in database design. In-memory OLTP is not supported, which means you can't use related DDL commands and Transact-SQL functions, catalog views, and dynamic management views.
HierarchyID data type is also not supported, as is the Spatial data type. This might be a problem if you're working with location-based data or need to create complex hierarchies in your database.
Stretch DB is another feature that's not available, which is a shame because it can help improve query performance. Full-text indexes and search are also not supported, which means you can't use related DDL commands and Transact-SQL functions, catalog views, and dynamic management views.
FileTable and FILESTREAM are not supported either, which might be a problem if you're working with large files or need to store files in your database. Replication is also not supported, although you can configure Azure SQL Edge as a push subscriber of a replication topology.
Here's a list of some of the other unsupported features in Azure SQL Edge:
- In-memory OLTP
- HierarchyID data type
- Spatial data type
- Stretch DB
- Full-text indexes and search
- FileTable and FILESTREAM
- Replication
- PolyBase
- Language extensibility through Java and Spark
- Active Directory integration
- Database Auto Shrink
- Database snapshots
- Support for persistent memory
- Microsoft Distributed Transaction Coordinator
- Resource governor and IO resource governance
- Buffer pool extension
- Distributed query with third-party connections
- Linked servers
- System extended stored procedures
- CLR assemblies
- CLR-dependent T-SQL functions
- CLR-dependent date and time catalog views, functions, and query clauses
- Database mail
- Service Broker
- Policy Based Management
- Management Data Warehouse
- Contained Databases
- S3-compatible object storage integration
- Microsoft Entra authentication
- Buffer pool parallel scan
- Hybrid buffer pool with direct write
- Concurrent updates to global allocation map (GAM) pages and shared global allocation map (SGAM) pages
- Integrated acceleration & offloading (Intel QAT)
- Intelligent Query Processing
- SQL Server Agent
- High Availability
- Security
- Services
- Manageability
These are just a few of the many features that are not supported in Azure SQL Edge. It's essential to understand what's not available before deciding to use this edition of SQL Server.
Frequently Asked Questions
Is Azure SQL Edge free?
Azure SQL Edge has no upfront cost, but you'll pay a low annual fee starting at $60 per device. Learn more about how to get started with Azure SQL Edge at a cost that fits your needs.
How do I connect to Azure SQL Edge?
To connect to Azure SQL Edge, use the "docker exec -it" command to access the container and then connect locally with sqlcmd. This interactive process allows you to manage and interact with your Azure SQL Edge instance.
Sources
- https://learn.microsoft.com/en-us/azure/azure-sql-edge/overview
- https://raima.com/azure-sql-database-edge-how-to-deploy-backup-and-restore/
- https://www.kevinrchant.com/2023/05/03/spreading-your-sql-server-wings-with-azure-sql-edge/
- https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/azure-sql-edge/features.md
- https://learn.microsoft.com/en-us/azure/azure-sql-edge/release-notes
Featured Images: pexels.com