Understanding SQL Databases & Transaction Managers

By Deane Horn, director of marketing, Softing Inc.

Imagine you’re manufacturing an automotive part, such as a wheel.

Let’s say the wheel is type “premium mag wheel.” Thousands of parameters describe how that wheel will be made, such as size, color, where to drill holes, size of holes, wheel spokes and more. This is called the recipe.

The recipe is different if the customer orders “black premium mag wheel” vs. “gray premium mag wheel.” Since the data is a collection of parameters and is dependent on the production line or customer order, we say the data has context. Data that can be changed in real time in context with production needs is much more complex than typical programmable logic controller (PLC) static data used to carry out repetitive tasks.

A recipe is a great example of complex data; it’s a collection of parameters. And, if you manufacture 10 different wheels, you need 10 different recipes. What does a recipe look like to a PLC? The answer: a table (see Figure 1). A table has rows and columns. For this wheel example, you’ll have one recipe per row, and each column will have the parameters of the recipe — paint type, paint level, etc.

PLCs have challenges with tables. Why? The memory and performance of a PLC is optimized and dedicated to production, moving machines, moving product, making product — simple data. Complex data requires more memory. In PLCs, memory is expensive.

What platform would be good with handling complex data and tables and relieving the burden and risk from the PLC? An SQL database.

Function of an SQL Database

What’s an SQL database? You can visualize it as a table, and most likely, you have one! Your IT Manager probably is already using an enterprise SQL database across your company to share information throughout your organization. Why? Because businesses need to share information across the company, and an SQL database is designed for this.

If an order comes in, the order is placed in an SQL database. If a sales manager wants a report on sales, she pulls this from the SQL database. If production needs to build the product that was just ordered, guess where the production department gets the order details? The SQL database. After the part is built, the performance, quality, and metrics can be stored in the SQL database. Reports can be generated, and processes improved, by pulling these metrics out of the SQL database and analyzing relations across data sets.

SQL databases are optimized for storing data with tight control so only approved users get access to certain data. The SQL database runs on powerful PC servers, with tons of memory, tons of storage, with redundancy, with failovers, in multiple locations to help ensure the whole company is always online.

So why are you putting recipes in your PLC? Why are you storing production key performance indicators (KPIs) and quality parameters in your PLC? You could be using these towers of servers and databases you already have in your plant, and you could relieve a huge burden from your PLC.

You are probably doing this because you are in control of the entire process, and you’re good at getting things done yourself. At some point, however, the PLC will reach its limit, and the process will get a little risky. Now it’s time to step back and architect this solution in a different way.

How can you ease this burden from your PLC and tap into this massive resource, your enterprise SQL databases, or other databases that you already have in your facility? It’s pretty easy, and we’re going to cover these three aspects: prep the PLC, prep the SQL database and implement a transaction manager between the PLC and SQL database.

Prep the PLC

You have to do some prep work in your PLC. You’ll no longer need hard-coded recipes, 1,000 constants x 10 different wheel types, in your PLC. Instead, change these constants to tags, or UDTs, in your PLC. Why? You’ll have one logic code base, now using variables, and you’ll be able to update the variables each time you have a different part to manufacture — each time there’s a new recipe to download from the SQL database to the PLC.

How will you update these variables in the PLC for any of the 10 wheels you want to manufacture? That’s where the SQL database enters the picture. You are going to store the 1,000 constants times 10 wheels in an SQL database table. The data in the SQL database will only be downloaded to the PLC when needed; your facility servers and transaction manager will carry the burden of storing and responding to requests for information.

How do you prep your SQL enterprise database? You, the control engineer, need to be prepared to guide your IT Manager who owns the SQL database. Next, we’ll cover what your IT Manager needs to know.

Creating an SQL Relational Database

Your recipe will be stored in what’s called a relational database. If you’ve never worked with an SQL relational database, that’s OK. Your IT Manager is an expert in SQL. If you’re a control engineer or systems integrator, here is your crash course on communicating to the IT Manager your project requirements.

For your IT Manager to create the table in the SQL database, you’ll need to provide your IT Manager with three pieces of information: 1) headers 2) recipe names 3) recipe data (see Figure 2).

The headers are at the top of each column. Headers are just a short description, or a column title, for your data. If you use Microsoft Excel as your tool to build a template, you can start by defining headers in the spreadsheet and placing those at the top of each column.

In an SQL database, each row is called a record. Recipe names are placed in the first cell of each row, as shown in Figure 2. If you have 10 recipes, you will have 10 rows, or records.

Why is it called a record? In a relational database, there are 4 main tasks that we do: insert records, select records, update records, and delete records.
 
You’ll use the “Select” command for our example since we are getting recipes from the database and downloading recipes to the PLC. In your recipe spreadsheet, put the recipe name in the first cell of each row.

And finally, you need to fill out your recipe spreadsheet with data. This means for each recipe, fill in the data values, which will be constants, for each column. For example, for header “color” and recipe “premium mag wheel” the data value will be “black,” as shown in Figure 2. Now your spreadsheet has recipe names, column descriptions and data. This is all the IT Manager needs to build the SQL table.

Now, we’ve just walked you through building the SQL database template for the hardest example – recipe downloads.

Another extremely useful application for the SQL database is the “insert” command. This also happens to be the easiest command to use since absolutely no PLC logic is required to change. In this case, we’re inserting values into the database from the PLC. “Inserts” are used when we are monitoring and storing PLC tags in the SQL database. For example, when we monitor KPIs or product quality, we insert these tags into the SQL database.

After the PLC tags are created and SQL table is created, how does data move between the PLC and the SQL database? How does a recipe get downloaded from an SQL database to a PLC? To get data moving between the PLC and the SQL database, you need a transaction manager.

What Is a Transaction Manager?

What does a transaction manager do? Let’s say your goal is to download a recipe. We’ll use the “Select” command because we are getting a recipe from the SQL database. Now that your tags are setup in your PLC, and tables are setup in your SQL database, the transaction manager will log into the PLC, log into the SQL database, and browse both tags (destination) and tables (source).

The transaction manager contains the connections between PLC tags “whl colr” and table records (wheel color) known as a map. What initiates data movement? The transaction manager handles triggers, which initiates data movement.

Perhaps you want a barcode scan to be the trigger for a transaction. If the barcode scan read equals “prem mag wheel”, the transaction manager recognizes this trigger and uses the barcode scan value as the trigger and lookup value to  select “prem mag wheel” from the SQL table (1st recipe in our example), which will return all of the data in that row, for that recipe, and load it into the PLC.

The transaction manager controls the world between the PLC and the SQL database, and it’s unique because it completely understands both PLCs and databases so that we don’t have to. The transaction manager contains the data maps, data sources, data destinations, triggers to initiate a transaction, and SQL commands (such as “selects” or “inserts”).

The transaction manager also handles failover scenarios such as redundant paths, failover servers, or store and forward when the network goes down. It provides email alerts upon successful transactions or status tags when it’s critical to confirm that transactions completed.

Transaction managers come in many forms:

• Software that runs on a PC with protocol conversion.
• Industrial gateway DIN rail devices.
• In-chassis PLC modules.
• Software code you write yourself on a PC.

Final considerations might include failover scenarios. Consider what you want to do if the network goes down, if an SQL database goes offline, or if the PLC goes down. Also consider how you want to receive status of transactions. The transaction manager can handle all of these scenarios to help ensure easy setup, maximum production uptime, simplified tuning, and predictable deployment, support and scalability.

Another burden that is lifted from you in this operations technology (OT) to IT convergence is that your facility servers now will take care of nightly backups and safeguard your data. When you need a new recipe or need to monitor a new production parameter, in some cases, you can make upgrades on the SQL database side and not have to touch the logic in the PLC. That reduces a lot of risk.

Use This Practical Information

Before you get started, what are the project considerations in addition to connectivity you should be thinking about? Speed of transactions, network outage scenarios, database outage scenarios, security, are there other PLC’s you need to connect as data sources, transaction notifications (successful or failures). These are easy tasks that a good transaction manager will handle for you. You just need to think these through and discuss them with your project team.

Use this article, make notes, create a team, identify work processes, data, sources, transaction manager, and create an action plan. With a team approach, you’ll save a lot of time, have fewer roadblocks, and end up with a more successful project.

Softing Inc., based in Knoxville, Tennessee, is an Encompass™ Product Partner in the Rockwell Automation PartnerNetwork™ program. Softing develops products for connectivity and products to monitor and troubleshoot network communication health, including its tManager solution that performs bidirectional data transactions between SQL databases and ControlLogix® PLCs or CompactLogix™ PLCs.

^{The Journal From Rockwell Automation and Our PartnerNetwork™ is published by Putman Media, Inc.}