Persistent DataCache with Pub / Sub OnChange

Question

(The next question is a little long, so if you are not interested in constantly cashing out and notifying, feel free to click the x button in your browser!)

We were instructed to implement a permanent data warehouse, which will contain a lot of numerical information.

The precedent is mainly used:

• You will receive feeds with updates and data inserts that should be distributed in the data warehouse.

  1.1. The refresh rate currently varies from 1 channel from 1 to 10 seconds. But later we can get more scalable matches.

  1,2. Volumes . Each feed will be approximately 100 thousand lines (even if the value does not change, it will still be in the feed)

• Values ​​must be retained. As soon as this happens, the C # server should be notified of parity. Perfect with the event. Since the channels will contain data that ideally does not change, the server event should be accompanied by a delta, and not just OnStuffChanged .

Implementation

Since the system uses the SQL server, some non-technical people have signed up to use the sql server for implementation. This look smelled bad to me! But in any case, some research has been done and found about Wrapper in the SqlDependency API.

I have seen, however, that appi comes with luggage in the form of a long list of restrictions ( http://msdn.microsoft.com/en-us/library/ms181122%28v=sql.105%29.aspx ). I also saw that MS claims that it is not so effective for secondary performance (which is currently not, but may be in the future).

In particular, for performance and reliability MS say

"Request notifications may not be the best choice for applications when notifications should be received with a second second response time, when the network infrastructure is not fast and reliable, or when the volume of notifications is very high."

and suggest alternatives to request notification , for example:

• After starting the update (I am not a big fan of triggers) in a controlled table, the action of which is to use the SQL Server service broker to send a message to the update notification.
• An implementation of custom middleware that stores and notifies.

(guys, I get to the place, thanks for being patient!)

So the thing is, I really feel that none of these ideas, other than custom middleware, are good.

Questions

• Has anyone had experience with SqlDependency and Service Broker ? Do you think I should start a crusade against mgmt to prevent a random mistake in the 1st place?
• I think that maybe I should use something like redis/memcached/mongo for the data cache. They offer perseverance. I am sure that I can figure out a way to combine them with a relational database and provide the changed / new numbers to the server for processing. Doesn't that make sense?
• Maybe I'm just redoing a thing. Should I try other suggestions instead (a similar stack thread question suggested by ``)?

Sorry for the long post! Thank you in advance for reading up to this point!

Answer 1

Do not use SqlDependency, this is not the right script for it. SqlDependency - control data that rarely changes (for example, reference data). With SqlDependency, you only get a notification that the data has changed, without knowing what the change contains, you have to ask yourself.

A service broker would itself be better suited to the bill as a means of separating the notice from consumption. large-scale deployments are already doing this. With SQL Server 2012, you can do multicast .

But absolutely not using a trigger. The notice should be a first-class citizen in your application, and not an afterthought tied to the data model in atrigger ( ughhh ). Have explicit procedures for notifying subscribers and invoking your application explicitly. Do not mix your data models and yoru exchange schemes, m you just enter an unnecessary connection.

IF you want to follow the NoSQL path, this is a completely different discussion. Radishes are a common choice . But watch out for genuine pub / sub messaging products like ZeroMQ .

By the way, such crusades (pro or contra, I don’t care ...) are best at fighting prototypes.