2

I'm developing a Windows Form application which runs on a cloud SQL service hosted by the Microsoft Azure development services. Without having the ability to physically have a receiving application which would allow me to utilize sockets which is an essential pitfall as my application would rely heavily on a dedicated database, so i'd prefer to have the ability to constantly check the database for changes and alert the current user based on row entries which match criteria.

I've read on a few blogs/posts that polling (Was a possible solution) is a bad thing to utalize. So, what would be an approach to consider?

Daryl Gill
  • 5,464
  • 9
  • 36
  • 69
  • Polling is definitely a bad idea. What exactly is wrong with sockets? WCF is a good option (but it also uses sockets...) – BradleyDotNET Aug 22 '14 at 23:24
  • @BradleyDotNET Azure has a MSSQL hosted service, so installing/configuring sockets looks to be out of the question – Daryl Gill Aug 22 '14 at 23:25
  • I see now, you want live feed from a database, which in the best case is *still* polling at some service level. Tough call, really. If you could host a standard WCF service on the server that kept an in-memory copy of the database (and just used the DB for recovery) it would be a lot easier. – BradleyDotNET Aug 22 '14 at 23:38
  • @BradleyDotNET Indeed, Azure does offer the ability to have hosted virtual machines. The time and exceeded cost of configuring a Windows Server machine with a Socket receiver and MSSQL Database is a little stretched. Would setting up this be a better alternative? Or would you say it's some-what possible using the cloud SQL Service? – Daryl Gill Aug 22 '14 at 23:51
  • Its hard to say. I would use the service just because having 10K clients constantly querying a database sounds like a *really* bad idea. See my answer for more information. – BradleyDotNET Aug 23 '14 at 00:08
  • http://msdn.microsoft.com/en-us/library/ms175110(v=sql.105).aspx – I4V Aug 30 '14 at 17:34

1 Answers1

2

To clarify the problem, you want clients to receive updates when certain data changes in the database.

SQL Server 2005 and 2008 do support the concept of notifying clients when data changes (MSDN). However, this SO question would seem to indicate that this feature is not present in SQL azure at this time. This means that you have a few options:

  1. Poll the database itself

    • This option lets you keep costs down, as you already have everything you need. The disadvantage of course is that you are doing (potentially large) queries at a relatively frequent interval. With enough clients this will drastically slow down due to the load involved. It could also prove costly if you have a price/transfer scheme.

  2. Wrap a service around the database and talk to that

    • This could be used with polling, but is even better when using a push technology (like sockets or WebSockets). This of course has additional upfront costs, but allows you to control what updates are received and when. Ideally, this service would keep a copy of the database in-memory that it used to service client requests, while updating the actual DB on its own. This keeps queries to the database (which are slow) at a minimum.

Only you know the right answer for this, but I would go with the second. It will be more performant, scale better, and prove more extensible when you need functionality that goes beyond simple queries.

Community
  • 1
  • 1
BradleyDotNET
  • 60,462
  • 10
  • 96
  • 117
  • `From my experience, SQL server (or any other RDBMS) does not support notification of a change to connected client` for ex, http://docs.oracle.com/cd/E11882_01/java.112/e16548/dbchgnf.htm#JJDBC28820 or http://msdn.microsoft.com/en-us/library/ms175110(v=sql.105).aspx – I4V Aug 30 '14 at 17:30
  • @I4V It would be interesting to know if that worked on Azure SQL servers. That being said, I had no idea that existed, so thank you for educating me! If you aren't planning on putting that information into an answer, I would be happy to include it in mine. Please let me know what you plan to do. – BradleyDotNET Aug 30 '14 at 17:56
  • @I4V Thanks! I have updated my answer with it. I did find out that SQL Azure does not appear to support this feature :( But thanks again for the info anyways! – BradleyDotNET Aug 31 '14 at 21:55