Given the following structure. There is a partitioned (partitioned) database (MSSQL), a services layer (C # web-api or WCF), a mobile client (IOS). The goal is to organize the notification of mobile clients that one of them has made changes to the database. As far as I know, for this, you can use MS Service Broker, when a table changes, an event will be raised. In services, it can be intercepted via a SqlDependency object, subscribed to a specific request (correct it if I'm wrong). And so a few questions:

  1. Is it possible to configure Service Broker for notifications about changes to a specific partition (in the MSSQL realities, when database is partitioned, a separate file and file group is created for each partition).

  2. How to organize customer notification. Before that, I implemented applications in real time using SignalR, but the client was on JS and there was an opportunity to subscribe to a specific hub. What to do in cases of iOS apps? Mobile developers insist on the long polling option. In my case, I assume that it will be necessary to capture a GET request for a timer and wait through the while loop until the SqlDependency event is raised (is this the correct approach?). But long polling captures streams and does not let go until the answer is received (is it a correct assumption). Thus, when the number of clients exceeds 1000, 1000 streams will be captured, for a rather serious period. Is the WebSocket option good for this case?

  3. Is it possible to use the AMQP implementation (RabbitMQ or ZeroMQ) for such a case? Instead of integration through a database. In the following structure. The device sends a POST request and updates the data in the specified partition, then if the update is successful, puts the message with the necessary header into the queue, and all subscribers receive a notification, then sending a GET request for receiving data.

Thanks in advance for your attention and help.

    1 answer 1

    Notifications via Service Broker are a rather fragile and cumbersome and unstable solution. I had to maintain a system where notifications were organized by calling the .net code in the SB - this is real hell.

    SqlDependency is designed primarily for invalidating the cache, and not for receiving full Callbacks.

    The simplest and most stable solution (in my experience) is the following:

    • When launching the application, mobile clients send the Apple Push Notifications Service token to the server.
    • The code that processes the changes saves data to the database, and then sends a notification to the queue (Service Bus, RabbitMQ or something else with the AMQP protocol)
    • A background handler (Windows Service, Azure Web Job, or even Azure Function with a trigger on AMQP) reads messages from the queue, and sends notifications to clients via APNS.
    • Mobile clients process push notifications and either take the necessary data directly from payload, or go after them to the server after receiving the notification.

    This scheme is easily scaled

    • it does not require a permanent connection to your server by mobile phones.
    • for each mobile phone you just need to save the token. You do not need to keep the flow or handle the connection breaks.
    • processing of registration, sending notifications, and processing of changes are weakly connected with each other and can be separated by different applications (with independent deployment).
    • Thank you very much. In general, and proceeded to implement. While there is suffering with the installation on Win10 RabbitMQ (on Win8, everything fell without problems). In the near future I will try to install immediately on WinServer 2012R. - TimRiazanov
    • @TimRiazanovt the easiest way to raise in a container through the docker: hub.docker.com/_/rabbitmq - PashaPash ♦