web sockets - what things to do i need to consider?

Hi

I has a list of assets (hardware) which report to a database whenever something changes

Currently i poll the database every 20 seconds to get updates
this worked fine on small volumes of hardware and connected browsers

However, now we are into thousands of hardware and users connecting to with browsers, the database is being hit very hard every 20 secs by each individual browser for data

I was thinking of moving to push instead of poll
I.e. if a certain table gets a new record, then push this message to all connected browsers that are currently looking at this unit

The browsers will be modern, but not just chrome or firefox,
also, users may login on their smart  phone/ tablet

is PUSH a possible option for me? and how would you suggest I get started
I looked into signal R a bit, but there are too many unknowns

heres some info on the platform:
Visual studio
Asp.net 4.6,
C#,
SQL server 2014 enterprise,
 Jquery,
windows servers

The data first gets pushed through a c# console app for processing before being inserted into DB, not sure if this can or should be part of process

500 Points go to someone who's been there and done that.
Please help me get started on the right track including some personal experience of you doing something similar.
Any info about supported browsers and light weightness etc will be much appreciated
websssCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Najam UddinCommented:
SignalR is the way to go, not only it abstract you from choosing between different option of push (long polling, web socket...) but it is pretty simple to use.
Please share more about unknowns.
I looked into signal R a bit, but there are too many unknowns


However SignalR resolve only half of the problem, that is pushing updates to client. Another half of getting data. If 20 sec interval is okay, and every one sees same data then you can leverage in-memory functionality of SQL Server 2014 or if there more different kind of data, a layer of caching (Redis) will be better.

Caching layer will be refreshed by worker process and your web application will get data from Redis/Caching.
websssCEOAuthor Commented:
Thanks, good to know about SQL in memory!
I have one table which contains the current last updated data, and all the history of the hardware
I only need to get the last update, I guess I could simple cache the last update in this in mem table, or maybe just cache the whole table in memory as other areas of the app use historic data
 
Regarding unknowns, I wasn't sure if all modern browsers work with it, and mobile devices
also, how to it all hangs together (big picture) i.e. how is the data pushed?
Does it work with sql server, or is there some app which sits in the middle which deals with the pushing?
Najam UddinCommented:
SignalR directly does not communicate with SQL Server, you are responsible for providing data. It is responsible for pushing things to client through web socket in newer browser and long polling for older browser. If talking of true sense of pushing " it lets you invoke java script function on client.

Yes it does work with mobile devices.
This article will tell you more about working.
websssCEOAuthor Commented:
thanks
I've been reading up a lot on this
How might i get the SQL push part to work?
Najam UddinCommented:
SQLDependency  is for such work, and will require query notification to be enabled query notification. However SQLdependency does not scale a lot. You can create a layer (web service based on DuplexHttpBinding) which uses sql dependency to tell your application/service that particular datachanged. then your application can inquire about changes from database and push them to client.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.