Azure SQL數據庫邊緣:如何部署,備份和還原

8 月 12

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可通過Azure市場作為Azure IoT Edge的模塊使用,有兩個版本-用於生產部署的Azure SQL Edge和僅用於開發的Azure SQL Edge Developer。 

 

在本文中,您將學習: 

全球網絡的概念。物聯網(IoT)。 ICT(信息通信網絡)。具有網絡連接的物理設備網絡

什麼是Azure SQL數據庫邊緣 

Azure SQL Database Edge is an optimized database engine for Internet of Things (IoT) and IoT Edge workloads that require 分佈式存儲 策略。它也被稱為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基於與以下引擎相同的引擎 Azure SQL數據庫。這使它能夠在雲資源,數據中心和IoT Edge設備之間提供T-SQL功能和可移植性。  

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. 

 

這些版本是:  

  • Azure SQL邊緣—意味著生產部署。此版本提供對具有最多8個CPU內核和64GB內存的Edge容器的訪問。  
  • Azure SQL Edge開發人員—meant for development only. This version provides access to Edge containers with up to four CPU cores and 32GB of memory.  


Azure SQL Edge功能 

Azure SQL數據庫邊緣的兩個主要功能使其脫穎而出-流數據的能力及其機器學習功能。 

 

流媒體功能 

SQL Edge包括用於流傳輸的內置功能,可以將其應用於復雜的事件處理和實時分析。這些功能旨在實現彈性,高效使用帶寬,低延遲和合規性。  

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. 

 

機器學習能力 

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.

 

如何部署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 這裡.  

 

先決條件 

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. 
  • 登錄到Azure門戶。 
  • 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. 
  • 在集線器中註冊IoT Edge設備。您可以通過門戶,Visual Studio Code或CLI來註冊設備。 
  • Preparing your IoT Edge device to deploy the Edge modules of your choice. You can add modules to your device through the Portal. 

 

部署Azure SQL Edge 

滿足先決條件後,請執行以下步驟將SQL Edge部署到設備。  

  1. 在裡面市場,找到Azure 的SQL 邊緣模塊.
  1. 找到適合您要求的選項,然後選擇創造.
  1. 在裡面物聯網邊緣模塊的目標設備 屏幕上,定義以下內容。完成後,選擇 創造
  • 訂閱—ties the module to your subscription. This should match the subscription used for your IoT Hub. 
  • 物聯網中心—在其中註冊了所需設備的集線器。您應該選擇“部署到設備”選項。  
  • IoT Edge設備名稱—the name of the device you want to deploy SQL Edge to.
  1. 在“部署模塊”部分設置模塊 頁上,單擊 配置 SQL Edge模塊旁邊的按鈕。
  1. 導航到IoT Edge定制 模組 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.
  1. 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. 

您可以在下面看到定義這些設置的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"
]
}
  1. 接下來,仍然在IoT Edge自定義模塊 屏幕上,進行以下設置並選擇 保存。 
  • 慾望ed狀態 跑步  
  • 重新啟動政策 總是
  1. 您應該被帶到設置模塊 頁。點擊 下一個 並指定您的模塊到模塊和模塊到集線器路由。完成後,您可以點擊 下一個 然後 提交 完成部署。
     

如何在Azure SQL Edge中備份和還原數據庫 

Azure SQL Database Edge基於最新版本的Linux SQL Server數據庫引擎(了解有關運行的更多信息 Azure上的Linux工作負載)。這種共享基礎使您可以像其他任何SQL數據庫一樣備份和還原SQL Edge數據庫。  

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.  

 

備份到本地磁盤 

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. 

 

  1. 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 

 

  1. 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

 
  1. 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. 
 
從本地磁盤還原 

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'

 

如何利用邊緣處理

在某些情況下,邊緣設備具有備用處理能力和可額外利用的小型本地存儲。在這種情況下,最好使用像Raima's這樣的以物聯網邊緣為中心的數據庫 Raima數據庫管理器(RDM) 與僅使用Azure SQL數據庫邊緣相比,可以更有效地利用資源。

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.

 

結論 

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.  

Get notified about new RDM updates

Be the first to know about new Raima Database Manager updates when they go live, use cases, industry trends and more.