access MS SQL Database from cRIO

[Brandyn]

You have some options.  You can go down the Actor framework route or you can develop a "typical" architecture that you would develop for the CLA exam.  So without having full details on the application, the first thing you need to do is break things down into individual processes or "modules".  From what I have gleaned from this short conversation you would need the following Modules:

1. TCP Module: (This module would be responsible for reading TCP messages from the CRIO(s) and then passing it on to whoever needs it.  

2. SQL Module (This module would be resopnosible for writing data to the SQL Server)

Then you would need to define the messaging mechanism.  The best thing in my opinion to use are dynamic events.  Then the structure of the messages or data that is passed would be a Cluster containing a string/enum and variant. (String/Enum to denote the message name or state to go to and the variant will be the data. 

[amatz11]

ok i think i know what should i do but if u have simple program it will more helpful for me to understand...

anyway thank you for your advice...

[Brandyn]

Its very difficult to explain over a forum.

[amatz11]

maybe u can make simple block diagram for me?

[Brandyn]

The challenge is its multiple Block diagrams.  I think a good starting point would be looking at:

http://blog.jki.net/events/niweek-2011-beyond-state-machines-slides-and-code-now-available/

[tshelleyamrc]

I have just read this thread with great interest. I am also trying to implement a similar type of system with multiple cRIOs to a MS SQL database. 

How did you progress with finding a solution to your proplem? and are you able to share you findings?

[mikeporter]

There are an awful lot of variables in play here:
How many crios? How much data is each one generating? How often is data being transfered? What does the data look like? What is the table structure you're inserting into? What version of SQL Server? Who else is on the network?

Having an in between computer, as was advised, it an excellent approach, but there are details to be sorted out.

Mike..

[tshelleyamrc]

Hi Mike, 

I would just like to give some context to the problem. I work in the High value manufacturing & machining, my team and i (with support from NI) have a research project which looking into Machine Health monitoring anything from power, spindle power, ambient temperature, vibration and acqustics. The aim is to produce a "black box" (as decsribed my mechanical engineers, potentially a cRIO or SoM), which selects 2/3 data types (temp, vibration, power) and creates a tool capable of capturing this data, what ever it maybe and pushing it into a custom database with relevant meta information, which can then be used for post-prosing via something like diadem/matlab etc.. I would also note sometimes it is not feasible to have a windows computer between the cRIO and database because these devices could be located inside machines.. 

I fully understand there are several your comments and have already asked several myself, however the aim is to produce a flexible arcitechure which can cope with 90% of the requirements, and we will accept that sometimes the system will not be capbable of the 10%. Intially there will be 2 cRIO of the network which will talk to a mySQL database located on the companies virtual machine. We need to look into the local security and create a password to stop direct access to the cRIOs.

With respect to data rate i can envisgae anything from 1s per minute to 10000 per/s. However for this project we can determine this for this project, based on the sensors we select. The mySQL tables will be design to recieve the relevant data from the cRIOs and most importantly contain the meta data about when, where and how.. which makes the information very useful in post processing, we're currently thinking about what the tables will look like.

If you have any further comments please let me know, we are at the early stages on concept and i would appreacte and comments and questions on our approach and stratergy.   

[mikeporter]

What I think I would do is look at creating a separate box (you can call it something like a data concentrator). This box would be a small rugged industrial PC running LabVIEW. It could connect to 4 to 8 crio systems via Ethernet and the server on the other side. Communication with the crio would be via something reliable like TCP/IP or VI Server. Do not let NI sucker you into using shared variables -- you need reliability.

The connection to the server could be wired or wireless, your choice.

The box's job is to gather data from the attached crio systems and transfer it to the database server. This data concentrator box could even become a separate product in its own right.

Mike...

[smithd]

I don't know for sure what sort of rates you'd be able to get out of a mySQL database on a cRIO (or even across the network, as the data transfer may also be a bottleneck),  but I'd highly recommend proving that out first. 

What I've seen elsewhere is people logging to a file system designed for streaming (like a tdms file or binary file) and then pulling that data on occasion across the network where it is processed. A good starting point is this: http://www.ni.com/example/31388/en/

It doesn't implement what I described (it only logs the data, it doesn't handle transferring it) but its pretty close and we've used it a ton for logging applications.

Depending on how many of these you're making and what this is being plugged in to, you might take a look at this: http://www.ni.com/white-paper/52454/en/

I don't honestly know too much about it, but if you're doing health monitoring its worth looking in to.