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
Solved

Send Steady UDP Stream From MSSQL

Posted on 2013-01-25
11
78 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 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