I need to create highly scalable solution - where field devices in thousands of sites are delivering data in real time to a back end system, and SQL Azure seems to fit the bill nicely in terms of adding sql databases and application servers.

Each field device is effectively sending 400 sensor values every second - for about two hours a day, and those 400 sensor values every 5 minutes for all other times forever. Additionally, when an error occurs on this field device, it will send up the last minute's data for all 400 sensors as well (400 * 60 readings) - causing a mass flood of data when anything goes wrong.

I really want to design the system so that the independent field devices and the data in which they store can not affect other devices. Allowing each field device to not affect the performance of other field devices.

I started the design with thinking a single database holding all the device's data - but have started to get deadlocks occurring when simulating multiple site devices. Hence, am in the process of moving to a multiple database solution. Where a master database holds a lookup table for all the devices - returning a connection string to the real database

At this stage of the project, it is most important that I am able to pass that data back to User Interfaces running in web browsers in real time - updating their screens every second.

In future stages of the project it will be necessary to start aggregating data across multiple devices showing statistics such as sum of sensor X in region Y. I can see this will be hard to do with the multiple database approach.

So would value any advice e.g.

Do you think it is sensible to use Sql Azure to host potentially 1000's of databases and to use this master database to indirectly point to the real ones?

Will I have a problem with Connections to the databases from the applications- with issues with connection pooling for example?

How will I be able to aggregate data from all these different databases in Sql Azure.

Would be interested in all your comments. Regards, Chris.

  • Can you describe the activity that is specifically causing a deadlock? A deadlock is primarily an application problem, not a database problem--the application is asking for something in a way that the database cannot satisfy. – Nick Chammas May 4 '12 at 18:37
  • I don't think we can give a reasonable answer without concrete numbers. "thousands of sites" -- 2,000? 25,000? What precision is required for the readings? 4 bytes? 16? Regardless, I think storage is going to be the biggest hurdle in this project -- if my numbers are correct, at a guess of 5,000 sites and 8 bytes/value with no overhead, you'll fill a 150 GB database in about 32 hours. At this kind of scale, even seemingly tiny decisions can have have a huge effect overall. Is paying Microsoft $12k/month after ~2 years +ROI vs buying your hardware? Will they even scale higher than that for you? – Jon Seigel May 6 '12 at 17:22
  • Current projection is about 200 sites per year - keeping the data for at least one year. I am getting a representation of a signed 32 bit integer from the site of which the maximum number of decimal places will be 3. – ChrisI May 8 '12 at 15:16
  • That would imply a generic decimal(10,3) would suffice. – ChrisI May 8 '12 at 15:22
  • 1
    The default isolation level in Azure is READ COMMITTED SNAPSHOT, so you're probably okay out of the box in terms of locking. With regards to my previous comment, it appears I way overestimated. That said, you'll still want to sit down, design a schema that can hold your data, then figure out the storage requirements based on expected need now, plus a reasonable growth rate over time. Scaling out to multiple databases should be a last resort. Perhaps you want to think of an archiving strategy? Maybe all you need are summaries of the data online, and you can archive the raw data elsewhere. – Jon Seigel May 11 '12 at 0:58

Since no one else has answered, I'll share some opinions and do some hand-waving.

As long as you aren't locking common resources, or are locking resources in the same order, you shouldn't have problems with deadlocks.

I'd look at separate tables before separate databases. Each additional database will definitely cost you more but additional tables won't necessarily cost you more. You might need to use more than 1 database becuase of the sheer volume of data you will store or because of the rate at which you need to store your burst traffic. If you can manage it, I think that a table-level granularlity will be more flexible and possibly a good deal cheaper than starting with a database-level granularity.

The problem with putting each device's data into it's own tables is that it makes the reporting hard since all of the table names will be different.

I presume that you have some way of detecting when you get a "failure resend" of data. You don't want to put the same value in a table twice and I i'm sure that the devices can fail (local power failure?) in ways that have nothing to do with whether or not earlier values where properly stored.

WAG: Assuming each "value" is 4 bytes, I calculated about 11.5 MB of collected data per device, per day. (This ignores all kinds of stuff, like device identifiers and timestamps, but I think it is OK as a rough estimate.) So, with "thousands" of sites, we are looking at tens of GB, per day. You don't mention any kind of lifetime on that data. The largest Azure database currently maxes out at 150 GB. You could fill those up pretty quickly.

Getting anything to happen in a web browser in a short period of time is iffy. When you are reading from (possibly multiple) databases with GBs of data, continuously inserting lots of new data into the tables you are reading from and interacting with web servers across the open internet, "real time" is wishful thinking. IMO. "Fast enough" is the usual goal.

If you can't keep all of the data you need in a single report in one SQL Azure database, it's a problem. There are no linked servers or distributed views (at this point). There is no simple way to aggregate accross many Azure databases. You'd have to pull all of the data to a central location and report from there. I'd guess that the aggregated data would be too large to store in a single SQL Azure database, so you'd have to go to on-premise or maybe EC2. A data mart or warehouse with a star-schema structure would be the classic answer there, but that takes significant processing time and that means no "real time". Also, that's potentially a lot more data transfer from Azure to wherever it goes, and that will cost you.

I wouldn't commit to this strategy without a pilot program first. The first thing to do would be to build a single instance (can it handle 400 sensor values a second? (Is that a series of rows, a big denormalized row, an XML document or something else? The format of the incoming data will affect how fast the data can be stored. Can you do bulk inserts, or does it have to be row-by-row?) How about 4,000 sensor values a second? It's possible that an single SQL Azure instance might not be able to store that much that quickly.) and see how it handles insertions at your expected rates and see how the reporting might work. And I'd talk to Microsoft too. Just dealing with the billing for hundreds or thousands of seperate databases might be quirky.

I don't know if this is applicable to you, but have you looked at Microsoft's "Stream Insight" product? It seems to be aimed at situations like yours. Caveat: I've never used it.

The marketting blurb: Effectively analyze large amounts of event data streaming in from multiple sources. Derive insights from critical information in near real time by using Microsoft StreamInsight. Monitor, analyze, and act on data in motion and make informed decisions almost instantaneously

While doing some quickly googling, I noticed a blog posting which states that StreamInsight available on SQL Azure as a CTP last year. it might be ready for prime time by now.

Good luck, it sounds like an interesting project.

| improve this answer | |
  • +1 schema/table isolation seems sufficient in this case. Unless the different databases are on separate and isolated servers (which you have very little control over), the different databases won't help much except for keeping backup/log activity separate (which also matters much less in Azure). – Aaron Bertrand May 4 '12 at 16:38
  • I find your comment about the 150GB db size for Azure quite an important one - as we will definitely have more than 150 engines - hence more than 150GB data. – ChrisI May 8 '12 at 15:08

I thought I would post a quick answer about how the project actually worked out.

In the end, we didn't use Azure. We used a standard SQL Database server - with each engine being in a different database. In theory, a master database holds the connection information for each engine. It is therefore possible to store different engines on different db servers. In practice we have not yet needed to. We have 200 engine database on one machine at the moment. I use connection pooling.

The 400 sensors arriving per second per engine, were sent up in XML, converted into a DataTable and batch inserted into the SQL database using a custom data type. Inserting 400 records every second takes only 40ms - 70ms. I do a outer join on the existing dataset so as to cope with the occasion when existing data gets resent in.

The system was written in such a way that each engine should not in theory slow down each other engine. Each engine effectively is managed within it's own thread pool. These thread pools can exist in different servers. The writing to the database and the updating of each user interface (web browser) was done is separate threads so the user never had to wait for the database to finishing inserting.

We are at a position now, where we are ready to take this concept into Azure. It does not look like there are as many restrictions in Azure now as there was at time of writing.

| improve this answer | |

I have no experience with systems like this, but my suggestion was longer than a comment so I'll post as an answer...

You say "sending 400 sensor values every second". Does this mean 400 separate messages per second, and I would assume that each message triggers a separate INSERT statement? If so, could you take all of this data, wrap it into a single XML message, and send that to a webservice that will store these incoming messages in a temporary holding table/queue and then disassemble them and process them as a separate step? This might result in slightly slower processing, but it might also help ease the deadlock problem without the need to resort to multiple databases, since in this scenario, you have one process that's managing all of the data inserts to the database. We use message queues and webservices for similar purposes here, though we come nowhere close to that kind of volume.

| improve this answer | |
  • 1
    Thanks for the reply, the messages from the remote sensors are going to be coming in through an XML packet - passed off to a service bus (currently MSMQ) and then processed by an application thread. I was going to either: 1. Have muliple threads/proceses all accessing the DB at the same time (hence my deadlock worry scenario) or 2. Have an application process/thread per database and never need to worry about deadlocking as each application will only be writing to a single db – ChrisI May 8 '12 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.