?
Solved

Send Steady UDP Stream From MSSQL

Posted on 2013-01-25
11
Medium Priority
?
97 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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
 

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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

764 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