MS SQL Server

Starting in version 1.49 (October 2024), tabular data generated by Gas Flow v1.7 and Liquid Flow v1.2 can be exported directly to a Microsoft SQL Server database.

 

Note that MS SQL Server is a complicated piece of software and installation, configuration, management and security is beyond the scope of this document. We explain how to use PLC Shift with SQL Server, but we don't explain how to use SQL Server itself.

 

Before the runtime can insert data into the database, the database must be prepared. We have provided T-SQL scripts to create the required tables in the SQL Server database. To view these scripts, select View->Show SQL Folder from the top menu in PLC Shift Manager. This will open Windows Explorer to the base directory for SQL scripts. From there, open the MS SQL Server and then the folder for the app that you want to be able to send data far.

 

 

 

The .sql script files can be opened directly by SQL Server Management Studio (SMSS) if you are using that software. Execute the script against the database to create the required tables. Each application type has its own specific schema, so if you want to export data from Gas Flow and Liquid Flow apps, then both the GasFlow.sql and LiquidFlow.sql scripts must be executed.

 

 

In PLC Shift Manager, set the 'Enable Table Export to MS SQL Server' configuration option the 'Export Settings' group at the device level to 'True'. Configure the 'MS SQL Server Connection String' in the same group as required.

 

 

MS SQL Server connection strings contain all the information that the app runtime needs to connect to the database server. There are a large number of options that a connection string can contain. At a minimum, a connection string should contain:

 

 

For our testing we have configured a managed SQL database in MS Azure. We have configured this database to be available over the public internet, and because it's hosted in the cloud, it has a certificate that is signed by a well known certificate authority. This allows the connection to the database to be encrypted and for the certificate to be validated with no additional effort.

 

For a database that is hosted on premises, a self signed certificate may be used. In PLC Shift version 1.49, we don't have configuration options to allow for self signed certificates to be used, although self signed certificates can be installed manually on the computer where the PLC Shift runtime is installed. 

 

We plan to add additional encryption, certificate and authentication options in the PLC Shift runtime the future, but MS SQL server supports many different types of identity schemes, and we are waiting to see which ones are the most popular and useful before adding support. Please reach out to us by email at support@reverity.io if you need specific authentication and encryption methods.

 

At the application level, in the 'Export Settings' group, choose the tables that you want to export. Selecting the 'Export Flow Config History' exports both the complete configuration and configuration change tables.

 

In the PLC Shift runtime, for each app that has table export enabled, and for each table that is exported, we keep track of the newest record that was exported. This means that any individual record will never be exported twice.

 

In an effort to not overload the processor or the network, table export is a slow process. Records are only exported a few times per hour, and no records are exported in the first 30 minutes after the runtime is started.