Scaling out SQL Server and syncing data across multiple machines

Question

I don't have expertise in architecting databases, and I've been teaching myself new stuff every day. I'd like to make an Internet-scale application using SQL Server as the data store. I haven't found any good information online with regards to scaling out SQL Server.

My understanding is that scaling out is great for write throughput, but it doesn't necessarily scale reads. A simple example (which is relevant in my case) is, if data is sharded by posting user id, status 1 posted by user X living in shard A will have all its likes and comments across the whole federation. So, if I need to fetch the comments on this status, I need to hit every database and merge and sort/filter results in application memory. This is bad for the databases because they are kept busy and bad for the web servers because I will be using CPU and RAM for post processing the objects. Ideally, I'd like to write to one database and read from one database for maximum scalability.

Now, what I'm thinking of doing is, instead of sharding by posting user id, shard by receiving user id. So, if user X posts status 1, user Y living in shard B can insert a comment in shard A, and I can enforce a parent-child relationship between the status and the comment. User Z living in shard C can insert a like in shard A for the comment, so the comment and the like can constitute a parent-child relationship. The benefit of this approach is I query only one database to get all the comments and likes for a specific status rather than naively querying every single shard.

However, I need to get results like "comments on status 1 by people who are male or 18+ years old". This is a crucial functionality I want to implement. I still have to hit other databases to get information about the users. In order to eliminate this, I'm thinking of creating a sync group where one database (hub) syncs all user deltas to all shards (every 5 minutes). I'm okay with eventual consistency though it has its own problems for example, if a user deletes their account, from the time the account is deleted to the time the delta is persisted to a shard, other users will not see the change potentially adding child objects to objects created by that user. This seems to me a data integrity issue.

I'm also aware of replication and caching to increase read throughput.

My question is, which approach should I pursue? If I choose the second one, will I have trouble syncing data across potentially hundreds or thousands of servers? Not to mention the hub is essentially a single point of failure.

Answer 1

Creating a Scale Out database at Internet scale is pretty huge step. You will face a lot of issues that are not critical on a single big database. From your notes I see that you understand some of the basic issues you face.

Since Microsoft has papers on using SQL Server for scale out, I suggest that you study those first. Your scale out strategy will need to take into account the database server you choose.

For Microsoft SQL Server you should first study: http://msdn.microsoft.com/en-us/library/aa479364.aspx

This paper discusses the decisions that you need to make and why they are important. It offers 5 SQL Server strategies for scaleout:

• Scalable Shared Databases

• Peer-to-Peer Replication

• Linked Servers

• Distributed Partitioned Views

• Data-Dependent Routing

As you go down the stack, things get more complicated, but also provide more powerful ways of scaling out.

Answer 2

Because I've had applications in the past fail, I would suggest two things. One is I wouldn't worry about scaling until you are closer to a live application and business rules are figured out. It seems like you might be solving for a problem you may not actually have. Which goes into the second point which is your data doesn't look like its mature enough to make a decision on Sharding. When sharding you generally need a strong understanding of your data in order to decide how to shard.

In one project, we actually went out and tried to apply best practice for horizontally scaling our database. We decided on sharding by tenant. Later due to evolving business changes we found the sharding tenants was not best as the tenants started sharing data and shards were mixed and being accessed at same time creating hot spots. It's one of microsofts watch out for's here: https://msdn.microsoft.com/en-us/library/dn589797.aspx

I suggest you build your application and figure out how your application would actually work before you start sharding. A single database can go pretty far before needing to do horizontal scaling if built right.

Other options may be read/write slaves. Read from one write to another, high availability groups and load balancing.

Good luck,

Answer 3

I tried PartitionDB* yesterday. Basically, from my understanding, it takes DPV (Distributed Partitioned Views) and does the work for you, maybe one of their union examples will work for you.

Note, you need to gather that shard/partition field ready in all your sharded/partitioned tables.

_{* I am a beta user, but not affiliated with this product otherwise.}