Solved

Send Steady UDP Stream From MSSQL

Posted on 2013-01-25
11
70 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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

17 Experts available now in Live!

Get 1:1 Help Now