Exchanging PLC data with MS SQL servers
Learn how to transfer data between you PLCs and MS SQL using SIA Connect
Overview
This tutorial will guide you through how to connect to an MS SQL server on your SIA Connect, how to exchange data between instances such as PLCs and with the MS SQL server.
In this tutorial we will show how to exchange data from a Allen-Bradley PLC and a MS SQL server.
Exchange data between any PLC and MS SQL servers
SIA Connect can be configured as MS SQL server gateway for all the supported OT and IT protocols. This example takes base in Allen-Bradley / Rockwell PLC, but you can see the full list here of supported protocols and devices which can exchange data with MS SQL servers (and yes, you can even exchange data between MS SQL and other databases):
Supported Industrial & Buildings devices
Supported Clouds & IoT messaging
Supported databases
Installation linked article
Installation
When you receive the unit, some basic installation setup is required such as network configuration and electrical installation.
To install the solution as software please refer to the following instructions:
Introduction to the UI
If you are new to SIA connect it is recommended that you also look through our guide introducing you to the UI. This guide will grant you with a overview of how our UI looks, how you can interact with it and will guide you on if you want to know more about the individual functionalities.
Connectors used in this guide
Use this guide for learning how to install connectors on SIA Connect.
The following connectors are used for this guide:
- MS SQL - Used for setting up the MS SQL server
- EtherNet/IP - Used for connecting to the Allen-Bradley PLC
Connecting to the MS SQL server
The first thing we will do is create a link between SIA Connect and the MS SQL server. We do this using the MS SQL connector. Once we have the connector installed we need to make an instance for it.
For information on how to set up an MS SQL instance and items within use this guide.
Following the setup guide you will end up with an instance looking like this. (Remember to use your own IP and login data).
Verify the connection to the database
To activate the connection a single item needs to be added to the instance.
A select query statement can be used here.
For the Query Default this command is used (Remember to replace “IPCTable” with a table name from your database): select top 1 * from IPCTable order by id desc
If the data updates to match your database the connection is successful.
Connecting to the Allen-Bradley PLC
No PLC data
If you don't have any data available from a PLC, you can use the simulator connector to make the items needed for the mapping.
For connecting to the Allen-Bradley PLC we use the Ethernet/IP connector. You can follow this guide for how to create instances and items. A setup for a Allen-Bradley Micro800, where we use the Int_item address on the PLC to illustrate the functionality, could look like this. (Remember to use the IP that your PLC is on.)
Mapping items to the database
To exchange data between the PLC and the database we will use the SIA Connect functionality mapping.
For information on how to create mappings follow this guide.
First we go into the MS SQL server instance and create an item for inserting into the database.
We use the Input template rather than the Query Default, because this item will be part of a mapping. An Input template is executed whenever a mapping calls the item, whereas a Query Default is executed automatically at the configured read interval.
An example of the input template: INSERT INTO IPCTable(Column_1,Column_3) VALUES (%VALUE%,%VALUE.TIME.FORMAT%("yyyy_mm_ddThh:mm:ss_zzz")%')
TIME.FORMAT
We use TIME.FORMAT to change the formatting to fit the constraints regarding datetime values in the database.
You can read more about the formatting here.
We now create the mapping between the PLC data and the insert query.
When writing data to the PLC through the Int_Item, the same data is automatically mapped to the MS SQL database.
Example of how your it would look in a database:
At this point, you have a complete connection between the PLC and the database, with data flowing reliably from the PLC into your MS SQL instance. If your use case requires it, you can also configure the setup bidirectionally, allowing the database to write values back to the PLC. Queries can always be added or adjusted to match your specific needs. With this foundation in place, you’re ready to tailor the solution to your own environment.