0

I need to develop a Azure SQL server production system capable of storing a user item recommendations based on the user's purchase history, user health profile and item recommendations(around 415 MB). I know the approximate output over 13 months, the time the data has to be stored, and have calculated a need of around 30 TB of storage.

I have some experience working with databases, but handling that amount of data is new to me.

My initial approach would be to store this data in multiple databases using sharding but I am not sure about how to handle the application design part where the application needs to be aware of the sharding strategy and know which database (shard) to connect to, for a given operation(In our application each user is a guid) I am also not sure of the complexity of managing multiple databases, managing transactions that span multiple databases. What will the cost structure look like considering that in azure I pay per database.

Would the community have any inputs on my problem?

Improve this question

2 Answers 2

Reset to default
1

Would the community have any inputs on my problem?

Sure, these are my thoughts...

a user item recommendations based on the user's purchase history, user health profile and item recommendations(around 415 MB)

Sounds like a lot for a single user. How did you land on that number?

have calculated a need of around 30 TB of storage.

Did you include data compression in your size calculation?

I have some experience working with databases, but handling that amount of data is new to me.

Size of data at rest doesn't matter much. As long as your database is architected properly, there's mostly no difference.

My initial approach would be to store this data in multiple databases using sharding but I am not sure about how to handle the application design part where the application needs to be aware of the sharding strategy and know which database (shard) to connect to, for a given operation(In our application each user is a guid) I am also not sure of the complexity of managing multiple databases, managing transactions that span multiple databases.

This would indeed be needlessly complex at probably little to no gain. Sounds a little premature of an optimization currently until you've exhausted other options.

What will the cost structure look like considering that in azure I pay per database.

Sounds like if you sharded it, it would be expensive. If you kept it in a single database and vertically scaled as needed instead, it wouldn't be as expensive. But the cloud is a premium cost either way.

In short, I wouldn't concern myself with sharding the data until I've exhausted my other options. Just build a normal database and address performance problems if and when they occur. Don't try to proactively prematurely optimize.

Improve this answer
4
  • Thank you @J.D. for your inputs. We did look at the sizing multiple times and the sizing comes to 30 Terabytes. We came to this number based on a. 100, 000 users (and that number is growing) b. 14 user widgets, each widget has approximately 4 health user profiles c. Each user has 1200 items that we need to use to generate recommendations. These recommendations need to be generated for each widget(each which has a profile associated with it) d. filtered item recommendations dataset (around 130 MB) I have not looked at data compression(thank you) but i plan to do that
    – Ajit Goel
    Commented Jan 31 at 18:41
  • 1
    @AjitGoel "filtered item recommendations dataset (around 130 MB)" - Intuitively it sounds like this dataset may be storing redundant data for the same items that are recommended to multiple people. In a properly architected database you wouldn't need to repeat this information redundantly. I have a feeling your data can be reduced rather measurably when converted to a properly designed relational structure.
    – J.D.
    Commented Jan 31 at 19:18
  • Thank you @J.D. for your input, I will look into normalization of database tables.
    – Ajit Goel
    Commented Jan 31 at 21:24
  • 1
    @AjitGoel No problem! I think a normalized database would probably be the simplest and most cost effective solution for your use case. Then you can stick to a more standard technology like SQL Server or Azure SQL Database. If you need help normalizing, feel free to post another question with some example object definitions and data. Plenty of people on here are experienced with that and can help. (Also feel free to upvote and / or accept the answers on here if you found them helpful, so that they can help others as well.) Best of luck!
    – J.D.
    Commented Jan 31 at 21:50
0

Sounds like an good case for Azure Synapse Analytics. https://azure.microsoft.com/en-ca/products/synapse-analytics

Improve this answer
2
  • Thank you @SergeyA. I don't have a data warehouse background, but the data for my problem statement comes from different data sources. Once it arrives then there is a extremely large and complex processing logic that needs to be applied. Would Azure Synapse Analytics (or any data warehousing solution) support this user case? We would also need access to this data via an API
    – Ajit Goel
    Commented Jan 31 at 18:30
  • Move data from various data sources could be done via Azure Data Factory azure.microsoft.com/en-ca/products/data-factory. Data could be stored within Synapse (aka Dedicated pool (expensive)) or on Data Lake Storage account (Serverless SQL pool (less expensive))
    – SergeyA
    Commented Jan 31 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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