Boost SQL server IQ

Writing historical production data with a PLC has long been a painful but necessary process. Some system designers have to use a PC to continuously poll their PLC for the command that tells it to grab and store historical data. The PC retrieves the data from the PLC, more often than not over a proprietary network link. It’s then written to the SQL server via a communication link, typically Ethernet.

The conventional PC tends to be the weak link. If it fails, the operator must decide whether keeping the data or keeping the system going is more important. Even when things run smoothly, this constant polling can inundate the network.

Having a PC in the system adds hardware. Anytime hardware is added, reliability decreases. Also, if communication to the PLC is networked via a proprietary fieldbus, then additional—quite often expensive—cards are required.

However, there is light at the end of this PLC tunnel. When Microsoft released .NET technology, writing software for embedded operating systems such as Windows CE became as easy as writing a desktop application. With this advance and the release of Visual Studio 2005, Microsoft created a tighter integration of SQL servers, and made writing a database application simpler. It no longer requires a specialized developer, and system designers no longer are tied to the limitations of proprietary operating systems.

I first used this advancement at a brake-pad production facility to expand the company’s mix-room system. The original system consisted of a line of feeders for raw materials and one mixer using the traditional PLC-to-PC SQL server design.

My customer wanted to replicate the verifying scale and mixer units to be able to send materials in two directions to improve throughput and efficiency. The customer also wanted the new section to be independent of the existing line’s controls, so it could operate when one side was shut down.

The second mixer created another large source of data to be transferred to the SQL server. To complicate things even further, the customer’s IT department began limiting the number of PCs added to its network, making the conventional method even more difficult.

I decided to use a DIN-rail-mounted embedded controller from Beckhoff Automation, partly because the controller image has the appropriate software included, such as the .NET 2.0 compact framework.

The newly integrated line is operated by two controllers with Windows CE, motion control software, and a Visual Basic program running as an operator interface. One controller is used for conveyor control, while the other controls the mixer. After weighing each batch, the controller transfers the material weights and batch numbers to a SQL server. At the end of the mixing cycle, the mixer data also is stored in the SQL server. When a mix cycle runs, the energy used by each motor and the mix temperature is recorded at intervals, and stored in the database.

Once the application starts, it establishes controller-to-controller communication via Beckhoff’s Automation Device Specification (ADS), which is fieldbus and network-independent, and will operate over Ethernet or almost any other fieldbus network. Process data is retrieved and written directly to the SQL server—no polling is required. Network traffic on this system only occurs during data transfer. 

 .NET driver supplied for the compact framework allows a program that runs on Windows CE to exchange data with the software. Also, Microsoft’s .NET environment includes a SQL server client for Windows CE, making it very easy to write a small VB program that can read and write to a SQL server.

The software includes a function block that can launch a VB .NET application from the PLC code at the end of the mix cycle. This application resides on the controller Compact Flash, and handles the data transfer from the control software to the SQL server.

Beyond the streamlined, easier-to-integrate architecture, there are considerable cost savings for the customer. The most significant savings come from eliminating the type of PC used in the conventional PLC approach. Installing a typical enclosed PC, with labor, can cost $7,000 to $10,000, not including time spent on PC maintenance for tasks such as security updates. Eliminating the need to poll a PLC also greatly reduces network traffic. There’s an additional savings of $2,000 when not using the PLC platform’s special network card. Using embedded PCs can save well over $10,000 per SQL server application in hardware costs alone.

Taking the PLC and traditional PC out of the system reduces hardware and cost, and the solution provides increased security as well.