---
title: Microsoft SQL Server
slug: bhdo/microsoft-sql-server
createdAt: 2022-11-25T19:01:27.000Z
---

You can generate data from an edge device, then connect and store the data in the MSSQL database using the DB-Microsoft SQL Server connector. Data is collected from the selected device.

See the following for more information:

- [Microsoft SQL Server - Ubuntu based images](https://hub.docker.com/_/microsoft-mssql-server)
- [sqlcmd Utility](https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15)
- [Deploy and connect to SQL Server Docker containers](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-docker-container-deployment?view=sql-server-ver15\&pivots=cs1-bash)

# Step 1: Add Device

Follow the steps to [Connect a Device](https://public-docs.litmus.io/bhdo/connect-a-device). The device will be used to store tags that will be eventually used to create outbound topics in the connector. Make sure to select the **Enable Data Store** check box.&#x20;

For this use case, select the **Simulator&#x20;**&#x64;evice and **Simulator (Gen1)** driver and enter the name **PLC1**.

# Step 2: Add Tags

After connecting the device in Belden Horizon Data Operations, you can [Add Tags](https://public-docs.litmus.io/bhdo/add-tags) to the device. Create tags that you want to use to create outbound topics for the connector.&#x20;

For this use case, add two tags with the following details.&#x20;

- **Name**: Select **int-integer**.&#x20;
- **Value Type**: Select **int**.&#x20;
- **Tag Name**: Enter **int\_plc1**.&#x20;
- **Polling Interval**: Enter **1**.&#x20;
- **Address**: Enter **12**.&#x20;



- **Name**: Select **float-float**.&#x20;
- **Value Type**: Select **float**.&#x20;
- **Tag Name**: Enter **float-plc1**.&#x20;
- **Polling Interval**: Enter **1**.&#x20;
- **Address**: Enter **13**.&#x20;

# Step 3: Add Microsoft SQL Server Application

:::hint{type="info"}
**Note**: The version used in this use case for Microsoft SQL Server is 2017-latest.
:::

**To add the Microsoft SQL Server Marketplace Application:**

1. In Belden Horizon Data Operations, navigate to **Applications&#x20;**> **Marketplace**.
2. Click **Marketplace List** and select **Default Marketplace Catalog**.
3. Click the **Microsoft SQL Server&#x20;**&#x61;pplication tile.&#x20;
4. From the **Installation script version** drop-down list, select **2017-CU8-ubuntu**.&#x20;
5. Enter **MSSQL&#x20;**&#x69;n the **Name&#x20;**&#x66;ield. &#x20;
6. Copy the password from the **SA Password&#xA0;**&#x66;ield or create a new one and save it for use later.
7. Click **Launch**.&#x20;
8. Navigate to **Applications&#x20;**> **Catalog Apps**.&#x20;
   The Microsoft SQL Server application appears, pulls the image, and then starts the application. The MSSQL application shows as running.
9. Navigate to **Applications&#x20;**> **Containers**.&#x20;
   The MSSQL application Container is running.
10. Copy the IP address for the MSSQL application.

# Step 4: Add the Microsoft SQL Server Connector

Follow the steps to [Add a Connector](https://public-docs.litmus.io/bhdo/add-a-connector) and select the **DB - MIcrosoft SQL Server&#x20;**&#x70;rovider.&#x20;

Configure the following parameters.&#x20;

- **Name**: A recognizable name to identify the connector.&#x20;
- **Hostname**: Paste the IP address you copied in Step 3. &#x20;
- **Port**: The MSSQL Server port. The default value is **1433**.&#x20;
- **Username**: Enter **sa**.&#x20;
- **Password&#x20;**(Optional): Enter the user password you copied in Step 3.&#x20;
- **Database**: Enter **master**.&#x20;
- **Table**: Enter **test\_table**.&#x20;
- **Show Mapping**: If you want to send data to a custom table, select this check box and unselect *Create table*. See [Work with Tables in SQL Connectors (Create Table and Show Mapping)](https://public-docs.litmus.io/bhdo/work-with-tables-in-sql-connectors-create-table-and-show-mapping) to learn more. To add key/value pairs for the custom table, see [Configure Key/Value Pairs](https://public-docs.litmus.io/bhdo/work-with-tables-in-sql-connectors-create-table-and-show-mapping#configure-keyvalue-pairs).&#x20;
- **Create table**: If you want to send data to an existing table in the default format, or you want to create a new table in the default format, select this check box and unselect *Show Mapping*. See [Work with Tables in SQL Connectors (Create Table and Show Mapping)](https://public-docs.litmus.io/bhdo/work-with-tables-in-sql-connectors-create-table-and-show-mapping) to learn more.&#x20;
- **Throttling limit**: The maximum number of messages per second to be processed. The default value is zero, which means that there is no limit.&#x20;
- **Persistent storage**: When enabled, this will cause messages to undergo a store-and-forward procedure. Messages will be stored within Belden Horizon Data Operations when cloud providers are online. &#x20;

# Step 5: Enable the Microsoft SQL Server Connector

**To enable the connector:&#x20;**

1. Return to the *Belden Horizon Data Operations Integration* browser tab.
2. Click the connection toggle on the connector tile.&#x20;
   The connector shows connected after verifying the certificate, username, and password.

   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/606DvoZX5g2pfL9e-8Bw4_microsoft.png" size="60" width="351" height="262" caption="Toggle to enable connector " position="center" showCaption="true"}

# Step 6: Create Topics for Microsoft Connector

You will now need to import the tags created in Step 2 as topics for the Microsoft connector. The topics will be created as outbound topics.&#x20;

**To create outbound topics:**

1. Click the connector tile.
   The connector *Dashboard&#x20;*&#x61;ppears.&#x20;
2. Click the **Topics&#x20;**&#x74;ab.&#x20;
3. Click the **Import from DeviceHub** tags icon.&#x20;
   The *DeviceHub Import&#x20;*&#x64;ialog box appears.

   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/4fC8yaOKctxiiTvUDGfWu_import-from-devicehub.png" size="80" width="1148" height="570" position="center" showCaption="false"}
4. Select all the tags to import and click **Import**.&#x20;

After importing the tags, click the toggle in the connector tile to restart the connection.&#x20;

::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/rW-XEEUxmrLyHjo5FMlqJ_microsoft.png" size="60" width="351" height="262" caption="Toggle to enable connector" position="center" showCaption="true"}

# Step 7: Enable Topics

To enable the topics, return to the Topics tab and click the **Enable all topics** icon.&#x20;

::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/gIcY2lZ9Axa2ssaWk9yL5_enable-all-topics.png" size="80" width="1148" height="570" caption="Enable all topics icon" position="center" showCaption="true"}

# Step 8: Create Flow

You can now create a flow in Belden Horizon Data Operations to verify the connection.&#x20;

**To create the flow:&#x20;**

1. In Belden Horizon Data Operations, navigate to **Flows Manager**.&#x20;
2. Click the **Go To Flow Definition** icon for a selecte&#x64;**&#x20;Flows Manager**.&#x20;
   The *Flow canvas* opens in a new browser tab.&#x20;

   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/VlQh8Cy425HSbOCoHkQiP_mqtt-lwtgo-to-flow.png" size="80" width="1095" height="427" caption="Go To Flow Definition icon" position="center" showCaption="true"}
3. From the node palette, drag the **DataHub Subscribe** node (DataConnector section) to the canvas.

   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/j2Sz7jq3aibQ0V56l3Cy8_datahubsubscribenode.png" size="80" width="1055" height="690" caption="DataHub Subscribe node" position="center" showCaption="true"}
4. Drag the **Debug&#xA0;**&#x6E;ode to the canvas and connect the two nodes.
5. Double click the **DataHub Subscribe** node.&#x20;
   The *Edit DataHub Subscribe Node&#x20;*&#x64;ialog box appears.
6. In the **Topic&#x20;**&#x66;ield, paste the topic name copied in Step 6.
7. Enter **tag imported to mssql db** in the **Name&#xA0;**&#x66;ield.
8. If needed, configure the *Datahub Subscribe* connection. See the "Step 3: Configure Connector Nodes" section in [Create a Flow](https://public-docs.litmus.io/bhdo/create-a-flow#step-3-configure-connector-nodes) to learn more.&#x20;
9. Click **Done**.&#x20;
10. Click **Deploy**.&#x20;
11. Drag the **Sidebar&#xA0;**&#x62;eneath the flow up and click the **Debug&#xA0;**&#x69;con. Verify that messages are displaying.&#x20;

    ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/gPJk6NsW3loIyEUdOJWuM_debugicon.png" size="80" width="885" height="365" caption="Debug node messages" position="center" showCaption="true"}

# Step 9: Make Queries in the Container Terminal

**To make queries in the container terminal:**

1. In Belden Horizon Data Operations, navigate to **Applications&#x20;**>**&#x20;Containers**.
2. Click the **Terminal&#xA0;**&#x69;con next to the MSSQL container.
   The MSSQL shell opens.
3. From the MSSQL container terminal, enter **/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P \<sa password>&#xA0;**, and then press **ENTER**.
4. Enter **select DB\_NAME ()**, and then press **ENTER**.
5. Enter **go**, and then press **ENTER**.
   The master database name appears.
6. Enter **select name from sys.databases**, and then press ENTER.
7. Enter **go**, and then press **ENTER**.
   All database names appear.
8. Enter **use master**, and then press **ENTER**
9. Enter **go**, and then press **ENTER**.****
   Changed database context to *master&#x20;*&#x61;ppears.
10. Enter **select \* from information\_schema.tables;** and press **ENTER**.
11. Enter **go**, and then press **ENTER**.
    The *Table Catalog* for the master database appears.
12. Enter **select \* from test\_table**, and then press **ENTER**.
13. Enter **go**, and then press **ENTER**.
    Identification Messages from the Tag appear, including the *deviceId&#x20;*&#x61;nd *registerId&#x20;*&#x61;t the end of each message.
14. Return to the flows canvas browser tab and check the *deviceId&#x20;*&#x61;nd *registerId&#x20;*&#x74;o ensure they are the same.

