Database Gateway

(DBGW)

Software for data exchange between SQL-Databases and PLCs

In detail

Table of Contents

1. How it works

2. PLC Instruction DBGW

3. Windows DBGW-Service

4. How to exchange SQL data with PLC data

5. Implementation possibilities

1. How it works:

  • The Windows DBGW-Service is a TCP-server that listens on a specific port (default 11201), and the instruction DBGW in the PLC is a TCP-Client.
  • All communication is initiated by triggering the DBGW instruction in the PLC.
  • The PLC instruction DBGW communicates with the Windows DBGW-Service through an encrypted proprietary TCP-protocol.
  • Windows DBGW-Service communicates with a Microsoft SQL Database using .NET Framework Data Provider with or without encryption.
  • SQL-query, placed in a string tag(s), is send to the DBGW-Service, which will pass it through to the SQL Database. Usernames and passwords for accessing SQL Database are exclusively handled by Windows DBGW-Service.
  • Depending on executed SQL-query, none or several records are received from the database and copied into PLC instruction DBGW’s tag-array.
  • New PLCs with new SQL-queries can be added, or existing SQL-queries can be changed or removed without touching the Windows DGBW-Service.
  • The PLC instruction DBGW is licensed to the PLC’s unique serial number. No license on Windows DBGW-Service program.

2. PLC instruction DBGW:

1. Can be implemented with PLC in RUN-mode

2. No tag limits, only available PLC memory is the limit for number of records that can be received from the database

3. Each record can consist of maximum 32 Boolean- and 100 Integer- and 100 Real and 100 String-columns. Equal to max 332 table columns

4a. RA: Array length for Integers, Reals and Strings are individually configurable (Range 1 to 100)

4b. Siemens: *Preconfigured array length for Integers, Reals and Strings are available (Length= 5 & 10)

5a. RA: Array length for Boolean is fixed (Always 32)

5b. Siemens: *Preconfigured array length for Boolean (Length= 8 & 16)

6a. RA: String length are configurable to fit column definition in database

6b. Siemens: *Preconfigured String length (Length= 40)

7a. RA: Maximum number of records that can be received is configurable (No limit, depends on free PLC memory)

7b. Siemens: *Preconfigured number of records that can be received (Records = 1, 10 & 25)

Remark * : Preconfigured sizes can be changed to fit customer request

9. Maximum number of bytes in one record is configurable (RA: Max 4000 bytes. Siemens: Max 1460 bytes)

10. Maximum number of bytes per TCP packet that can be received from Windows DBGW-service, is configurable (Max 1460 bytes)

11. Maximum number of bytes per TCP packet that can be send to Windows DBGW-service, is configurable (RA: Max 3000 bytes. Siemens: Max 1460 bytes)

12. Multiple DBGW instructions can be imported into each PLC-program. Using the same license

13. Supported SQL datatypes:

SQL DatatypePLC datatype
VarcharString
NVarchar (PLC only supports None Unicode (1 byte characters), Unicode are not supported (2 bytes for special characters)String
CharString
NChar (PLC only supports None Unicode (1 byte characters), Unicode are not supported (2 bytes for special characters)String
DatetimeString
UniqueidentifierString
TinyIntDINT
SmallIntDINT
IntegerDINT
BigInt (PLC only supports from -2147483648 to 2147483647)DINT
Float (PLC only supports Single-precision floating-point format, 4 byte)REAL
Real (PLC only supports Single-precision floating-point format, 4 byte)REAL
Decimal (PLC only supports Single-precision floating-point format, 4 byte)REAL
Numeric (PLC only supports Single-precision floating-point format, 4 byte)REAL
MoneyREAL
BitBOOL

14. System variables. Makes queries easier, especially regarding handling date and time

System variableDescription
@TIMESYNC@Used for timesynchronization of PLC clock with SQL-Server datetime
@SN@Serial number: Will be replaced by PLC’s unique serial number
@TS@Timestamp: Will be replaced by actual datetime on SQL-Server during insert & update
@TD@This Day: Will be replaced by actual date
@TW@This Week: Will be replaced by actual start date for this week
@TM@This Month: Will be replaced by actual start date for this month
@TY@This Year: Will be replaced by actual start date for this year
@LxxH@Last xx Hours: Will be replaced by start datetime xx hours back in time
@LxxD@Last xx Days: Will be replaced by start datetime xx days back in time
@LxxM@Last xx Month: Will be replaced by start datetime xx months back in time

15. Supported Rockwell Automation PLCs and Ethernet modules:

Rockwell Automation PLCs and Ethernet modulesFirmware version
5370 CompactLogix 1769-L3y Controllers:
1769-L30ER(M)(S)
1769-L30ER-NSE,
1769-L33ER(M)(S)
1769-L36ERM(S),
1769-L37ERM(S)
1769-L38ERM(S)

5370 CompactLogix 1769-L2y Controllers:
1769-L24ER-QB1B
1769-L24ER-QBFC1B
1769-L27ERM-QBFC1B

5370 CompactLogix 1769-L1y Controllers:
1769-L16ER
1769-L18ER(M),
1769-L19ER

5380 CompactLogix 5069-L3z:
5069-L306ER(M)(S2),
5069-L310ER(M)(S2)
5069-L310ER-NSE
5069-320ER(M)(S2)
5069-330ER(M)(S2)
5069-340ER(M)(S2)
5069-350ER(M)(S2)
5069-380ER(M)(S2)
5069-3100ER(M)(S2)

5580 ControlLogix Controllers:
1756-L81E(S)
1756-L82E(S)
1756-L83E(S)
1756-L84E(S)
1756-L85E

1756-EN2T, 1756-EN2F, 1756-EN2TR, 1756-EN3TR ControlLogix® EtherNet/IP communication modules
1756-EWEB ControlLogix EtherNet/IP web server module
1768-EWEB CompactLogix EtherNet/IP web server module
1756-EN4TR
From v30.0








From v30.0




From v30.0




From v30.0









From v30.0






From v5.007
From v4.006
From v1.002
From v24.00.00

16. Supported Siemens PLCs:

PLC TypeFirmware version
S7-1200
6ES7211-1BE40-0XB0
6ES7211-1AE40-0XB0
6ES7211-1HE40-0XB0
6ES7212-1BE40-0XB0
6ES7212-1AE40-0XB0
6ES7212-1HE40-0XB0
6ES7214-1BG40-0XB0
6ES7214-1AG40-0XB0
6ES7214-1HG40-0XB0
6ES7215-1BG40-0XB0
6ES7215-1AG40-0XB0
6ES7215-1HG40-0XB0
6ES7217-1AG40-0XB0
6ES7212-1AF40-0XB0
6ES7212-1HF40-0XB0
6ES7214-1AF40-0XB0
6ES7214-1HF40-0XB0
6ES7215-1AF40-0XB0
6ES7215-1HF40-0XB0
From v4.2.0 (Date 17.02.2014)
S7-1500
6ES7510-1DJ01-0AB0
6ES7510-1SJ01-0AB0
6ES7511-1AK01-0AB0
6ES7511-1CK00-0AB0
6ES7511-1FK01-0AB0
6ES7511-1TK01-0AB0
6ES7512-1CK00-0AB0
6ES7512-1DK01-0AB0
6ES7512-1SK01-0AB0
6ES7513-1AL01-0AB0
6ES7513-1FL01-0AB0
6ES7515-2AM01-0AB0
6ES7515-2FM01-0AB0
6ES7515-2TM01-0AB0
6ES7516-3AN01-0AB0
6ES7516-3FN01-0AB0
6ES7516-2PN00-0AB0
6ES7516-2GN00-0AB0
6ES7517-3AP00-0AB0
6ES7517-3FP00-0AB0
6ES7517-3TP00-0AB0
6ES7517-3UP00-0AB0
6ES7518-4AP00-0AB0
6ES7518-4FP00-0AB0
6ES7518-4AP00-3AB0
6ES7518-4FP00-3AB0
From v2.0.1 (Date 21.10.2016)
ET 200SP CPU
6ES7 510-1DJ01-0AB0
6ES7 512-1DK01-0AB0
6ES7 510-1SJ01-0AB0
6ES7 512-1SK01-0AB0
From v2.0.1 (Date 21.10.2016)

3. Windows DBGW-Service:        

1. C# developed program (based on Frameworks .NET 4.5) using Async & Await for handling multiple SQL-queries from multiple PLCs at the same time.

2. Resources on hosting computer, SQL server and network performance is the limit for maximum PLC connections.

3. Multiple instances of DBGW-Service can be installed on the same computer. Each with unique port number.

4. DBGW-Service program can be installed on several Windows computers for redundancy and/or for accessing more than one SQL server instance.

5. Each DBGW-Service can only communicate with one SQL Server instance. Each SQL server instance can host multiple SQL databases.

6. Supported user types for SQL authentication: Windows Workgroup user, Windows Domain user and SQL user

7. Simple ASCII config-file for entering: Diagnostic level, Local IP-Address or Network Connection Name, Local listening port number, SQL Connection string and SQL query timeout

8. Windows Eventlog automatically created (named DBGW Log) for diagnostic-logging, with 3 different levels

9. Supported operating systems:

Windows operating system
Windows 10
Windows 11 (Beta release 21H2)
Windows Server 2012
Windows Server 2012 R2
Windows Server 2016
Windows Server 2019
Windows Server 2022
Virtual Machine (Operating System = Windows) in Microsoft Azure Cloud
Virtual Machine (Operating System = Windows) in Amazon Web Services
Virtual Machine (Operating System = Windows) in Google Cloud Platform

10. Supported SQL Databases

SQL Database
SQL Server 7.0
SQL Server 2000
SQL Server 2005
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
SQL Server 2000 Express and newer
Cloud based SQL Database in Microsoft Azure Cloud, Amazon Web Services and Google Cloud Platform

4. How to exchange SQL data with PLC data:                                                                                                            

Use Microsoft SQL Management Studio application to develop and test your query first.

4.1 Example 1a (RA PLC):

Select query:

Select all columns and their data from Orders table

Figure 4.1: Select query from RA PLC

4.2 Example 1b (Siemens PLC):

Select query:

Select all columns and their data from Orders table

Figure 4.2: Select query from Siemens PLC

4.3 Example 2 (RA PLC):

Insert query:

Insert new record into Orders table

Figure 4.3: Insert query from RA PLC

4.4 Example 2b (Siemens PLC):

Insert query:

Insert new record into Orders table

Figure 4.4: Insert query from Siemens PLC

4.5 Example 3:

Update query:

Update value for Status column where ID=17 in Orders table

Figure 4.3: Update query fro RA PLC

Further examples are included in Studio 5000 project: DBGW_Demo.ACD & DBGW Library for TIA Portal

5. Implementation possibilities: 

5.1 Implementation on standard Windows 10 computer.

DBGW-service and SQL Server installed on the same computer. Most simple implementation.

Figure 5.1: Most simple implementation

5.2 Implementation on virtual server.

DBGW-Service installed on a virtual computer

SQL Server runs on a separate server. Data encryption between DBGW-Service and SQL Server is supported

Figure 5.2: SQL server on separat virtual machine

5.3 Implementation with Cloud based SQL-Server.

DBGW-Service installed on local computer. Data encryption between DBGW-Service and Cloud SQL-server can be activated

5.4 Implementation with 2 databases. Local SQL-Server and Cloud based SQL-Server

DBGW-service 1 and DBGW-service 2 are installed on the same virtual server.

DBGW-service 1 communicates with local SQL-Server.

DBGW-service 2 communicates with Cloud based SQL-Server.

Data encryption between DBGW-Service 2 and Azure Cloud SQL-Server can be activated.

5.5 Implementation with Virtual Machine in Microsoft Azure Cloud.

DBGW-service installed on a Microsoft Azure Cloud Virtual Machine.

Data encryption between Add-On-Instruction in the PLC and DBGW-service is not according to AES standard, and therefore not 100% secure.

For sensitive company data, this implementation is not recommended.

DBGW-service can communicate with cloud based SQL-servers in Microsoft Azure, Amazon Web Services or Google Cloud Platform

5.6 Implementation with Virtual Machine in Amazon Web Services.

DBGW-service installed on a Microsoft Azure Cloud Virtual Machine.

Data encryption between Add-On-Instruction in the PLC and DBGW-service is not according to AES standard, and therefore not 100% secure.

For sensitive company data, this implementation is not recommended.

DBGW-service can communicate with cloud based SQL-servers in Microsoft Azure, Amazon Web Services or Google Cloud Platform

5.7 Implementation with Virtual Machine in Amazon Web Services.

DBGW-service installed on a Microsoft Azure Cloud Virtual Machine.

Data encryption between Add-On-Instruction in the PLC and DBGW-service is not according to AES standard, and therefore not 100% secure.

For sensitive company data, this implementation is not recommended.

DBGW-service can communicate with cloud based SQL-servers in Microsoft Azure, Amazon Web Services or Google Cloud Platform

5.8 Implementation with Microsoft Azure Cloud Virtual Machine through Point to Site VPN.

DBGW-service installed on a Microsoft Azure Cloud Virtual Machine.

Communication between Add-On-Instruction in the PLC and DBGW-service is passing through a Point to Site VPN, and therefore secure.

Figure 5.6: Point to Site VPN to Virtual Machine on Microsoft Azure Cloud

5.7. Implementation with Microsoft Azure Cloud Virtual Machine through Site to Site VPN.

DBGW-service installed on a Microsoft Azure Cloud Virtual Machine.

Communication between Add-On-Instruction in the PLC and DBGW-service is passing through a Site to Site VPN, and therefore secure.

Figure 5.7: Site to Site VPN to Virtual Machine on Microsoft Azure Cloud