You need to design a sharding strategy for the Planning Assistance database.
What should you recommend?
You need to design a sharding strategy for the Planning Assistance database.
What should you recommend?
To design an effective sharding strategy for the Planning Assistance database, it's crucial to choose a shard key that remains static and does not lead to uneven data distribution or hotspots. A list mapping shard map on the location column is appropriate as location data is generally static and doesn't change frequently, avoiding the pitfalls of frequent updates. Additionally, this allows for logically grouping data by regions, which can be beneficial for querying and managing regional data efficiently.
I would go with C. Thoughts?
C: Yes a list based mapping where sensors in similar locations are sharded together makes sense. You want to see the sensors over time in that area. Lat/Long would be mapped to shards. Time-based will cause hot-spots.
I believe that C is the best option: A) A binary representation of the license plate could cause problems due to the non-numeric characters in the plate number. B) Speeds will more often than not be similar leading to hotspots D) Rush hour traffic will cause hotspots as well if you use timespans Yes, some locations may have significantly more traffic than others but I think the likelihood of the differences being so great as to cause hotspots is minimal because real-world logic tells me that the planners are not going to put sensors in low-traffic areas.
I agree as "The shard key should be static. It shouldn't be based on data that might change." https://docs.microsoft.com/en-us/azure/architecture/patterns/sharding
I think the D is correct answer. https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-shard-map-management The binary representation of plate number is not supported among available data types for sharding keys. Time range mapping is more appropriate solution.
date and time columns are not good options for sharding
Wouldn't D be a better choice here?
I would go for D: https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-shard-map-management
Is it really D?
shard key should be static "The shard key should be static. It shouldn't be based on data that might change." https://docs.microsoft.com/en-us/azure/architecture/patterns/sharding
I agree with C