Solved

Send Steady UDP Stream From MSSQL

Posted on 2013-01-25
11
73 Views
Last Modified: 2015-10-01
HELLO...

We're looking either for some advice, direction, and/or a recommendation here.

We have an MSSQL database application, that runs our Point of Sale front end.  The publisher of the Video Surveillance system that we use, to properly "integrate" the POS data to their system, requires us to transmit the MSSQL data...via UDP...in an ASCII "CSV" stream...to their servers.  Note...this is a ONE-WAY stream...MSSQL must output *TO* the Vidoeo Surveillance system, and *NEVER* vice-versa.  Because this is near real-time transactionally based, there will be a lot of traffic as a result...and we both expect that, and have the network infrastructure to handle it.

Having never done this before...we were hoping that someone that has done this...can shed some light here.  Even if there's some 3rd Party package that can handle the data conduit...certainly that would be fine.

So...any suggestions y'all?  Anyone ever done this?  Looking for something tried & true.  Please let us know...and THANK YOU very much in advance!...Mark
0
Comment
Question by:datatechcorp
  • 6
  • 5
11 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38823428
Hi,

Soooo, what will you do with this stream. What is the point? How will you use this to recover?

SQL - especially SQL 2012 - has a bunch of different dr/recovery options - backups, replication, mirroring, log shipping, clustering. If I'm right, I suggest you look at that kind of option, rather than rolling your own.

Why not just do a network backup - use the inbuilt compression and do a copy_only backup to that network share?

Regards
  David

PS Please confirm version and edition of SQL, size of database, expected transaction volume ...
0
 

Author Comment

by:datatechcorp
ID: 38823465
Hi David...

Thank you for replying.  The MSSQL version we're on...is 2008 R2.  I think I understand your suggestion, but it cannot apply in this situation.

Because this is a Video Surveillance system...what has to occur...is that as data is appended to a table on our MSSQL Server, a (corollary) "stream" of that *SAME* data...needs to be ported out...to a Video Surveillance "Server"...and the author's system (it's Agillence)...will *ONLY* accept a UDP formatted stream.  Uugh, I know... :-(

Is there a way to accomplish this with, say, a trigger on the MSSQL table...whereby the "output" is then "redirected" to, say, UDP?  Any thoughts here?

Please let us know...Thanks!...Mark
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38823474
Hi,

Short answer - no, not easily.

That was why I was suggesting all the standard SQL mechanisms.

A T-SQL trigger is only going to do an insert/update/delete. While triggers are great, they are a catch 22 - of course you could invoke some complex system stored procedure or write your own CLR procedure/function, but that is too big to be called by a trigger.

Okay, thinking a bit outside the box - get the insert trigger to put the rowid of that record/those records (never assume that what fired a trigger is only one row!) in a queue (use a table for this)
Schedule a task - can get it as fine-grained as every minute - to use the CLR procedure you will write to do this to extract data and write data to the stream. As it does so, it removes the ids from the queue (table).

Now, writing CLR stuff ain't for the novice developer, but its a lot easier than developing an extended procedure which is how you'll have had to do this with SQL 2000.

HTH
  David

PS What is the data that is being inserted? Is it surveillance ie time-lapse photo?
0
 

Author Comment

by:datatechcorp
ID: 38825118
It's all the POS system's transactional "pieces"...i.e., Clerk starts a transaction...clerk rings up an item...clerk rings up a second item...cleark does a price override...clerk adds a third item...clerk voids a line...clerk receives tender payment for sale...clerk gives change due...and so forth.

With Surveillance Systems that "integrate" (actually, "tie-in" would be more appropriate terminology)...all those facets of a transaction...are coordinated...with the video feed.  Thus, when a security audit is necessary, the data, with the video, coexist.

I hope this makes sense...Thanks!...Mark
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38825145
Hi Mark,

Makes sense.

Did my comments about writing a CLR procedure help any?

How have other clients of your supplier managed this?

Regards
  David
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:datatechcorp
ID: 38825165
Yeah...it kinda' makes sense David.  Don't have any others that have used Agilence...but, apparently, Agilence is picking up momentum these days...and they WILL NOT write an interface (uugh!).  They tell the end-user, flat-out, "here" is our "UDP" spec...here are the maps to the field data we expect...no go ahead and itegrate it...y'know?

We've *never* had to go down this road in the past...like, ever.  Have you written such a CLR (and/or Extended Stored Procedure)?
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 38825197
Hi Mark,

While I have some C++ in my background, I'm not fluent at present.

However, I took a small console app, and converted it to a CLR function. Its not that hard. Specifically to stream the data, that might need a bit more work ...

All the best
  David
0
 

Author Comment

by:datatechcorp
ID: 39043273
Hi David...

Sorry...it's been a little while...but we had some family emergencies, and I've been quite "bi-coastal" in the past (2) months.  I'd like to pick this conversation up again, when you have time.

Thank you for your patience!...Mark
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39045105
Hi Mark,

Sorry to hear about your family. Hope its sorted now.

Regards
  David
0
 

Author Comment

by:datatechcorp
ID: 39275893
Hi All...

Getting back into swing again...please feel free to chime in on responses...Thanks!...Mark
0
 

Author Closing Comment

by:datatechcorp
ID: 41020035
Hi David...

I just realized...that with all the "family drama" I had at that time (my Dad passing)...that I'd abandoned this question/issue.  Soooooo sorry for that!  I'm granting you all the points.  Thanks & take care!...Mark
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Subtracting Amounts from "Among Rows" 3 43
What is format f12.8 for a CSV file 6 41
TSQL previous 5 24
how to install/upgrade the Blitz responder kit 8 24
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now