title | description | keywords | services | ms.service | ms.topic | author | ms.author | ms.reviewer | ms.date |
---|---|---|---|---|---|---|---|---|---|
Deploy Azure SQL Edge using the Azure portal |
Learn how to deploy Azure SQL Edge using the Azure portal |
deploy SQL Edge |
sql-edge |
sql-edge |
conceptual |
rothja |
jroth |
jroth |
09/22/2020 |
Azure SQL Edge is a relational database engine optimized for IoT and Azure IoT Edge deployments. It provides capabilities to create a high-performance data storage and processing layer for IoT applications and solutions. This quickstart shows you how to get started with creating an Azure SQL Edge module through Azure IoT Edge using the Azure portal.
- If you don't have an Azure subscription, create a free account.
- Sign in to the Azure portal.
- Create an Azure IoT Hub.
- Create an Azure IoT Edge device.
Note
To deploy an Azure Linux VM as an IoT Edge device, see this quickstart guide.
Azure Marketplace is an online applications and services marketplace where you can browse through a wide range of enterprise applications and solutions that are certified and optimized to run on Azure, including IoT Edge modules. Azure SQL Edge can be deployed to an edge device through the marketplace.
-
Find the Azure SQL Edge module on the Azure Marketplace.
-
Pick the software plan that best matches your requirements and click Create.
-
On the Target Devices for IoT Edge Module page, specify the following details and then click Create
Field Description Subscription The Azure subscription under which the IoT Hub was created IoT Hub Name of the IoT Hub where the IoT Edge device is registered and then select "Deploy to a device" option IoT Edge Device Name Name of the IoT Edge device where SQL Edge would be deployed -
On the Set Modules on device: page, click on the Azure SQL Edge module under IoT Edge Modules. The default module name is set to AzureSQLEdge.
-
On the Module Settings section of the Update IoT Edge Module blade, specify the desired values for the IoT Edge Module Name, Restart Policy and Desired Status.
[!IMPORTANT]
Do not change or update the Image URI settings on the module. -
On the Environment Variables section of the Update IoT Edge Module blade, specify the desired values for the environment variables. For a complete list of Azure SQL Edge environment variables refer Configure using environment variables. The following default environment variables are defined for the module.
Parameter Description MSSQL_SA_PASSWORD Change the default value to specify a strong password for the SQL Edge admin account. MSSQL_LCID Change the default value to set the desired language ID to use for SQL Edge. For example, 1036 is French. MSSQL_COLLATION Change the default value to set the default collation for SQL Edge. This setting overrides the default mapping of language ID (LCID) to collation. [!IMPORTANT]
Do not change or update the ACCEPT_EULA environment variable for the module. -
On the Container Create Options section of the Update IoT Edge Module blade, update the following options as per requirement.
- Host Port : Map the specified host port to port 1433 (default SQL port) in the container.
- Binds and Mounts : If you need to deploy more than one SQL Edge module, ensure that you update the mounts option to create a new source & target pair for the persistent volume. For more information on mounts and volume, refer Use volumes on docker documentation.
{ "HostConfig": { "CapAdd": [ "SYS_PTRACE" ], "Binds": [ "sqlvolume:/sqlvolume" ], "PortBindings": { "1433/tcp": [ { "HostPort": "1433" } ] }, "Mounts": [ { "Type": "volume", "Source": "sqlvolume", "Target": "/var/opt/mssql" } ] }, "Env": [ "MSSQL_AGENT_ENABLED=TRUE", "ClientTransportType=AMQP_TCP_Only", "PlanId=asde-developer-on-iot-edge" ] }
[!IMPORTANT]
Do not change thePlanId
enviroment variable defined in the create config setting. If this value is changed, the Azure SQL Edge container will fail to start. -
On the Update IoT Edge Module pane, click Update.
-
On the Set modules on device page click Next: Routes > if you need to define routes for your deployment. Otherwise click Review + Create. For more information on configuring routes, see Deploy modules and establish routes in IoT Edge.
-
On the Set modules on device page, click Create.
The following steps use the Azure SQL Edge command-line tool, sqlcmd, inside the container to connect to Azure SQL Edge.
Note
SQL Command line tools (sqlcmd) are not available inside the ARM64 version of Azure SQL Edge containers.
-
Use the
docker exec -it
command to start an interactive bash shell inside your running container. In the following exampleAzureSQLEdge
is name specified by theName
parameter of your IoT Edge Module.sudo docker exec -it AzureSQLEdge "bash"
-
Once inside the container, connect locally with sqlcmd. Sqlcmd is not in the path by default, so you have to specify the full path.
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourNewStrong@Passw0rd>"
[!TIP]
You can omit the password on the command-line to be prompted to enter it. -
If successful, you should get to a sqlcmd command prompt:
1>
.
The following sections walk you through using sqlcmd and Transact-SQL to create a new database, add data, and run a query.
The following steps create a new database named TestDB
.
-
From the sqlcmd command prompt, paste the following Transact-SQL command to create a test database:
CREATE DATABASE TestDB Go
-
On the next line, write a query to return the name of all of the databases on your server:
SELECT Name from sys.Databases Go
Next create a new table, Inventory
, and insert two new rows.
-
From the sqlcmd command prompt, switch context to the new
TestDB
database:USE TestDB
-
Create new table named
Inventory
:CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
-
Insert data into the new table:
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
-
Type
GO
to execute the previous commands:GO
Now, run a query to return data from the Inventory
table.
-
From the sqlcmd command prompt, enter a query that returns rows from the
Inventory
table where the quantity is greater than 152:SELECT * FROM Inventory WHERE quantity > 152;
-
Execute the command:
GO
-
To end your sqlcmd session, type
QUIT
:QUIT
-
To exit the interactive command-prompt in your container, type
exit
. Your container continues to run after you exit the interactive bash shell.
You can connect and run SQL queries against your Azure SQL Edge instance from any external Linux, Windows, or macOS tool that supports SQL connections. For more information on connecting to a SQL Edge container from outside, refer Connect and Query Azure SQL Edge.
In this quickstart, you deployed a SQL Edge Module on an IoT Edge device.