Database Gateway
(DBGW)
Software for data exchange between SQL-Databases and PLCs
In detail
Table of Contents
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 Datatype | PLC datatype |
|---|---|
| Varchar | String |
| NVarchar (PLC only supports None Unicode (1 byte characters), Unicode are not supported (2 bytes for special characters) | String |
| Char | String |
| NChar (PLC only supports None Unicode (1 byte characters), Unicode are not supported (2 bytes for special characters) | String |
| Datetime | String |
| Uniqueidentifier | String |
| TinyInt | DINT |
| SmallInt | DINT |
| Integer | DINT |
| 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 |
| Money | REAL |
| Bit | BOOL |
14. System variables. Makes queries easier, especially regarding handling date and time
| System variable | Description |
|---|---|
| @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 modules | Firmware 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 Type | Firmware 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
4.2 Example 1b (Siemens PLC):
Select query:
Select all columns and their data from Orders table
4.3 Example 2 (RA PLC):
Insert query:
Insert new record into Orders table
4.4 Example 2b (Siemens PLC):
Insert query:
Insert new record into Orders table
4.5 Example 3:
Update query:
Update value for Status column where ID=17 in Orders table
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.
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
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.
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.
