I have a local SQL Server 2014 Express instance running on a client (couple hundred actually). The client syncs data to a server running SQL Server 2012 Enterprise. The infrastructure is something along these lines:
Client ---> Master Server-->Server A && Server B
The client executes a stored procedure when it needs to sync. The stored proc then hits the master server and tells it that a sync is starting. The master server uses Broker Services to facilitate the sync.
When sync starts, the master server pulls all of the changed records from the client and stores them in the respective tables on the server. It then takes a subset of that data, transforms it and pushes it to Server A. It then pulls any new data on Server A, transforms it and inserts into the master server. This process is repeated with a different subset of data from the master server to Server B.
Finally, the master server pushes all of the new data down to the client, rekeying everything in the process. Since both the client and the master server use identity columns, we rekey to keep them in sync.
There are a few more steps in there, and some additional servers where we do more data mutation. The client basically fires the stored proc and then goes into a "syncing..." state. We want to give meaningful info back to the client so they know where they are during sync. Initially we were going to just insert update records from the master server to the client as Broker Services ran. The client would then query that table once or twice a second.
Then I discovered Query Notifications and Event Notifications I'm not sure which makes the most sense to use in this scenario. I've seen how to receive notifications using C# (what the client is written in), but have not seen how to do this with Event Notifications.
However, the client app executes a stored proc within the local database. That database then pings the master server. Can I still use one of these solutions (it sounds like Query Notification is the way to go) if the client uses SqlDependency pointing at the local DB? Can I Marshall notifications from the master server to the client through the local DB?
Am I way off base on the direction I'm wanting to try?
Event Notifications is intended primarily for DDL changes. You can get a notification when a table is modified, when new procedures are added, when server configuration is changed and on and so forth. I doubt is the kind of notification you want.
Query Notification is for cache invalidation. The client can use it to know when to refresh the display because the tables were updated.
While both Event Notification and Query Notification can deliver the notifications remotely, I don't see a need for your scenario. It seems to me that you may use Query Notifications to avoid the 1-2 seconds pull you have now. you are already on the right track, SqlDependency is the right way.
You can also check https://github.com/rusanu/linqtocache which wraps Linq (and Entity Framework) calls in a caching layer that is invalidated by Query Notifications.