We need to scale out sql server database while writing on windows azure. We ideally don't want to scle the master tables and just want to scale only the transaction tables based on the regions in a country. Most of my research leads to SHARDING which involves the replica of the same database in to multiple servers. Is there any other technique which can scale only the transaction data and not the master data tables?,We would like to scale out a SQL Server DB which is connected to a application that handles transaction details. The DB also has master and other transaction tables. How can we scale only the transaction tables alone. Ex: based on a region of a country the records should be updated on the scaled databases. Note: We are trying to implement this on windows azure cloud. What is the feasible solution to this? Most of my research on internet says, SHARDING is the technique, but this is involves replication of the same DB in multiple resources, instead the master tables should be in one server and only the transaction tables should be scaled into multiple servers/db's. Is there any technique or solutions like this? Regards, Prakash
If you're exceeding the limits of Azure SQL Database for a single database, then yeah, you'll have to shard. There's no other magic options. Sharding will require you to write code to manage the distribution of your data. You can leave your master data in a single database and just shard the transactional data. That's fine. You'll have to build out either code directly in the client, or, better still, a service that the client calls, to handle determining where to put the transactional data. You can scale up, or you can scale out. If scaling up fails to meet requirements, you'll have to scale out. And that's referred to as sharding. Now, if the primary concern is just data collection, and you can afford to have a secondary process to deal with putting that data into structured storage, you could look at using Hadoop or DocumentDB instead. Then, you have a single entry point, but you can just add unlimited numbers of servers behind it. You'll lose ACID properties of the data, but you can guarantee massive scale. Then, in order to make the data reportable, you'll have to have a secondary process that takes it from the unstructured storage and puts it into structured storage. But, you avoid sharding.