question

prakashgs2015 avatar image
prakashgs2015 asked

SQL Server Scalability - Windows Azure

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
azure
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you need to scale writes beyond the limits of a single SQL Azure database (1000 DTUs and 500GB currently)? Sharding isn't my strong suit, so I was wondering what kind of analysis went into the decision to shard the database. I don't think you need to have a replica of an entire database on multiple servers, just have databases on each server with the same subset of shared tables. Your master tables could live in their own database, and the application would just interact with those tables in their own database. From what I understand, sharding isn't something that will be transparent to your application, so the developers will have to write the code that deals with your transaction tables to be "shard aware" so that reads/writes are routed to the appropriate shard, and they can also write the code for the master tables to work with a single database. Looking forward reading anything you can share on the scale out analysis.
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.