Is it OK to create hundreds of databases in SQL Azure versus one big one and run the risk of deadlocking
2
votes
3
answers
3808
views
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.
Asked by ChrisI
(31 rep)
May 4, 2012, 06:48 AM
Last activity: Oct 26, 2022, 08:02 AM
Last activity: Oct 26, 2022, 08:02 AM